英文:
Creating sub columns in Pandas Dataframes for Summary Statistics
问题
我正在处理地表水和地下水的水质数据。我想要创建一个汇总统计表，包括所有三个参数（pH、温度、盐度），并按样本采集地点（地表水与地下水）分组，如下所示：
| '地表水' | '地下水' |___________________________________________________________________________| 最小值 | 最大值 | 平均值 | 标准差 | 最小值 | 最大值 | 平均值 | 标准差'pH'
我设置Excel表格以收集数据，包括以下列：日期，监测ID（地表水或地下水），pH，温度和盐度。
如何使用Python来做到这一点？我熟悉groupby和describe()函数，但我不知道如何以我想要的方式组织它。任何帮助将不胜感激！
我尝试使用groupby函数来进行每个描述性统计，例如：
平均值 = df.\groupby('监测ID')\[['pH', 'SAL (ppt)', '温度 (°C)', 'DO (mg/L)']].mean()最小值 = df.\groupby('监测ID')\[['pH', 'SAL (ppt)', '温度 (°C)', 'DO (mg/L)']].min()
等等... 但我不知道如何将它们都整合到一个漂亮的表格中。
英文:
I am working with water quality data for both surface water locations and groundwater well locations. I would like to create a summary statistics table for all three of my parameters (pH, Temp, salinity) grouped by the location the samples were taken from (surface water vs. Groundwater) as shown below:
> | 'Surface Water' | 'Groundwater' |
> ___________________________________________________________________________
> | min | max | mean | std | min | max | mean | std
> 'pH'
The way I set up my Excel Sheet for data collection includes the following columns: Date, Monitoring ID (Either Surface Water or Groundwater), pH, Temp, and Salinity.
How can i tell python to do this? I am familiar with the groupby and describe() function but I don't know how to style organize it the way that I want. Any help would be appreciated!
I have tried using the groupby function for each descriptive stat for example:
mean = df.\groupby('Monitoring ID')\[['pH', 'SAL (ppt)', 'Temperature (°C)', 'DO (mg/L)']].mean()min = df.\groupby('Monitoring ID')\[['pH', 'SAL (ppt)', 'Temperature (°C)', 'DO (mg/L)']].min()
etc.... but I don't know how to incorporate it all into one nice table
答案1
得分: 1
你可以按照你提议的使用groupby_describe然后stack_transpose：
metrics = ['count', 'mean', 'std', 'min', 'max']out = df.groupby('Monitoring ID').describe().stack().T.loc[:, (slice(None), metrics)]
>>> outMonitoring ID 地下水 地表水计数 平均 标准差 最小 最大 计数 平均 标准差 最小 最大pH 159.0 6.979182 0.587316 6.00 7.98 141.0 6.991135 0.564097 6.00 7.99SAL (ppt) 159.0 1.976226 0.577557 1.02 2.99 141.0 1.917589 0.576650 1.01 2.99Temperature (°C) 159.0 13.466101 4.805317 4.13 21.78 141.0 13.099645 4.989240 4.03 21.61DO (mg/L) 159.0 1.984277 0.609071 1.00 2.99 141.0 1.939433 0.577651 1.00 2.96
英文:
You can use groupby_describe as you suggest then stack_transpose:
metrics = ['count', 'mean', 'std', 'min', 'max']out = df.groupby('Monitoring ID').describe().stack().T.loc[:, (slice(None), metrics)]
>>> outMonitoring ID Groundwater Surface Watercount mean std min max count mean std min maxpH 159.0 6.979182 0.587316 6.00 7.98 141.0 6.991135 0.564097 6.00 7.99SAL (ppt) 159.0 1.976226 0.577557 1.02 2.99 141.0 1.917589 0.576650 1.01 2.99Temperature (°C) 159.0 13.466101 4.805317 4.13 21.78 141.0 13.099645 4.989240 4.03 21.61DO (mg/L) 159.0 1.984277 0.609071 1.00 2.99 141.0 1.939433 0.577651 1.00 2.96
答案2
得分: 0
您可以使用agg和groupby来执行以下操作：
import pandas as pdimport numpy as np# 示例数据data = {'Date': ['2022-01-01', '2022-01-02', '2022-01-03', '2022-01-01', '2022-01-02', '2022-01-03'],'Monitoring ID': ['Surface Water', 'Surface Water', 'Surface Water', 'Groundwater', 'Groundwater', 'Groundwater'],'pH': [7.1, 7.2, 7.5, 7.8, 7.6, 7.4],'Temp': [10, 12, 9, 15, 13, 14],'Salinity': [0.1, 0.2, 0.3, 0.4, 0.5, 0.6]}df = pd.DataFrame(data)# 按'Monitoring ID'分组并计算摘要统计信息summary_stats = df.groupby('Monitoring ID').agg({'pH': ['min', 'max', 'mean', 'std'],'Temp': ['min', 'max', 'mean', 'std'],'Salinity': ['min', 'max', 'mean', 'std']})# 通过重命名重新组织列summary_stats.columns = ['_'.join(col).strip() for col in summary_stats.columns.values]# 摘要表print(summary_stats)
请原谅我，我仍在努力找出如何在这里演示代码的输出，但我希望这有所帮助。
英文:
You can use agg along with groupby:
import pandas as pdimport numpy as np# Sample datadata = {'Date': ['2022-01-01', '2022-01-02', '2022-01-03', '2022-01-01', '2022-01-02', '2022-01-03'],'Monitoring ID': ['Surface Water', 'Surface Water', 'Surface Water', 'Groundwater', 'Groundwater', 'Groundwater'],'pH': [7.1, 7.2, 7.5, 7.8, 7.6, 7.4],'Temp': [10, 12, 9, 15, 13, 14],'Salinity': [0.1, 0.2, 0.3, 0.4, 0.5, 0.6]}df = pd.DataFrame(data)# Group by 'Monitoring ID' and calculate summary statisticssummary_stats = df.groupby('Monitoring ID').agg({'pH': ['min', 'max', 'mean', 'std'],'Temp': ['min', 'max', 'mean', 'std'],'Salinity': ['min', 'max', 'mean', 'std']})# Reorganise column by renamingsummary_stats.columns = ['_'.join(col).strip() for col in summary_stats.columns.values]# Summary tableprint(summary_stats)
Pardon me I'm still trying to figure how to demonstrate the output of the code here but I hope this helps.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论