How can I count occurrences of unique items per column across columns in pandas?

TabDelimited 0
Viewed 34

Say I have a dataframe that looks as follows:

Index one two three
First A B C
Second C A B
Third B A C

My desired out is the counts of df.iloc[:,1:]: across the columns.

Item one two three
A 1 2 0
B 1 1 1
C 1 0 2

Explanation for the second table counts in parenthesis next to each count.

Item one two three
A 1 (Only one A in column 1) 2 (two counts of A in column 2) 0 (No A in column 3)
B 1(Only one B in column 1) 1 (Only one B in column 2) 1 (1 B in column 3)
C 1 (Only one C in column 1) 0 (No Cs in column 2) 2 (two counts of C in column 3)

I have tried the following that did not quite work:

  • df3.iloc[:,1:].value_counts().to_frame('counts').reset_index()

  • df[df.columns[1:]].value_counts()

The above two one-liners are close but not quite there.

A Macgyvered solution I can think of is to loop through the columns one by one and do .value_counts() on each of them and try to organize the counts but that gets a bit messy given the order of each .value_count is somewhat different.

How should I do this?

pandas python-3.x
3 Answers

Try this:

df = df.apply(pd.value_counts).fillna(0)

Hoope it helps.

userSwSh10

Try this :

from io import StringIO
import pandas as pd

s = """Index    one two three
First   A   B   C
Second  C   A   B
Third   B   A   C"""

df  = pd.read_csv(StringIO(s), sep='\t').set_index('Index')

df = (df.apply(pd.value_counts).fillna(0)
      .apply(pd.to_numeric,downcast='integer')
      .reset_index()
      .rename(columns={'index':'Item'})
     )
>>> display(df)

enter image description here

abokey0

use value_counts in an apply for the dataframe

data="""Index,one,two,three
First,A,B,C
Second,C,A,B
Third,B,A,C
"""
df = pd.read_csv(io.StringIO(data), sep=',')


df=df.apply(lambda x: x.value_counts()).fillna(0)
df=df.drop(labels=['First','Second','Third'],axis=0)

print(df)
Golden Lion0
Related