Pandas: count frequency of values and convert into columns

Question

For example, I have a data frame like this:

     id    0     1     2     3     4     5
0    1     a     b    null   c    null  null
1    2    null   a    null   d     e    null
2    3     d    null   c    null   a    null
3    4    null  null   d    null   b     e     
4    5     c     a     e     b    null   b

And I wanna get a data frame like:

     id    null     a     b     c     d     e
0    1       3      1     1     1     0     0
1    2       3      1     0     0     1     1
2    3       3      1     0     1     1     0
3    4       3      0     1     0     1     1     
4    5       1      1     2     1     0     1

How to do that? Thanks!

Edit: I tried this aoi_data = pd.get_dummies(aoi.set_index('userId').stack()).sum(level=0)

That's it!

Good question but it does not show, what have you tried. Can you show us your code? — harpan
– harpan, Commented Jul 20, 2018 at 14:32

BENY · Accepted Answer · 2018-07-20 14:32:21Z

3

You need get_dummies

df.fillna('null').set_index('id').stack().str.get_dummies().sum(level=0)
Out[637]: 
    a  b  c  d  e  null
id                     
1   1  1  1  0  0     3
2   1  0  0  1  1     3
3   1  0  1  1  0     3
4   0  1  0  1  1     3
5   1  2  1  0  1     1

answered Jul 20, 2018 at 14:32

BENY

324k22 gold badges176 silver badges250 bronze badges

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

8 Comments

Yuca Over a year ago

when you're around I just take a break for two hours. You just crush every question

BENY Over a year ago

@Yuca there are a lot of people provide the nicer and quicker responses than me , :-)

Yuca Over a year ago

I have yet to see one, for real though, I have mad respect for your pandas knowledge

BENY Over a year ago

@Yuca to be honestly , I learnt those skill from Jz, PiR ,Coldspeed, Scott and Zero and so on . :-)

WWH98932 Over a year ago

@Wen Thanks for your solution! But my dataset is too large and this takes forever, so I didn't apply it yet. BTW, is there any alternative way to get this result?

|

Collectives™ on Stack Overflow

Pandas: count frequency of values and convert into columns

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related