CODEWITHSUNDEEP
pythonpandasdataframegroup-bypandas-groupby
Martin Bouhier
Martin Bouhier

Reputation: 361

Perform groupby.sum for some columns, and groupby.mean for others

With my code I have some results in csv and are right, the problem is I need mean() the two columns that I'm drop because I don't know why I can sum some columns and mean the others.

I added the csv text to be more specific and my output. Also, the output that I'm looking for!

code:

"""Calculate"""
# encoding=utf8

import pandas as pd

dfh = pd.read_csv("este_mes.csv", sep=',')
h = dfh['Fecha'].max()
dfh['Cliente'] = dfh['Cliente'] + "," + h
dfh = dfh.groupby(['Cliente']).sum()

frames = [dfh]

results2 = pd.concat(frames)
results2 = results2.drop('Fill_rate', 1)
results2 = results2.drop('ECPM_medio', 1)
results2.to_csv("Cliente_x_mes.csv", sep=',', index=True)
results2 = pd.read_csv("Cliente_x_mes.csv", sep=',')

csv:

Cliente,Fecha,Status,cl_fecha,Subastas,Impresiones_exchange,Fill_rate,Importe_a_pagar_a_medio,ECPM_medio
jjj,01/01/2018,Alerta Revenue: aumento Subastadas - descenso eCPM y Fillrate,jjj_01/01/2018,1930916,53231,2.76,17.32,0.33
jjj,02/01/2018,Alerta Fillrate -- Incremento Revenue - Imp Vendidas - Subastadas,jjj_02/01/2018,5930774,98181,1.66,33.2,0.34
jjj,03/01/2018,Estable,jjj_03/01/2018,5487499,97782,1.78,33.37,0.34
jjj,04/01/2018,Estable,jjj_04/01/2018,5254018,98039,1.87,32.95,0.34
jjj,05/01/2018,Estable,jjj_05/01/2018,4904150,98068,2.0,31.58,0.32
jjj,06/01/2018,Alerta Revenue - Imp Vendidas - Subastadas -- Incremento Fillrate: descenso eCPM,jjj_06/01/2018,4904150,98068,2.0,31.58,0.32
kkk,01/01/2018,Alerta Fillrate - Revenue - Imp Vendidas,kkk_01/01/2018,30668,4464,14.56,3.87,0.87
kkk,02/01/2018,Incremento Imp Vendidas - Subastadas: descenso eCPM,kkk_02/01/2018,41032,5707,13.91,4.06,0.71
kkk,03/01/2018,Estable,kkk_03/01/2018,39847,5331,13.38,3.72,0.7
kkk,04/01/2018,Estable: descenso Imp Vendidas,kkk_04/01/2018,37403,4733,12.65,3.37,0.71
kkk,05/01/2018,Estable: descenso Fillrate,kkk_05/01/2018,40330,4473,11.09,3.35,0.75
kkk,06/01/2018,Estable: descenso Subastadas y aumento Fillrate,kkk_06/01/2018,32797,4050,12.35,3.22,0.8

The output:

Cliente,Subastas,Impresiones_exchange,Importe_a_pagar_a_medio
"jjj,10/01/2018",44367734,946163,303.14
"kkk,10/01/2018",382800,47851,36.47

The output I need:

Cliente,Subastas,Impresiones_exchange,Importe_a_pagar_a_medio,Fill_rate,ECPM_medio
"jjj,10/01/2018",44367734,946163,303.14,30,0.331666667
"kkk,10/01/2018",382800,47851,36.47,3.598333333,0.756666667

On the other hand if you can have the output with only 2 decimals it will be great

Upvotes: 1

Views: 223

Answers (1)

cs95
cs95

Reputation: 402553

Option 1
Split your grouping code into two stages. First, create a groupby object, and then calculate sum/mean for the appropriate columns.

m = ['Fill_rate', 'ECPM_medio']        # columns to calculate mean for
s = df.columns.difference(m).tolist()  # columns to calculate sum for

An alternate manner of finding s (for numeric columns only) - 

s = df.columns[df.dtypes != object].difference(m).tolist()

Next, 

# Stage 1
g = df.groupby('Cliente')   

# Stage 2
i = g[s].sum()
j = g[m].mean()

# concatenate results, and save to CSV
pd.concat([i, j], 1).to_csv('Cliente_x_mes.csv')

Details

i

         Importe_a_pagar_a_medio  Impresiones_exchange  Subastas
Cliente                                                         
jjj                       180.00                543369  28411507
kkk                        21.59                 28758    222077

j

         Fill_rate  ECPM_medio
Cliente                       
jjj       2.011667    0.331667
kkk      12.990000    0.756667

Option 2
Another way of doing this would be to build an dict of functions, and pass it to groupby.agg - 

f = dict.fromkeys(m, 'mean')
f.update(dict.fromkeys(, 'sum'))

df.groupby('Cliente').agg(f).to_csv('Cliente_x_mes.csv')

Cilente_x_mes.csv

Cliente,Importe_a_pagar_a_medio,Impresiones_exchange,Subastas,Fill_rate,ECPM_medio
jjj,180.0,543369,28411507,2.0116666666666667,0.3316666666666667
kkk,21.59,28758,222077,12.99,0.7566666666666667

Upvotes: 2

Related Questions