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

Question

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.

Zero · Accepted Answer · 2017-08-30 19:11:35Z

3

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

edited Aug 30, 2017 at 19:11

answered Aug 30, 2017 at 19:01

Zero

77.4k22 gold badges153 silver badges153 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

piRSquared · Accepted Answer · 2017-08-30 19:16:42Z

0

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

answered Aug 30, 2017 at 19:16

piRSquared

296k68 gold badges509 silver badges654 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related