Pandas aggregating values of multiple columns

Question

Here is an example of my DataFrame

  user    workflow    device     location  date 
  A         top         Mac      SF        1/1/19
  B         left        Win      SA        1/1/19
  A         right       Mac      SF        1/5/19
  C         left        iphone   LA        1/7/19
  D         left        pixel    BO        1/20/19
  C         bottom      iphone   LA        1/21/19

I want to transform the DF to look like this:

 User top bottom left right Mac Win iphone pixel SF  SA   LA  BO
 A     1   0     0      1   1   0     0     0     2   0   0    0
 B     0   0     1      0   0   1     0     0     0   1   0    0
 C     0   1     1      0   0   0     2     0     0   0   2    0
 D     0   0     1      0   0   0     0     1     0   0   0    1

How would one do that using Pandas?

BENY · Accepted Answer · 2019-03-19 01:03:13Z

1

Using str.get_dummies after stack

s=df.set_index('user').stack().str.get_dummies().sum(level=0)
s  
      BO  LA  Mac  SA  SF  Win  bottom  iphone  left  pixel  right  top
user                                                                   
A      0   0    2   0   2    0       0       0     0      0      1    1
B      0   0    0   1   0    1       0       0     1      0      0    0
C      0   2    0   0   0    0       1       2     1      0      0    0
D      1   0    0   0   0    0       0       0     1      1      0    0

answered Mar 19, 2019 at 1:03

BENY

324k22 gold badges176 silver badges250 bronze badges

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

12 Comments

user5844628 Over a year ago

what if I had other columns I am not using in the DataFrame. How would I still output the same result?

BENY Over a year ago

@user5844628 I am not get the , what you mean other columns but not in the data frame ?

user5844628 Over a year ago

please see updated DateFrame. I add a date column but I do not desire to use that in my transformed counts

BENY Over a year ago

@user5844628 I think I get you , df.drop('date',1).set_index('user').stack().str.get_dummies().sum(level=0)

user5844628 Over a year ago

Thanks! What if you had 100+ columns you are not using. Is there an easier way to drop those without specifically calling the column names out as you are doing here?

|

Collectives™ on Stack Overflow

Pandas aggregating values of multiple columns

1 Answer 1

12 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

12 Comments

Your Answer

Sign up or log in

Post as a guest

Related