CODEWITHSUNDEEP
pythonpandasdataframe
swang16
swang16

Reputation: 879

Python Pandas: Create New Columns For Each Instance of A Particular Column Value

I have a data frame like this

----------------
RecID| A  |B
----------------
1    |Dog | x 
2    |Dog | y 
3    |Dog | z
4    |Cat | a 
5    |Cat | b

And want to know if there is anyway to transform it so that it is like this:

-----------------------------
RecID| A |B_1|B_2|B_3|
-----------------------------
1    |Dog| x | y | z |
2    |Cat| a | b | NA|

Basically create new columns of each possible value of B grouped by particular values of A and fill with NA if need be.

Upvotes: 2

Views: 309

Answers (2)

piRSquared
piRSquared

Reputation: 294546

Using set_index with groupby and cumcount

df.set_index(
    ['A', df.groupby('A').cumcount() + 1]
).B.unstack().add_prefix('B_').reset_index()

     A B_1 B_2   B_3
0  Cat   a   b  None
1  Dog   x   y     z

Upvotes: 0

Zero
Zero

Reputation: 77027

One way would be 

In [294]: (df.groupby('A', sort=False).B.apply(list)
             .apply(pd.Series).add_prefix('B_').reset_index())
Out[294]:
     A B_0 B_1  B_2
0  Dog   x   y    z
1  Cat   a   b  NaN

Or,

In [320]: (df.groupby('A', sort=False).B.apply(lambda x: pd.Series(x.values))
             .unstack().rename(columns=lambda x: 'B_{}'.format(int(x)+1))
             .reset_index())
Out[320]:
     A B_1 B_2   B_3
0  Dog   x   y     z
1  Cat   a   b  None

Upvotes: 3

Related Questions