Geek Answers Handbook

Averaging data from multiple data files in Python using pandas

I have 30 csv data files from 30 repeated runs of the experiment that I ran. I use the pandas' function read_csv()to read data into a DataFrames list. I would like to create a single DataFrame from this list containing an average of 30 DataFrames for each column. Is there a built-in way to do this?

To clarify, I will talk about the example in the answers below. Let's say I have two DataFrames:

>>> x
          A         B         C
0 -0.264438 -1.026059 -0.619500
1  0.927272  0.302904 -0.032399
2 -0.264273 -0.386314 -0.217601
3 -0.871858 -0.348382  1.100491
>>> y
          A         B         C
0  1.923135  0.135355 -0.285491
1 -0.208940  0.642432 -0.764902
2  1.477419 -1.659804 -0.431375
3 -1.191664  0.152576  0.935773

What is the merge function that I have to use to create a 3D array using a DataFrame? eg.

>>> automagic_merge(x, y)
                      A                      B                      C
0 [-0.264438,  1.923135] [-1.026059,  0.135355] [-0.619500, -0.285491]
1 [ 0.927272, -0.208940] [ 0.302904,  0.642432] [-0.032399, -0.764902]
2 [-0.264273,  1.477419] [-0.386314, -1.659804] [-0.217601, -0.431375]
3 [-0.871858, -1.191664] [-0.348382,  0.152576] [ 1.100491,  0.935773]

so I can calculate average, sem, etc. in these lists instead of the entire column.

+2
3

:

In [14]: glued = pd.concat([x, y], axis=1, keys=['x', 'y'])

In [15]: glued
Out[15]: 
          x                             y                    
          A         B         C         A         B         C
0 -0.264438 -1.026059 -0.619500  1.923135  0.135355 -0.285491
1  0.927272  0.302904 -0.032399 -0.208940  0.642432 -0.764902
2 -0.264273 -0.386314 -0.217601  1.477419 -1.659804 -0.431375
3 -0.871858 -0.348382  1.100491 -1.191664  0.152576  0.935773

In [16]: glued.swaplevel(0, 1, axis=1).sortlevel(axis=1)
Out[16]: 
          A                   B                   C          
          x         y         x         y         x         y
0 -0.264438  1.923135 -1.026059  0.135355 -0.619500 -0.285491
1  0.927272 -0.208940  0.302904  0.642432 -0.032399 -0.764902
2 -0.264273  1.477419 -0.386314 -1.659804 -0.217601 -0.431375
3 -0.871858 -1.191664 -0.348382  0.152576  1.100491  0.935773

In [17]: glued = glued.swaplevel(0, 1, axis=1).sortlevel(axis=1)

In [18]: glued
Out[18]: 
          A                   B                   C          
          x         y         x         y         x         y
0 -0.264438  1.923135 -1.026059  0.135355 -0.619500 -0.285491
1  0.927272 -0.208940  0.302904  0.642432 -0.032399 -0.764902
2 -0.264273  1.477419 -0.386314 -1.659804 -0.217601 -0.431375
3 -0.871858 -1.191664 -0.348382  0.152576  1.100491  0.935773

, .

, :

In [19]: glued.groupby(level=0, axis=1).mean()
Out[19]: 
          A         B         C
0  0.829349 -0.445352 -0.452496
1  0.359166  0.472668 -0.398650
2  0.606573 -1.023059 -0.324488
3 -1.031761 -0.097903  1.018132
+7

, .

pandas DataFrames DataFrame.add(): http://pandas.sourceforge.net/generated/pandas.DataFrame.add.html

, DataFrames , DataFrames, :

avgDataFrame = DataFrameList[0]

for i in range(1, len(DataFrameList)):
    avgDataFrame = avgDataFrame.add(DataFrameList[i])

avgDataFrame = avgDataFrame / len(DataFrameList)
+2

pandas.concat(). , concat DataFrames , pandas, .

, DataFrames, :

>>> x
          A         B         C
0 -0.264438 -1.026059 -0.619500
1  0.927272  0.302904 -0.032399
2 -0.264273 -0.386314 -0.217601
3 -0.871858 -0.348382  1.100491
>>> y
          A         B         C
0  1.923135  0.135355 -0.285491
1 -0.208940  0.642432 -0.764902
2  1.477419 -1.659804 -0.431375
3 -1.191664  0.152576  0.935773
>>> pandas.concat([x, y])
          A         B         C
0 -0.264438 -1.026059 -0.619500
1  0.927272  0.302904 -0.032399
2 -0.264273 -0.386314 -0.217601
3 -0.871858 -0.348382  1.100491
0  1.923135  0.135355 -0.285491
1 -0.208940  0.642432 -0.764902
2  1.477419 -1.659804 -0.431375
3 -1.191664  0.152576  0.935773
+1

More articles:


All Articles