Better Pandas way to count frequency of values in different columns together

user_4865723 0
Viewed 84

I have a pandas.DataFrame with ZIPCODES in two columns. I just want to count the appearence of all ZIPCODES with value_counts(). But it does not matter for me in which column they are. I need a result for all ZIPCODE columns in the DataFrame together.

This is the inital data with ZIPCODES in the columns:

   ZIPCODE_A  ZIPCODE_B
0      10000      40000
1      20000      30000
2      20000      20000
3      10000      50000
4      30000      10000

The final and expected result would be:

       ZIPCODE_N
10000          3
20000          3
30000          2
40000          1
50000          1

Question

My solution works but looks complicated. Is there another more elegant pandas-way to solve this?

MWE

#!/usr/bin/env python3
import pandas as pd
0000
df = pd.DataFrame({'ZIPCODE_A': [10000, 20000, 20000, 10000, 30000],
                   'ZIPCODE_B': [40000, 30000, 20000, 50000, 10000]})

print(df)

a = df.ZIPCODE_A.value_counts()
b = df.ZIPCODE_B.value_counts()

a = pd.DataFrame(a)
b = pd.DataFrame(b)

r = a.join(b, how='outer')

r.loc[r.ZIPCODE_A.isna(), 'ZIPCODE_A'] = 0
r.loc[r.ZIPCODE_B.isna(), 'ZIPCODE_B'] = 0

r['ZIPCODE_N'] = r.ZIPCODE_A + r.ZIPCODE_B
r.ZIPCODE_N = r.ZIPCODE_N.astype(int)

del r['ZIPCODE_A']
del r['ZIPCODE_B']

print(r)
pandas python
4 Answers

First stack the dataframe to have values of all the columns in a single column then call value_counts(), if needed, call to_frame() and pass the new column name i.e. ZIPCODE_N

>>> df.stack().value_counts().to_frame("ZIPCODE_N")

       ZIPCODE_N
10000          3
20000          3
30000          2
50000          1
40000          1
user_91363480

you can do:

df.unstack().value_counts()
Out[4]: 
20000    3
10000    3
30000    2
50000    1
40000    1
dtype: int64
user_109535920

melt on your zipcode columns and then use value_counts once:

df.melt(value_vars=["ZIPCODE_A", "ZIPCODE_B"])["value"].value_counts()

# 10000    3
# 20000    3
# 30000    2
# 40000    1
# 50000    1
# Name: value, dtype: int64
user_32797160

You might just pandas.concat required columns and then use .value_counts that is

import pandas as pd
df = pd.DataFrame({'ZIPCODE_A': [10000, 20000, 20000, 10000, 30000], 'ZIPCODE_B': [40000, 30000, 20000, 50000, 10000]})
together = pd.concat([df.ZIPCODE_A,df.ZIPCODE_B])
print(together.value_counts())

output

20000    3
10000    3
30000    2
50000    1
40000    1
dtype: int64
user_107859750
Related