Reputation: 43
I'm trying to transform this dataset:
A B C
1 x1 a
1 x1 a
1 x1 b
2 x2 b
2 x2 a
into:
A B C1 C2 C3
1 x1 a a b
2 x2 b a null
df = pd.DataFrame({ 'A': [1, 1, 1, 2, 2],
'B': ['x1', 'x1', 'x1', 'x2', 'x2'],
'C': ['a', 'a', 'b', 'b', 'a']
})
The answer from here is somehow close but the pivot does not quite work for me. How to do a transpose a dataframe group by key on pandas?
Upvotes: 4
Views: 4869
Reputation: 77027
You could use set_index
and unstack
In [196]: (df.set_index(['A', 'B', df.groupby(['A', 'B']).cumcount()+1])['C']
.unstack()
.add_prefix('C')
.reset_index())
Out[196]:
A B C1 C2 C3
0 1 x1 a a b
1 2 x2 b a None
Upvotes: 3
Reputation: 403278
Use groupby
+ apply
-
v = df.groupby(['A' ,'B']).C.apply(lambda x: x.tolist())
df = pd.DataFrame(v.tolist(), index=v.index)\
.rename(columns=lambda x: x + 1)\
.add_prefix('C')\
.reset_index()
df
A B C1 C2 C3
0 1 x1 a a b
1 2 x2 b a None
Upvotes: 4