CODEWITHSUNDEEP
pythonpandasdataframe
Rustam
Rustam

Reputation: 158

How to combine groupby results without NaN?

I have the following dataframe:

import pandas as pd 

data = [['10', '20'], ['10', '15'], ['15', '14']] 

df = pd.DataFrame(data, columns = ['dt', 'ct'])
df.groupby('dt')['dt'].count()

returns

dt
10    2
15    1

and

df.groupby('ct')['ct'].count()

returns

ct
14    1
15    1
20    1

and then when I combine results

df.groupby('ct')['ct'].count() + df.groupby('dt')['dt'].count()

it returns

10    NaN
14    NaN
15    2.0
20    NaN

But I want to get the following:

10    2
14    1
15    2.0
20    1

Upvotes: 1

Views: 58

Answers (2)

Mac1
Mac1

Reputation: 1

try this:

data = [['10', '20'], ['10', '15'], ['15', '14']] 

df1 = pd.DataFrame(data, columns = ['dt', 'ct'])
df2= df1.groupby('ct')['ct'].count()
df3= df1.groupby('dt')['dt'].count()
print df2.append(df3)

Upvotes: 0

user3483203
user3483203

Reputation: 51165

Use stack + value_counts instead of two groupbys

df.stack().value_counts()


10    2
15    2
20    1
14    1
dtype: int64

If you have more than these columns, index first

df[['dt', 'ct']].stack().value_counts()

Upvotes: 6

Related Questions