Skip to content
Advertisement

Create new dataframe that contain the average value from some of the columns in the old dataframe

I have a dataframe extracted from a csv file. I want to iterate a data process where only some of the columns’s data is the mean of n rows, while the rest of the columns is the first row for each iteration.

For example, the data extracted from the csv consisted of 100 rows and 6 columns. I have a variable n_AVE = 6, which tells the code to average the data per 6 rows.

JavaScript

Take only the first row of [TIME, D, E] columns
Average the data per n_AVE (6) from [A, B, C] columns.
I want to create a new dataframe which looks like this

JavaScript

The code is like this:

JavaScript

But the code is not working because apparently when I use pandas.mean(), the dataframe’s format changed into like this

JavaScript

How to use pandas.mean() without losing the old format?
Or should I not use pandas.mean() and just create my own averaging code?

Advertisement

Answer

You can group the dataframe by the grouper np.arange(len(df)) // 6 which groups the dataframe every six rows, then aggregate the columns using the desired aggregation functions to get the result, optionally reindex along axis=1 to reorder the columns

JavaScript

Define aggegation functions using columns index:

JavaScript

Result

JavaScript
User contributions licensed under: CC BY-SA
2 People found this is helpful
Advertisement