Pandas groupby on comma separated values

user_7683771 0
Viewed 36

I want to calculate the count of one column grouped by comma separated values in another column.

The table looks like this:

ColumnA ColumnB
A,B,C   P1
A,C     P1
A,C     P2
D       P3

The output should look like this:

P1     A     2
       B     1
       C     2
P2     A     1
       C     1
P3     D     1

The code I have tried is like this:

new_df = (new_df.set_index('ColumnB')['ColumnA'].str.split(',', expand=True).stack().reset_index(name='ColumnA').groupby('ColumnA'))

Cannot figure out the correct syntax.

pandas csv
1 Answers

Add GroupBy.size for Series with MultiIndex:

new_df = (new_df.set_index('ColumnB')['ColumnA']
                .str.split(',', expand=True)
                .stack()).reset_index(name='ColumnA')
                .groupby(['ColumnB','ColumnA'])
                .size())

print (new_df)
ColumnB  ColumnA
P1       A          2
         B          1
         C          2
P2       A          1
         C          1
P3       D          1
dtype: int64
user_29010020
Related