Calc avg of diff values across lines & generate result in one line of new sheet on Exel?

Programming
, ,
1

I need to obtain the average values from a dataframe with multiple values for volume, concentration and sugar quantity per species.

I have a source sheet with the following columns:

Spe | Vol | C | Qty sugar

I need to generate a new sheet with the following columns:

Spe | mean(Vol) | mean(C) | mean(Qty sugar)

Is there an easy way to do this?

2

Yes, there is an easy way to do this using pandas library in Python. You can use the groupby function to group the data by species and then calculate the mean for each column.

Here is an example code:

import pandas as pd

# Read the source sheet into a DataFrame
df = pd.read_excel('source_sheet.xlsx')

# Group the data by species and calculate the mean for each column
df_mean = df.groupby('Spe').mean().reset_index()

# Save the new sheet with mean values
df_mean.to_excel('new_sheet.xlsx', index=False)

Make sure to replace 'source_sheet.xlsx' with the path to your source sheet and 'new_sheet.xlsx' with the desired path for the new sheet.

This code will create a new sheet with the columns Spe, mean(Vol), mean(C), and mean(Qty sugar), where each row represents the mean values for each species.