Pandas count in second dataframe if part of string is first df's content

Question

having a simple DataFrame like this one:

    items
0   apple
1   car
2   tree
3   house
4   light
5   camera
6   laptop
7   watch
8   other

what would be a good way to count the times the individual words occur in a string in DF2:

    id        items
0   555       wall, grass, apple
1   124       bag, coffee, light
2   23123     bag, none, game
3   666       none, none, none

resulting in something like this

    items    count
0   apple    1
1   car      0
2   tree     0
3   house    0
4   light    1
5   camera   0
6   laptop   0
7   watch    0
8   other    0

Ynjxsjmh · Accepted Answer · 2022-08-29 13:10:31Z

1

Let's try value_counts the exploded items columns of df2 then map the result to items column of df1

df1['count'] = (df1['items'].map(df2['items'].str.split(', ')
                                 .explode()
                                 .value_counts())
                .fillna(0).astype(int))

print(df1)

    items  count
0   apple      1
1     car      0
2    tree      0
3   house      0
4   light      1
5  camera      0
6  laptop      0
7   watch      0
8   other      0

answered Aug 29, 2022 at 13:10

Ynjxsjmh

30.3k7 gold badges43 silver badges64 bronze badges

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

3 Comments

Bishonen_PL Over a year ago

Might be that I'm doing something wrong but this approach gave me: 'DataFrame' object has no attribute 'map'

Ynjxsjmh Over a year ago

@Bishonen_PL You didn't use df1['items'].map?

Bishonen_PL Over a year ago

apologies, my bad! When creating the DataFrame, I created it with a typo in which I set the columns to = [['items']] (double brackets). After fixing that, your solution works ;)

Quang Hoang · Accepted Answer · 2022-08-29 13:13:20Z

1

I would split the items, explode, then reindex:

(df2['items'].str.split(', ')
    .explode().value_counts()
    .reindex(df1['items'], fill_value=0)
    .reset_index(name='count')
)

Output:

    items  count
0   apple      1
1     car      0
2    tree      0
3   house      0
4   light      1
5  camera      0
6  laptop      0
7   watch      0
8   other      0

answered Aug 29, 2022 at 13:13

Quang Hoang

151k11 gold badges64 silver badges86 bronze badges

Collectives™ on Stack Overflow

Pandas count in second dataframe if part of string is first df's content

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related