CODEWITHSUNDEEP
pythonpandasdataframe
Martin Bouhier
Martin Bouhier

Reputation: 361

Taking the sum and mean of multiple columns and appending the result as a single row

I read a csv file and I want to create a new row with some results as a sum()for 3 columns and a mean() for the rest columns. The problem is that my output is something like this:

Total                   1596260396              
Total2                      30453841            
Tota3                           3.024863813     
Total4                              19742.04    
Total5                                  0.733696498

And I´m looking for this:

Total 1596260396 30453841 3.024863813 19742.04 0.733696498

My code:

# encoding=utf8  
from io import StringIO
import pandas as pd
import os
from pandas import ExcelWriter
import sys
reload(sys)
sys.setdefaultencoding('utf-8')

df1 = pd.read_csv("este_mes.csv", sep=',')
df1.loc['Total'] = pd.Series(df1['Subastas'].sum(), index = ['Subastas'])
df1.loc['Total2'] = pd.Series(df1['Impresiones_exchange'].sum(), index = ['Impresiones_exchange'])
df1.loc['Tota3'] = pd.Series(df1['Fill_rate'].mean(), index = ['Fill_rate'])
df1.loc['Total4'] = pd.Series(df1['Importe_a_pagar_a_medio'].sum(), index = ['Importe_a_pagar_a_medio'])
df1.loc['Total5'] = pd.Series(df1['ECPM_medio'].mean(), index = ['ECPM_medio'])

Some rows of my csv:

Cliente,Fecha,Status,cl_fecha,Subastas,Impresiones_exchange,Fill_rate,Importe_a_pagar_a_medio,ECPM_medio
jjj,01/01/2017,Alerta Fillrate - Revenue - Imp Vendidas,jjj_01/01/2017,2277331,25843,1.13,10.36,0.4
jjj,02/01/2017,Alerta Fillrate -- Incremento Revenue - Imp Vendidas - Subastadas,jjj_02/01/2017,6747816,42957,0.64,17.9,0.42
jjj,03/01/2017,Alerta Fillrate - Revenue - Imp Vendidas:  aumento eCPM-- CRITICO,jjj_03/01/2017,6397915,27926,0.44,13.1,0.47
jjj,04/01/2017,Estable: descenso Revenue e Imp Vendidas,jjj_04/01/2017,5764124,22940,0.4,11.14,0.49
jjj,05/01/2017,Estable,jjj_05/01/2017,5635852,23924,0.42,11.24,0.47

Output I want:

Cliente,Fecha,Status,cl_fecha,Subastas,Impresiones_exchange,Fill_rate,Importe_a_pagar_a_medio,ECPM_medio
    jjj,01/01/2017,Alerta Fillrate - Revenue - Imp Vendidas,jjj_01/01/2017,2277331,25843,1.13,10.36,0.4
    jjj,02/01/2017,Alerta Fillrate -- Incremento Revenue - Imp Vendidas - Subastadas,jjj_02/01/2017,6747816,42957,0.64,17.9,0.42
    jjj,03/01/2017,Alerta Fillrate - Revenue - Imp Vendidas:  aumento eCPM-- CRITICO,jjj_03/01/2017,6397915,27926,0.44,13.1,0.47
    jjj,04/01/2017,Estable: descenso Revenue e Imp Vendidas,jjj_04/01/2017,5764124,22940,0.4,11.14,0.49
    jjj,05/01/2017,Estable,jjj_05/01/2017,5635852,23924,0.42,11.24,0.47

    Total 1596260396 30453841 3.024863813 19742.04 0.733696498

Upvotes: 0

Views: 52

Answers (1)

cs95
cs95

Reputation: 402523

Define your sum columns and mean columns - 

s = ['Subastas', 'Impresiones_exchange', 'Importe_a_pagar_a_medio']
m = ['Fill_rate', 'ECPM_medio']

Next, slice and compute - 

i = df1[s].sum()
i

Subastas                   26823038.00
Impresiones_exchange         143590.00
Importe_a_pagar_a_medio          63.74
dtype: float64


j = df1[m].mean()
j

Fill_rate     0.606
ECPM_medio    0.450
dtype: float64

Concatenate the two - 

v = pd.concat([i, j])
v

Subastas                   2.682304e+07
Impresiones_exchange       1.435900e+05
Importe_a_pagar_a_medio    6.374000e+01
Fill_rate                  6.060000e-01
ECPM_medio                 4.500000e-01
dtype: float64

And reshape your output - 

df = pd.DataFrame(v.values[:, None].T, index=['Total'], columns=v.index)
df

         Subastas  Impresiones_exchange  Importe_a_pagar_a_medio  Fill_rate  \
Total  26823038.0              143590.0                    63.74      0.606   

       ECPM_medio  
Total        0.45

To write to an existing CSV, use the append mode - 

df.to_csv('file.csv', mode='a')

Upvotes: 1

Related Questions