Count of elements in lists in two columns within pandas dataframe

Question

I need to get the frequency of each element in lists when the list is in a pandas dataframe columns.

it like data.groupby(["element in a","element in b"]).size(),but column 'a' and column 'b' is list.

I need the size of each combination by element in 'a' and b'b'

in data:
        a           b
0   [17, 21, 22]    [zhinan, shejiyuanze, fankui]
1   [17, 21, 23]    [zhinan, shejiyuanze]
2   [17, 21]        [zhinan, shejiyuanze, fankui]
3   [17, 21, 22]    [zhinan, shejiyuanze, fankui]
4   [17, 21]        [zhinan, shejiyuanze, yizhi]

Desired Output:

              17 21 22 23 
zhinan        5  5  2  1
shejiyuanze   .  .  .  . 
fankui        .  .  .  . 
yizhi         .  .  .  .

For example, when a=17 and b=zhinan, the number is 5.when a=17 and b=fankui,the number is 3.when a=23 and b= fankui or b=yizhi the number is 0.

I was wondering if there is a efficient/direct way to do this.

thanks

u can technically get ur rows & columns by using pd.data.pivot(index='b', columns='a') but I fail to see how u are counting frequencies here? could you elaborate I lack the information to reproduce ur example — Uralan
– Uralan, Commented Sep 4, 2019 at 6:59
it's like groupby element in column 'a' and element in columns 'b'. i need the size of Each combination by 'a' and 'b'. — hanson james
– hanson james, Commented Sep 4, 2019 at 7:18
ok. i see. you want to count how many times each combination of 17, 21, 22, 23 x zhinan, shejiyuanze, fankui, yizhi occurs? — Uralan
– Uralan, Commented Sep 4, 2019 at 9:21

ckedar · Accepted Answer · 2019-09-04 07:16:23Z

3

Use explode to explode lists. Remember to reset_index before second explode.

Then use group_by to count number of occurrences.

Finally use unstack to convert Series to Dataframe

df.explode('a').reset_index(drop=True).explode('b').groupby(['b', 'a']).a.count().unstack()

answered Sep 4, 2019 at 7:16

ckedar

1,9097 silver badges7 bronze badges

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

3 Comments

anky Over a year ago

@hansonjames if that worked, please consider accepting this answer.

ckedar Over a year ago

Does it complain about explode? Dataframe.explode is available from pandas 0.25.0

Uralan Over a year ago

@ckedar that was the problem, indeed. after upgrade explode works. great solution! i misunderstood the question. I thought he wanted individual combinations based on list index (e.g. count +1 only when two elements' list positions match)

Collectives™ on Stack Overflow

Count of elements in lists in two columns within pandas dataframe

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related