Keeping some data from duplicates and adding to existing python dataframe

Question

I have an issue with keeping some data from duplicates and wanting to add valuable information to a new column in the dataframe.

import pandas as pd
data = {'id':[1,1,2,2,3],'key':[1,1,2,2,1],'value0':['a', 'b', 'x', 'y', 'a']}
frame = pd.DataFrame(data, columns = ['id','key','value0'])
print frame

Yields:

    id   key   value0
0   1    1     a
1   1    1     b
2   2    2     x
3   2    2     y
4   3    1     a

Desired Output:

    key   value0_0  value0_1    value1_0  
0   1     a         b           a
1   2     x         y           None

The "id" column isn't important to keep but could help with iteration and grouping.

I think this could be adapted to other projects where you don't know how many values exist for a set of keys.

Answer 1

set_index including a cumcount and unstack

frame.set_index(
    ['key', frame.groupby('key').cumcount()]
).value0.unstack().add_prefix('value0_').reset_index()

   key value0_0 value0_1 value0_2
0    1        a        b        a
1    2        x        y     None

---

I'm questioning your column labeling but here is an approach using binary

frame.set_index(
        ['key', frame.groupby('key').cumcount()]
    ).value0.unstack().rename(
    columns='{:02b}'.format
).add_prefix('value_').reset_index()

   key value_00 value_01 value_10
0    1        a        b        a
1    2        x        y     None