min() max() and sum() functions working on pandas group by object but not mean()

user_14461938 0
Viewed 797

So basically, I have grouped month columns into quarters like columns 2000-01,2000-02,2000-03 into a single group 2000q1 where q1 means quarter 1 and so on. I have done is for 16 x 12 months and formed 48 quarters.

Now, I wish to get the average value of each row in a group. When I do grouped.max() grouped.min() and grouped.sum() I get the min , max and sum of each row in each group.(The row indices are the same for each group)

But when I try grouped.mean() I get an error saying:

No numeric types to aggregate.

Here is the code that I have written:

def quarter(val):
    month=val[5:]
    if month == "01" or month == "02"or month == "03":
        return val[:4]+"q1"
    elif month == "04"or month == "05"or month == "06":
        return val[:4]+"q2"  
    elif month == "07" or month == "08" or month == "09":
        return val[:4]+"q3"    
    elif month == "10"or month == "11"or month == "12":
        return val[:4]+"q4"  
city.fillna(0,inplace=True)


g=city.groupby(quarter, axis= 1 ).mean()

This is how my grouped data looks like

[('2000q1', 2000-01 2000-02 2000-03

0 0.0 0.0 0.0
1 204400.0 207000.0 209800.0
2 136800.0 138300.0 140100.0
3 52700.0 53100.0 53200.0
4 111000.0 111700.0 112800.0
5 131700.0 132600.0 133500.0

...

('2000q2', 2000-04 2000-05 2000-06
0 0.0 0.0 0.0
1 212300.0 214500.0 216600.0
2 141900.0 143700.0 145300.0
3 53400.0 53700.0 53800.0
4 113700.0 114300.0 115100.0
5 134100.0 134400.0 134600.0

...

('2002q2', 2002-04 2002-05 2002-06
0 0.0 0.0 0.0
1 268600.0 272600.0 276900.0
2 177800.0 177600.0 177300.0
3 60300.0 60700.0 61200.0
4 127900.0 128400.0 128800.0
5 150400.0 151000.0 151400.0

This is how city looks like enter image description here This is a part of the output I get when I do grouped.max()

pandas-groupby pandas mean dataframe python
1 Answers

It's easier to groupby the columns with values, and perform the operations.

df = pd.DataFrame({'Region':[1,2,3],'City':['a','b','c'],'Country':['A','B','C']})

df = pd.concat([df,pd.DataFrame(np.random.uniform(0,1,(3,12)),
columns=['2000-01','2000-02','2000-03','2000-04','2000-05','2000-06','2001-01','2001-02','2001-03','2001-04','2001-05','2001-06'])],axis=1)

You can use the date time function to create quarters:

def quarter(val):
    return pd.to_datetime(val).to_period("Q")

quarter(df.columns[3:])
 
PeriodIndex(['2000Q1', '2000Q1', '2000Q1', '2000Q2', '2000Q2', '2000Q2',
             '2001Q1', '2001Q1', '2001Q1', '2001Q2', '2001Q2', '2001Q2'],
            dtype='period[Q-DEC]', freq='Q-DEC')

Then we take columns that have the numerical values:

df.iloc[:,3:].groupby(quarter,axis=1).mean()
 
     2000Q1    2000Q2    2001Q1    2001Q2
0  0.506088  0.438958  0.132090  0.360160
1  0.635036  0.496895  0.673494  0.437333
2  0.560944  0.640423  0.603011  0.482962

You can always concat back the first three columns:

pd.concat([df.iloc[:,:3],df.iloc[:,3:].groupby(quarter,axis=1).mean()],axis=1)
user_122584590
Related