Pandas - Creating multiple columns similar to pd.get_dummies
<p>Let's say my data looks like this:</p>

<pre><code>df = pd.DataFrame({'color': ['red', 'blue', 'green', 'red', 'blue', 'blue'], 'line': ['sunday', 'sunday', 'monday', 'monday', 'monday', 'tuesday'],
               'group': ['1', '1', '2', '1', '1', '1'], 'value': ['a', 'b', 'a', 'c', 'a', 'b']})

    color   group   line    value
0   red       1     sunday   a
1   blue      1     sunday   b
2   green     2     monday   a
3   red       1     monday   c
4   blue      1     monday   a
5   blue      1    tuesday   b
</code></pre>

<p>Essentially, what I want is to get a list of lines for each color. For instance, I want the color red to show each line and value associated with it in its own column. The trick is that I also want to show other lines associated with colors from the same group. The corresponding values for those would be 'not eligible'. Thus I want my output to look like this:</p>

<pre><code>    color   line_1  line_1_value    line_2  line_2_value    line_3     line_3_value
0   red     sunday       a          monday       c          tuesday    not eligible
1   blue    sunday       b          monday       a          tuesday         b
2   green   monday       c      
</code></pre>

<p>There are some ~50,000 unique 'colors' that I need to do this for. I'm sure it's something relatively simple, but I don't possess the knowledge or skillset yet to figure it out. Any help would be appreciated!        </p>

<p>Consider a merge on two pivoted dfs with column name handling:</p>

<pre><code>df['count'] = df.groupby('color').cumcount() + 1

pvt1 = df.pivot(columns='count', index='color', values='line').reset_index().fillna('')
pvt1.columns = ['color'] + ['line_'+str(c) for c in pvt1.columns[1:]]

pvt2 = df.pivot(columns='count', index='color', values='value').reset_index().fillna('')
pvt2.columns = ['color'] + ['line_'+str(c)+'_value' for c in pvt2.columns[1:]]

pvtdf = pd.merge(pvt1, pvt2, on=['color'])
pvtdf = pvtdf[[c for c in sorted(pvtdf.columns)]]

#    color  line_1 line_1_value  line_2 line_2_value   line_3 line_3_value
# 0   blue  sunday            b  monday            a  tuesday            b
# 1  green  monday            a                                           
# 2    red  sunday            a  monday            c                      
</code></pre>

python pandas dataframe