How can I group by and count list value in dataframe-CodePudding

I have dataframe

df = pd.DataFrame({'session_id': ['T01', 'T02', 'T03', 'T04', 'T05', 'T06', 'T07'],
                   'path': [array(['a', 'b', 'c'], dtype='<U1'),
                            array(['x', 'y', 'z'], dtype='<U1'),
                            array(['a', 'b', 'c'], dtype='<U1'),
                            array(['x', 'y', 'z'], dtype='<U1'),
                            array(['k'], dtype='<U1'),
                            array(['x', 'y', 'z'], dtype='<U1'),
                            array(['h', 'i'], dtype='<U1')],
                   'flag': [0, 1, 0, 0, 0, 1, 0],
                   'total_value': [0.02, 0.05, 0.02, 0.05, 0.001, 0.05, 0.03]})

like this table below

| session_id |         path         | flag  |   total_value |
| ---------- | -------------------- | ----- | ------------- |
| T01        | [a,b,c]              | 0     | 0.020         |
| T02        | [x,y,z]              | 1     | 0.050         |
| T03        | [a,b,c]              | 0     | 0.020         |
| T04        | [x,y,z]              | 0     | 0.050         |
| T05        | [k]                  | 0     | 0.001         |
| T06        | [x,y,z]              | 1     | 0.050         |
| T07        | [h,i]                | 0     | 0.030         |

I want to group by path, flag, total_value and count number of records after that sort by total_value desc. Final result choose be like this below.


|         path         | flag  |   total_value |  count  |
| -------------------- | ----- | ------------- | ------- |
| [x,y,z]              | 1     | 0.050         |  2      |
| [x,y,z]              | 0     | 0.050         |  1      |
| [h,i]                | 0     | 0.030         |  1      |
| [a,b,c]              | 0     | 0.020         |  2      |
| [k]                  | 0     | 0.001         |  1      |

I try to used

df.groupby(['path', 'flag', 'total_value']).count()

but error will show

unhashable type: 'numpy.ndarray'

CodePudding user response：

The problem with that groupby is that as the error message says, np.ndarrays are mutable hence, unhashable, so cannot be used as an index. But tuples are hashable, so you can convert the arrays in the 'path' column to tuples and groupby on the desired columns and use count method.

Then after the operation, convert 'path' values back to np.arrays:

df['path'] = df['path'].apply(tuple)
out = df.groupby(['path', 'flag', 'total_value']).count().reset_index()
out['path'] = out['path'].apply(np.array)

Output:

        path  flag  total_value  session_id
0  [a, b, c]     0        0.020           2
1     [h, i]     0        0.030           1
2        [k]     0        0.001           1
3  [x, y, z]     0        0.050           1
4  [x, y, z]     1        0.050           2