Counting comma separated string in dataframe in a new column

user_3135025 0
Viewed 320

I have the following df:

df = pd.DataFrame({'Name': ['John', 'Sara', 'Paul', 'Guest'], 'Interaction': ['share,like,share,like,like,like', 'love,like,share,like,love,like', 'share,like,share,like,like,like,share,like,share,like,like,hug','share,like,care,like,like,like']})

Name    Interaction
0   John    share,like,share,like,like,like
1   Sara    love,like,share,like,love,like
2   Paul    share,like,share,like,like,like,share,like,sha...
3   Guest   share,like,care,like,like,like

I would like to create a third column calculating the number of single interactions as int

What I did:

df['likes'] = df[df['Interaction'] == 'like'].groupby('Name')['Interaction'].transform(lambda x: x[x.str.contains('like')].count())

I did the same line for share, care .. etc But it does not work!

Name    Interaction                                           likes     shares
0   John    share,like,share,like,like,like                     NaN     NaN
1   Sara    love,like,share,like,love,like                      NaN     NaN
2   Paul    share,like,share,like,like,like,share,like,sha...   NaN     NaN
3   Guest   share,like,care,like,like,like                      NaN     NaN

How can I count each interaction as int and then find the total per row in a final column?

pandas dataframe python-3.x python
3 Answers

You can split the string by ,, explode it and value_counts:

df.join(df['Interaction'].str.split(',')
          .explode()
          .groupby(level=0).value_counts()
          .unstack(fill_value=0))

Output:

    Name                                        Interaction  care  hug  like  love  share
0   John                    share,like,share,like,like,like     0    0     4     0      2
1   Sara                     love,like,share,like,love,like     0    0     3     2      1
2   Paul  share,like,share,like,like,like,share,like,sha...     0    1     7     0      4
3  Guest                     share,like,care,like,like,like     1    0     4     0      1
user_42384080

First you need to str.split the column on the comma, expand the result to create a dataframe, stack to get a series and use str.get_dummies that will create a column for each different word and add 1 for the corresponding value in the series. Finally sum on level=0 to go back to original shape. join the result to the original dataframe

df = df.join( df['Interaction'].str.split(',', expand=True)
                .stack()
                .str.get_dummies()
                .sum(level=0)
            )
print(df)
    Name                                        Interaction  care  hug  like  \
0   John                    share,like,share,like,like,like     0    0     4   
1   Sara                     love,like,share,like,love,like     0    0     3   
2   Paul  share,like,share,like,like,like,share,like,sha...     0    1     7   
3  Guest                     share,like,care,like,like,like     1    0     4   

   love  share  
0     0      2  
1     2      1  
2     0      4  
3     0      1
user_92747320

Let us do pd.crosstab

s = df.Interaction.str.split(',').explode()
df = df.join(pd.crosstab(s.index,s))
user_79645270
Related