在 pandas 数据框中填写缺少的时间 [英] Fill in missing hours in a pandas dataframe
问题描述
我有一个包含每小时数据的数据框:
I have a dataframe that contains hourly data:
area date hour output
H1 2018-07-01 07:00:00 150
H1 2018-07-01 08:00:00 150
H1 2018-07-01 09:00:00 100
H1 2018-07-01 11:00:00 150
H2 2018-07-01 09:00:00 100
H2 2018-07-01 10:00:00 50
H2 2018-07-01 11:00:00 50
H2 2018-07-01 12:00:00 150
但是数据仅包含输出时数的行，如何为输出0的每个区域填写缺少的时数?例如，为H1添加两行:
but the data only contains row for the hours when there was output, how can I fill in the missing hours for each area with output 0? For example add two rows for H1:
area date hour output
H1 2018-07-01 10:00:00 0
H1 2018-07-01 12:00:00 0
我可以假设所有区域的最小和最大小时数是采样周期的开始和结束时间(在这种情况下为7:00:00和12:00:00)
I can assume that the min and max hour for all areas are the beginning and end of the sample period (in this case 7:00:00 and 12:00:00)
现在，我正在创建一个数据框，其中包含每个区域从7:00到12:00的所有小时，然后将我的数据与该数据框合并，然后用0填充NaN.这非常慢，因为我的数据集可以包含数百万行.
Right now, I'm creating a dataframe containing all the hours from 7:00 to 12:00 for each area and then doing a merge of my data with that dataframe, and then filling the NaN with 0s. This is very slow as my data set can have millions of rows.
还有更好的方法吗?
推荐答案
您可以使用groupby
df['Datetime']=pd.to_datetime(df.date+' '+df.hour)# combine hour and date to datetime
df.drop(['date','hour'],inplace=True,axis = 1)# drop duplicate infomation
df.groupby('area').\
apply(lambda x : x.set_index('Datetime').resample('H').mean().fillna(0)).\
reset_index()
Out[662]:
area Datetime output
0 H1 2018-07-01 07:00:00 150.0
1 H1 2018-07-01 08:00:00 150.0
2 H1 2018-07-01 09:00:00 100.0
3 H1 2018-07-01 10:00:00 0.0
4 H1 2018-07-01 11:00:00 150.0
5 H2 2018-07-01 09:00:00 100.0
6 H2 2018-07-01 10:00:00 50.0
7 H2 2018-07-01 11:00:00 50.0
8 H2 2018-07-01 12:00:00 150.0
这篇关于在 pandas 数据框中填写缺少的时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
