英文:
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)]
>>> out
Monitoring ID 地下水 地表水
计数 平均 标准差 最小 最大 计数 平均 标准差 最小 最大
pH 159.0 6.979182 0.587316 6.00 7.98 141.0 6.991135 0.564097 6.00 7.99
SAL (ppt) 159.0 1.976226 0.577557 1.02 2.99 141.0 1.917589 0.576650 1.01 2.99
Temperature (°C) 159.0 13.466101 4.805317 4.13 21.78 141.0 13.099645 4.989240 4.03 21.61
DO (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)]
>>> out
Monitoring ID Groundwater Surface Water
count mean std min max count mean std min max
pH 159.0 6.979182 0.587316 6.00 7.98 141.0 6.991135 0.564097 6.00 7.99
SAL (ppt) 159.0 1.976226 0.577557 1.02 2.99 141.0 1.917589 0.576650 1.01 2.99
Temperature (°C) 159.0 13.466101 4.805317 4.13 21.78 141.0 13.099645 4.989240 4.03 21.61
DO (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 pd
import 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 pd
import numpy as np
# Sample data
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)
# Group by 'Monitoring ID' and calculate summary statistics
summary_stats = df.groupby('Monitoring ID').agg({'pH': ['min', 'max', 'mean', 'std'],
'Temp': ['min', 'max', 'mean', 'std'],
'Salinity': ['min', 'max', 'mean', 'std']})
# Reorganise column by renaming
summary_stats.columns = ['_'.join(col).strip() for col in summary_stats.columns.values]
# Summary table
print(summary_stats)
Pardon me I'm still trying to figure how to demonstrate the output of the code here but I hope this helps.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论