-2

I want to take a dataframe that's a million lines long, and summarize it so I take the columnwise mean of every block of 20 rows. Is there an easy way to do this?

Rob
  • 3,333
  • 5
  • 28
  • 71
  • np.split can be used to break it into smaller segments, then I could take the mean of those but it doesn't seem to be the best way. – Rob Aug 12 '15 at 18:59
  • Maybe show us what you've tried. Pandas has lots of rolling aggregation, resample, and grouping operations. – jhamman Aug 12 '15 at 19:08

2 Answers2

2

Here is another way using groupby according to integer division // and then .agg('mean').

df = pd.DataFrame(np.random.randn(50,2), columns=list('AB'))
df

         A       B
0  -0.6679 -0.3786
1   0.4253  1.0187
2   0.6159 -1.2768
3  -1.0202 -0.1413
4   0.2444  0.4939
5  -0.2606  0.1346
6  -1.2305  0.6479
7   0.2113 -1.0190
..     ...     ...
42 -0.0498 -1.3164
43  0.6948  0.5469
44  0.2718  0.2487
45 -2.9541 -0.9083
46 -0.5636 -0.4476
47 -0.1167  1.1087
48 -0.3220 -3.1022
49 -0.6414 -0.2629

[50 rows x 2 columns]

# the integer division
df.index//20

Int64Index([0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 1,
            1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 2, 2, 2, 2,
            2, 2, 2, 2, 2, 2],
           dtype='int64')


df.groupby(df.index//20).agg('mean')

        A       B
0 -0.9882 -0.0433
1 -2.4081  1.5017
2 -4.2048 -3.3826
JoeCondron
  • 8,546
  • 3
  • 27
  • 28
Jianxun Li
  • 24,004
  • 10
  • 58
  • 76
  • 1
    Note that you can pass `np.arange(len(df))//20` to groupby in case the index isn't already the standard. – DSM Aug 12 '15 at 19:09
0
    data = np.array([])
    result2 = np.split(result,96158)
    for each in range(len(result2)):
        data = np.append(data, np.array(result2[each].mean()))

this works but I'm not in love with it, assuming the length is 96158*20

Rob
  • 3,333
  • 5
  • 28
  • 71