Splitting dataframe column items and counting them in Pandas/Python

Question

I have a dataframe with a column called 'Name' that contains ordered items. Here is an example of what the data inside the column looks like:

I want to split each cell so that I can get a list of unique items ordered or split the contents of each cell and for each menu item += 1 to the counter.

I cannot seem to figure out how to go about splitting the cell contents to append them to a list so that I can then run a for loop of some kind and find out the unique count for each item.

Refrain from showing your dataframe as an image. Your question needs a minimal reproducible example consisting of sample input, expected output, actual output, and only the relevant code necessary to reproduce the problem. See [How to make good reproducible pandas examples](https://stackoverflow.com/questions/20109391/how-to-make-good-reproducible-pandas-examples) for best practices related to Pandas questions. — itprorh66, Feb 21 '23 at 14:05

score 0 · Answer 1 · answered Feb 21 '23 at 14:09

IIUC, you can try:

df1 = (df['Name'].str.split(', ').explode().to_frame('Items')
                 .reset_index().assign(Count=1)
                 .pivot_table(index='index', columns='Items',
                              values='Count', aggfunc='sum', fill_value=0))

out = pd.concat([df, df1], axis=1)

Output:

>>> out
            Name  A  B  C  D  E  F
0  A, B, C, D, A  2  1  1  1  0  0
1     B, C, E, C  0  1  2  0  1  0
2  A, F, D, C, A  2  0  1  1  0  1

Splitting dataframe column items and counting them in Pandas/Python

1 Answers1