python - Pandas 将数据放入 df 中

Question

我有一个时间序列数据的 CSV 文件，其中一列标记为 oatSensor(根据不同过程的条件过滤外部空气温度传感器)，我正在寻找一种对数据进行分类的方法如果可能的话，以“每天”为基础。

例如，我可以使用下面的代码对整个数据集进行分类。对于外部空气温度值，增量为 -5 到 55。

import pandas as pd

df = pd.read_csv('ahu1OatFiltHourFilt.csv', index_col='Date', parse_dates=True)
df.describe()

cats = ['-5--1',
        '0-4','5-9','10-14','15-19','20-24',
        '25-29','30-34','35-39','40-44','45-49','50-54']
bins = [-5, 0,
        5, 10, 15, 20, 25, 30, 35,
       40, 45, 50, 55]

df2 = (df.groupby([df.index, pd.cut(df['oatSensor'], bins, labels=cats)])
            .size()
            .unstack(fill_value=0)
            .reindex(columns=cats, fill_value=0))

df2.sum(axis = 0)

这将输出:

oatSensor

-5--1      3
0-4        6
5-9       19
10-14     49
15-19     65
20-24    125
25-29    230
30-34    201
35-39    171
40-44    205
45-49    179
50-54    183
dtype: int64

但最终我希望找到一种方法:

1) 为每个 bin 类别创建单独的 df 列 catsdf4 = pd.DataFrame(df2, columns=cats)

2) 每天对整个数据帧重新采样，以获得每个 bin 的总和

希望我正在使用的数据在此 git repo 中.

感谢您的帮助。

编辑如果我使用 df3 = df2.stack() ，这给了我我正在寻找的东西，但重新采样除外，但是我将如何实现创建单独的 猫 数据帧然后可以每天重新采样一次吗？

Date                 oatSensor
2018-09-26 05:00:00  -5--1        0
                     0-4          0
                     5-9          0
                     10-14        0
                     15-19        0
                     20-24        0
                     25-29        0
                     30-34        0
                     35-39        0
                     40-44        0
                     45-49        0
                     50-54        1
2018-09-26 06:00:00  -5--1        0
                     0-4          0
                     5-9          0
                     10-14        0
                     15-19        0
                     20-24        0
                     25-29        0
                     30-34        0
                     35-39        0
                     40-44        0
                     45-49        0
                     50-54        1

Answer 1

试试这个代码。祝你好运;)

import pandas as pd

distance = 5
url = 'https://raw.githubusercontent.com/bbartling/Data/master/ahu1OatFiltHourFilt.csv'
df = pd.read_csv(url)
df['Date'] = pd.to_datetime(df['Date'])
min = df.oatSensor.min()
max = df.oatSensor.max()
min = int(min - (min % distance))
max = int(max + distance - (max % distance))
labels = ["{0} - {1}".format(i, i + distance) for i in range(min, max, 
distance)]
df['group'] = pd.cut(df.oatSensor, range(min, max + distance, distance), 
right=False, labels=labels)
df2 = df.groupby(['group']).count()
df['one'] = 1
df3 = df.pivot_table('one','Date','group')
print(df3.tail())

输出:

# group                -5 - 0  0 - 5  5 - 10  ...  85 - 90  90 - 95  95 - 100
# Date                                        ...                            
# 2019-07-06 13:00:00     NaN    NaN     NaN  ...      NaN      NaN       NaN
# 2019-07-06 14:00:00     NaN    NaN     NaN  ...      NaN      NaN       NaN
# 2019-07-06 15:00:00     NaN    NaN     NaN  ...      NaN      NaN       NaN
# 2019-07-06 16:00:00     NaN    NaN     NaN  ...      NaN      NaN       NaN
# 2019-07-09 15:00:00     NaN    NaN     NaN  ...      1.0      NaN       NaN