How do I calculate the average of dataframes created by batchsizes of csv file?
Input:
A,1
B,2
B,1
C,2
A,1
B,3
B,1
C,1
A,1
B,2
B,1
C,3
I want to group them by 4 rows (A,B,B,C) and calculate the average of 2nd column.
output:
A, average(1,1,1)
B, average(2,3,2)
B, average(1,1,1)
C, average(1,2,3)
First create a cumcount level for each separate batch that way you can separate the first 'B' from the second 'B' (assuming an 'A' begins each new group). Then group over the letter and this newly created group label.
Assuming your columns are labeled 0 and 1:
import pandas as pd
df = pd.read_clipboard(sep=',', header=None)
df['gp'] = df.groupby([df[0].eq('A').cumsum(), 0]).cumcount()
df.groupby([0, 'gp'])[1].mean()
0 gp
A 0 1.000000
B 0 2.333333
1 1.000000
C 0 2.000000
Name: 1, dtype: float64
Assuming the columns is named label, value, since you are averaging over a fixed number of rows, a reshape would be fine:
nrows=4
pd.DataFrame({'label': df['label'].iloc[:nrows],
'value':df['value'].values.reshape(-1,nrows).mean(axis=0)
})
Output:
label value
0 A 1.000000
1 B 2.333333
2 B 1.000000
3 C 2.000000
You can groupby them like this and then compute the mean.
df.groupby([df.index%4, df[0]], as_index=False).mean().rename(columns={0:'labels', 1:'mean'})
labels mean
0 A 1.000000
1 B 2.333333
2 B 1.000000
3 C 2.000000