Lin Ma Lin Ma - 3 months ago 11
Python Question

get both unique count and max in group-by of pandas dataframe

Using Pandas data frame group by feature and I want to group by column

c_b
and (1) calculate unique count for column
c_a
and column
c_c
, (2) and get the max value of column c_d. Wondering if there is any solution to write one line of group by code to achieve both goals? I tried the following line of code, but it seems not correct.

sampleGroup = sample.groupby('c_b')(['c_a', 'c_d'].agg(pd.Series.nunique), ['c_d'].agg(pd.Series.max))


My expected results are,

Expected results,

c_b,c_a_unique_count,c_c_unique_count,c_d_max
python,2,2,1.0
c++,2,2,0.0


Thanks.

Input file,

c_a,c_b,c_c,c_d
hello,python,numpy,0.0
hi,python,pandas,1.0
ho,c++,vector,0.0
ho,c++,std,0.0
go,c++,std,0.0


Source code,

sample = pd.read_csv('123.csv', header=None, skiprows=1,
dtype={0:str, 1:str, 2:str, 3:float})
sample.columns = pd.Index(data=['c_a', 'c_b', 'c_c', 'c_d'])
sample['c_d'] = sample['c_d'].astype('int64')
sampleGroup = sample.groupby('c_b')(['c_a', 'c_d'].agg(pd.Series.nunique), ['c_d'].agg(pd.Series.max))
results.to_csv(sampleGroup, index= False)

Answer

You can pass a dict to agg():

df.groupby('c_b').agg({'c_a':'nunique', 'c_c':'nunique', 'c_d':'max'})

If you don't want c_b as index, you can pass as_index=False to groupby:

df.groupby('c_b', as_index=False).agg({'c_a':'nunique', 'c_c':'nunique', 'c_d':'max'})
Comments