MY DATA
Let’s say I have a dataframe where each row represents one shopping cart, like so:
pd.DataFrame({'0':['banana','apple','orange'],'1':['apple','milk','bread'],'2':['bread','cheese','banana']})
0 1 2
0 banana apple bread
1 apple milk cheese
2 orange bread banana
WHAT I AM TRYING TO DO
What I would like to do is get the value counts of each shopping cart and create an overall list. For example:
count
banana 2
bread 2
apple 2
milk 1
cheese 1
orange 1
WHAT I HAVE TRIED
I tried the following, thinking a row-wise function application would work. It almost gets me there, but not quite:
df.apply(lambda x : x.value_counts())
0 1 2
apple 1.0 1.0 NaN
banana 1.0 NaN 1.0
bread NaN 1.0 1.0
cheese NaN NaN 1.0
milk NaN 1.0 NaN
orange 1.0 NaN NaN
After this, I would need to sum those columns into one column to get the overall count. Am I missing a parameter, or is there a more pythonic way to do this in a single call?
>Solution :
Add DataFrame.stack first:
out = df.stack().value_counts()
print(out)
banana 2
apple 2
bread 2
milk 1
cheese 1
orange 1
Name: count, dtype: int64
Or DataFrame.melt:
out = df.melt()['value'].value_counts()
print(out)
value
banana 2
apple 2
bread 2
orange 1
milk 1
cheese 1
Name: count, dtype: int64
If need one column DataFrame
out = df.stack().value_counts().to_frame()
#out = df.melt()['value'].value_counts().to_frame()
print(out)
count
banana 2
apple 2
bread 2
milk 1
cheese 1
orange 1