对 pandas 使用重采样和聚合时丢失字符串列 [英] Losing String column when using resample and aggregation with pandas

问题描述

我有一个具有以下结构的 DataFrame:

I have a DataFrame with the following structure:

df = df.set_index('timestamp')
print(df.head())

timestamp            id                         value
2018-12-31 23:00:00  5c8fea84763aae175afda38b   98.587768
2018-12-31 23:10:00  5c8fea84763aae175afda38b  107.232742
2018-12-31 23:20:00  5c8fea84763aae175afda38b  104.224153
2018-12-31 23:30:00  5c8fea84763aae175afda38b  104.090750
2018-12-31 23:40:00  5c8fea84763aae175afda38b   99.357023

我需要获取一个包含每日最大值和最小值以及平均值的新 DataFrame.我获取这些数据没有问题，我是这样做的:

I need to obtain a new DataFrame with daily max and min values, as well as the mean. I have no problem in obtaining this data and I do it this way:

df = df.resample('D').agg(['min', 'max', 'mean'], columns=['value'])

问题是我丢失了 id 列，我需要它来将新数据存储在数据库中.

The problem is that I loose the column id and I need it in order to store new data in a database.

这是我通过打印新 DataFrame 的头部得到的输出:

This is the output I get by printing the head of the new DataFrame:

timestamp   min        max         mean
2018-12-31  98.587768  107.641060  103.522250
2019-01-01  88.396180  109.506622  100.135128
2019-01-02  85.857570  112.420754   99.839120
2019-01-03  87.565014  113.419561   99.734654
2019-01-04  88.902704  112.186989   99.764259

如您所见，我丢失了 id 字段.

As you can see, I have lost id field.

推荐答案

将字典传递给 agg 以聚合多列.对于ID"，通过取第一个值进行聚合.

Pass a dictionary to agg to aggregate multiple columns. For "ID", aggregate by taking the first value.

这是一个例子:

df.resample('D').agg({'id': 'first', 'value': ['mean', 'max']})

                                  id       value            
                               first        mean         max
timestamp                                                   
2018-12-31  5c8fea84763aae175afda38b  102.698487  107.232742

如果您愿意，可以通过传递元组来重命名输出列:

If you so wish, you can rename the output columns by passing tuples:

df.resample('D').agg({
    'id': [('A', 'first')], 'value': [('B', 'mean'), ('C', 'max')]})

                                  id       value            
                                   A           B           C
timestamp                                                   
2018-12-31  5c8fea84763aae175afda38b  102.698487  107.232742

另请参阅使用 pandas GroupBy.agg() 对同一列进行多次聚合.

这篇关于对 pandas 使用重采样和聚合时丢失字符串列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！