Groupby and count columns with multiple values-CodePudding

Given this dataframe:

df = pd.DataFrame({
    "names": [["Kevin, Jack"], ["Antoine, Mary, Johanne, Iv"], ["Ali"]],
    "commented": [["Kevin, Antoine, Iv"], ["Antoine, Mary, Ali"], ["Mary, Jack"]],
}, index=["1", "2", "3"])

that'll look like this:

    names   commented
1   [Kevin, Jack]   [Kevin, Antoine, Iv]
2   [Antoine, Mary, Johanne, Iv]    [Antoine, Mary, Ali]
3   [Ali]   [Mary, Jack]

I want to get a new dataframe that will count all comments all people made. Something like:

	Kevin	Jack	Antoine	Mary	Iv	Ali
Kevin	1	0	1	0	1	0
Jack	1	0	1	0	1	0
Antoine	0	0	1	1	0	1
Mary	0	0	1	1	0	1
Johanne	0	0	1	1	0	1
Iv	0	0	1	1	0	1
Ali	0	1	0	1	0	0

This dataframe might be too small for it to make sense, but my original dataframe is 100k rows and there will be numbers higher than 0 and 1.

I've looked at various options using pivot_table and several variations of group by but I can't seem to figure this out.

df.pivot_table(index = 'names', columns= 'commented', aggfunc= 'count')

df.groupby('names').commented.apply(list).reset_index()
df.explode('names')['commented'].value_counts()

df.set_index('names').apply(pd.Series.explode).reset_index()

Almost all solutions I tried give me the error: TypeError: unhashable type: 'list'

CodePudding user response：

You can try explode the list of strings to rows then use pandas.crosstab

df = (df.explode(df.columns.tolist())
      .apply(lambda col: col.str.split(', '))
      .explode('names')
      .explode('commented'))

out = pd.crosstab(df['names'], df['commented'])

print(df)

     names commented
1    Kevin     Kevin
1    Kevin   Antoine
1    Kevin        Iv
1     Jack     Kevin
1     Jack   Antoine
1     Jack        Iv
2  Antoine   Antoine
2  Antoine      Mary
2  Antoine       Ali
2     Mary   Antoine
2     Mary      Mary
2     Mary       Ali
2  Johanne   Antoine
2  Johanne      Mary
2  Johanne       Ali
2       Iv   Antoine
2       Iv      Mary
2       Iv       Ali
3      Ali      Mary
3      Ali      Jack

print(out)

commented  Ali  Antoine  Iv  Jack  Kevin  Mary
names
Ali          0        0   0     1      0     1
Antoine      1        1   0     0      0     1
Iv           1        1   0     0      0     1
Jack         0        1   1     0      1     0
Johanne      1        1   0     0      0     1
Kevin        0        1   1     0      1     0
Mary         1        1   0     0      0     1

CodePudding user response：

In your sample input, each element in the names and commented columns is an array with only 1 element (a string). Not sure if that is the case with your real data.

You can split each string by the comma, and then explode and pivot the dataframe:

split = lambda x: x[0].split(", ")
(
    df.assign(
        names=df["names"].apply(split),
        commented=df["commented"].apply(split),
        dummy=1
    )
    .explode("names")
    .explode("commented")
    .pivot_table(index="names", columns="commented", values="dummy", aggfunc="count", fill_value=0)
)

CodePudding user response：

Here is another way using str.get_dummies()

(df.assign(names = df['names'].str[0].str.split(', '))
.explode('names')
.set_index('names')
.squeeze()
.str[0]
.str.get_dummies(sep=', '))

Output:

         Ali  Antoine  Iv  Jack  Kevin  Mary
names                                       
Kevin      0        1   1     0      1     0
Jack       0        1   1     0      1     0
Antoine    1        1   0     0      0     1
Mary       1        1   0     0      0     1
Johanne    1        1   0     0      0     1
Iv         1        1   0     0      0     1
Ali        0        0   0     1      0     1