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>Drop the column you don't need and add a column to get a unique subindex per color:</p>

<pre><code>df = df.drop('group', axis=1)
df['index_by_color'] = df.groupby('color').cumcount()

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

<p>Use <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.pivot_table.html" rel="nofollow"><code>pivot_table</code></a> to get the orientation of data you want:</p>

<pre><code>df.pivot_table(index='color', columns=['index_by_color'], aggfunc=lambda x:x.iloc[0])

                  line                  value
index_by_color       0       1        2     0     1     2
color
blue            sunday  monday  tuesday     b     a     b
green           monday    None     None     a  None  None
red             sunday  monday     None     a     c  None
</code></pre>

<p>The thing about <code>aggfunc=lambda x:x.iloc[0]</code> is to reduce the non-numeric pivoted data as a specific value, and the first element of the subframe is enough as per the unicity of your data structure.</p>

<p>Reorder the column hierachical index:</p>

<pre><code> res = res.sort_index(axis=1, level=1)

                  line value    line value     line value
index_by_color       0     0       1     1        2     2
color
blue            sunday     b  monday     a  tuesday     b
green           monday     a    None  None     None  None
red             sunday     a  monday     c     None  None
</code></pre>

<p>The remaining parts are trivial cleanup depending on the exact presentation you need, like <code>cumcount()+1</code> if you need to start numbering at 1 instead of 0, write/flatten the column names as you want <code>res.columns =['_'.join([l0, str(l1)]) for l0,l1 in res.columns]</code> or equivalent, etc.</p>

python pandas dataframe