Pandas, DataFrame unique values from few columns

Question

I am trying to count uniqiue values that are in few columns. My data frame looks like that:

Name      Name.1    Name.2    Name.3
x         z          c          y
y         p          q          x
q         p           a         y

Output should looks like below:

I used a groupby or count_values but couldn't get a correct output. Any ideas ? Thanks All !

you can simply stack and count i.e `df.stack().value_counts()` — Bharath M Shetty, Jan 27 '19 at 13:21
Possible duplicate of [Get total values\_count from a dataframe with Python Pandas](https://stackoverflow.com/questions/52018311/get-total-values-count-from-a-dataframe-with-python-pandas). Also contains timings which may be relevant for your object type `DataFrame` — ALollz, Jan 27 '19 at 17:01

kentwait · Accepted Answer · 2019-01-27T13:10:52.043

2

Seems you want to consider values regardless of their row or column location. In that case you should collapse the dataframe and just use Counter.

from collections import Counter

arr = np.array(df)
count = Counter(arr.reshape(arr.size))

edited Jan 27 '19 at 13:10

answered Jan 27 '19 at 13:05

kentwait

It work's great. Thanks mate ! Do you have a idea how to show for instance 5 results that occurs the most often ? – Tmiskiewicz Jan 27 '19 at 13:10
`sorted(count.items(), key=lambda x: x[1])` – kentwait Jan 27 '19 at 13:12
It returns empty list – Tmiskiewicz Jan 27 '19 at 13:15
Sorry which one? The sorted result or count? – kentwait Jan 27 '19 at 13:16
Count is all right and I see that you changed your second answer and sorted shows all results. Not the first 5 that occurs the most often – Tmiskiewicz Jan 27 '19 at 13:20
1

You can do `sorted(..., reverse=True)[:5]` to get the top five by occurrence – kentwait Jan 27 '19 at 13:21
no need mate, that all I need :D Thank you one more time for your time – Tmiskiewicz Jan 27 '19 at 13:25

score 0 · Answer 2 · answered Jan 27 '19 at 17:57

Another (Pandas-based) approach is to (Series) apply value_counts to multiple columns and then take the sum (column-wise)

df2 = df.apply(pd.Series.value_counts)
print(df2.sum(axis=1).astype(int)
a    1
c    1
p    2
q    2
x    2
y    3
z    1
dtype: int32

2 Answers2