[FIXED] How to get the value counts of each unique value in a row for all rows in a pandas dataframe

Issue

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

Answered By - jezrael