How to find the average of each cell in multiple csv's

user_12169025 0
Viewed 77

I have several excel files with data in them in a format similar to this

csv1             csv1
  a b c           a b c
x 1 2 3         x 3 2 1
y 4 5 6         y 6 5 4

There are 3 csv's total, and I need to create a new csv with the average of each cell. So csv3 would be as follows

  a       b        c
x (3+1)/2) (2+2)/2  (3+1)/2
y (6+4)/2  etc.

So far I have the files imported but I am not sure how to proceed.

import pandas as pd

def Averager(fileA,fileB,fileC):
    csvA=pd.read_csv(fileA)
    csvB=pd.read_csv(fileB)
    csvC=pd.read_csv(fileC)
    g=pd.concat([csvA, csvB, csvC]).groupby(level=0).mean()
    print(g)                                                   
print(Averager('a.csv','b.csv','c.csv'))
pandas dataframe csv numpy python
3 Answers

Since you tagged numpy, I'm assuming a numpy solution would work.

import numpy as np
csv1 = np.genfromtxt('my_file1.csv', delimiter=',')
csv2 = np.genfromtxt('my_file2.csv', delimiter=',')
np.savetxt("foo.csv", (csv1+csv2)/2, delimiter=",")
user_14407840

You are pretty close, here is a solution that should work. I use pathlib to create the filenames, it's pretty easy to use.

import pandas as pd
from pathlib import Path

p = Path(r"path/to/file")

data = {}
for i in range(1,4):
    f = p / f"csv{i}.csv"
    data[i]=pd.read_csv(f)

df = pd.concat(data)
avg=df.groupby(level=1).mean()

print(df)
print(avg)
user_66584220

One super simple approach, after reading the CSV data into DataFrames is:

 df1.add(df2)/2

Or, in this specific case:

(csvA+csvB+csvC)/3

This works, providing all DataFrames are of the same shape, (and numeric) as shown in the original example.

user_63404960
Related