CODEWITHSUNDEEP
pandaslistpivot-table
benzinga
benzinga

Reputation: 49

Pandas Pivot-Table Containing List

I'd like to create a pivot table with the counts of values in a list, filtered by another column but am not sure how to use pandas pivot table (or function) with a list.

Here's an example what I'd like to do:

| Col1 |   Col2     |
| --- | ----------- |
| A   | ["e", "f"]  |
| B   | ["g", "f"]  |
| C   | ["g", "h"]  |
| A   | ["e", "g"]  |
| B   | ["g", "f"]  |
| C   | ["g", "e"]  |

Ideal Pivot Table
| 1  | 2 |count|
| A  | e | 2 |
|    | f | 1 |
|    | g | 1 |
| B  | g | 2 |
|    | f | 2 |
| C  | g | 2 | 
|    | h | 1 |
|    | e | 1 |

I cannot use a list to make a pivot table and am struggling to figure out how to modify the data or find a different method. Any help would be much appreciated!

Upvotes: 1

Views: 411

Answers (1)

rhug123
rhug123

Reputation: 8768

Try this:

cols = ['Col1','Col2']
df.explode('Col2').groupby(cols).size()

Upvotes: 1

Related Questions