扩展datetime列以包括所有数据行-Pandas [英] Expanding datetime column to include all rows of data - pandas

查看:81
本文介绍了扩展datetime列以包括所有数据行-Pandas的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我有一个数据集,该数据集的预测数据会在42小时内模棱两可地更新.这是一个示例:

I have dataset which has forecast data which is updated ambiguously over a 42 hour period. Here is a sample:

df_old = pd.DataFrame({'IssueDatetime': ['2010-01-01 09:00:00', '2010-01-01 09:00:00', '2010-01-01 09:00:00','2010-01-01 09:00:00','2010-01-01 09:00:00'],
                   'endtime':['2010-01-03 03:00:00','2010-01-03 03:00:00','2010-01-03 03:00:00','2010-01-03 03:00:00','2010-01-03 03:00:00'],
                   'Regions': ['EAST COAST-CAPE ST FRANCIS AND SOUTH', 'EAST COAST-CAPE ST FRANCIS AND SOUTH', 'EAST COAST-CAPE ST FRANCIS AND SOUTH','NORTHEAST COAST','NORTHEAST COAST'],
                  'forecastTime': ['2010-01-01 09:00:00','2010-01-01 15:00:00','2010-01-01 19:00:00','2010-01-01 09:00:00','2010-01-01 12:00:00'],
                   'forecast_Dir':[150,180,45,45,45],
                   'windSpeed':[20,90,35,45,15]})

问题在于df ['forecastTime']和df ['endtime]的小时之间存在差距.我尝试使用有限的熊猫知识对数据进行分组和重新采样,但是由于重复了日期,因此无法获得日期时间索引.

The issue is the gaps between the hours of df['forecastTime'] and df['endtime]. I have tried using my limited pandas knowledge to group and resample the data, but, because the dates are repeated I cannot get a datetime index.

最终,我的目标是扩展数据框,以便在数据框中原始小时之间的小时数具有自己的行,直到结束时段为止.

Ultimately my goal is to expand the dataframe so the hours in between the original hours in the dataframe have rows of their own right up until the end period...

所需输出示例:

df_new = pd.DataFrame({'IssueDatetime': [ '2010-01-01 09:00:00', '2010-01-01 09:00:00', '2010-01-01 09:00:00', '2010-01-01 09:00:00', '2010-01-01 09:00:00', '2010-01-01 09:00:00','2010-01-01 09:00:00'],
                   'endtime':['2010-01-03 03:00:00','2010-01-03 03:00:00','2010-01-03 03:00:00','2010-01-03 03:00:00','2010-01-03 03:00:00','2010-01-03 03:00:00','2010-01-03 03:00:00'],
                   'Regions': ['EAST COAST-CAPE ST FRANCIS AND SOUTH', 'EAST COAST-CAPE ST FRANCIS AND SOUTH','EAST COAST-CAPE ST FRANCIS AND SOUTH','EAST COAST-CAPE ST FRANCIS AND SOUTH','EAST COAST-CAPE ST FRANCIS AND SOUTH','EAST COAST-CAPE ST FRANCIS AND SOUTH','EAST COAST-CAPE ST FRANCIS AND SOUTH'],
                  'forecastTime': ['2010-01-01 09:00:00','2010-01-01 10:00:00','2010-01-01 11:00:00','2010-01-01 12:00:00','2010-01-01 13:00:00','2010-01-01 14:00:00','2010-01-01 15:00:00'],
                   'forecast_Dir':[150,150,150,150,150,150,180],
                   'windSpeed':[20,20,20,20,20,20,90]})

请注意第一个区域,介于df ['forecastTime'] ='2010-01-01 09:00:00'和df ['forecastTime'] ='2010-01-01 15:00:00之间的小时数'应该是自己的行.本质上,我正在寻求上采样以填补缺失的时间.

Note for the first region, the hours between df['forecastTime'] = '2010-01-01 09:00:00' and df['forecastTime'] = '2010-01-01 15:00:00' should be rows of their own. Essentially I'm looking to upsample to fill in missing hours.

-原始数据框

            IssueDatetime             endtime  \
0     2013-01-01 09:00:00 2013-01-03 03:00:00   
1     2013-01-01 09:00:00 2013-01-03 03:00:00   
2     2013-01-01 09:00:00 2013-01-03 03:00:00   
3     2013-01-01 09:00:00 2013-01-03 03:00:00   
4     2013-01-01 09:00:00 2013-01-03 03:00:00   
...                   ...                 ...   
53585 2016-12-30 09:00:00 2017-01-01 03:00:00   
53586 2016-12-30 09:00:00 2017-01-01 03:00:00   
53587 2016-12-30 09:00:00 2017-01-01 03:00:00   
53588 2016-12-30 09:00:00 2017-01-01 03:00:00   
53589 2016-12-30 09:00:00 2017-01-01 03:00:00   

                                     Regions        forecastTime  \
0                                SOUTH COAST 2013-01-01 09:00:00   
1                                SOUTH COAST 2013-01-01 18:00:00   
2                                SOUTH COAST 2013-01-02 06:00:00   
3                                SOUTH COAST 2013-01-02 13:00:00   
4      EAST COAST-CAPE ST  FRANCIS AND SOUTH 2013-01-01 09:00:00   
...                                      ...                 ...   
53585               SOUTHWESTERN GRAND BANKS 2016-12-30 18:00:00   
53586               SOUTHWESTERN GRAND BANKS 2016-12-31 09:00:00   
53587               SOUTHWESTERN GRAND BANKS 2016-12-31 15:00:00   
53588               SOUTHWESTERN GRAND BANKS 2016-12-31 18:00:00   
53589               SOUTHWESTERN GRAND BANKS 2017-01-01 00:00:00   

       forecastHour forecast_Dir forecast_WindSpeed_low  \
0               0.0          270                     35   
1               9.0          270                     25   
2              21.0          225                     15   
3              28.0          270                     35   
4               0.0          270                     35   
...             ...          ...                    ...   
53585           9.0          135                     40   
53586          24.0          135                     40   
53587          30.0          135                     40   
53588          33.0          315                     25   
53589          39.0          315                     25   

      forecast_WindSpeed_gust forecast_WindSpeed_high  \
0                        None                    None   
1                        None                    None   
2                        None                    None   
3                        None                    None   
4                        None                    None   
...                       ...                     ...   
53585                    None                      50   
53586                    None                     50    
53587                    None                      50   
53588                    None                      35   
53589                    None                    None   

      forecast_WindSpeed_exception_1_type forecast_Dir_exception_1  \
0                                     NaN                      NaN   
1                                     NaN                      NaN   
2                                     NaN                      NaN   
3                                     NaN                      NaN   
4                                     NaN                      NaN   
...                                   ...                      ...   
53585                                 NaN                      NaN   
53586          OVER NORTHWESTERN SECTIONS                      315   
53587                                 NaN                      NaN   
53588                                 NaN                      NaN   
53589                                 NaN                      NaN   

      forecast_WindSpeed_low_exception_1 forecast_WindSpeed_high_exception_1  
0                                    NaN                                 NaN  
1                                    NaN                                 NaN  
2                                    NaN                                 NaN  
3                                    NaN                                 NaN  
4                                    NaN                                 NaN  
...                                  ...                                 ...  
53585                                NaN                                 NaN  
53586                                 25                                None  
53587                                NaN                                 NaN  
53588                                NaN                                 NaN  
53589                                NaN                                 NaN  

推荐答案

IIUC,首先需要将'forecastTime'列转换为日期时间,然后将'forecastTime'列设置为索引以进行重新采样,然后按'Regions',按小时执行resample,并用ffill填充NaN值:

IIUC, first you need to convert the 'forecastTime' column to datetime, then you set 'forecastTime' column as index to do the resample, after that group by 'Regions', do the resample by hours, and fill the NaN values with ffill:

df_old['forecastTime'] = pd.to_datetime(df_old['forecastTime'])
df_new = df_old.set_index('forecastTime')
               .groupby('Regions',as_index=False)
               .resample('H').ffill().droplevel(0).reset_index()
print(df_new.head())


为避免由于重复的日期而可能导致的错误(非唯一索引),您可以尝试以下操作:


To avoid a possible error(non-unique index) because of duplicate dates, you can try this:

df_new = df_old.groupby('Regions',as_index=False)
   .apply(lambda x: x.set_index('forecastTime')
   .resample('H').ffill()).droplevel(0).reset_index()


输出:


Output:

         forecastTime        IssueDatetime              endtime                               Regions  forecast_Dir  windSpeed
0 2010-01-01 09:00:00  2010-01-01 09:00:00  2010-01-03 03:00:00  EAST COAST-CAPE ST FRANCIS AND SOUTH           150         20
1 2010-01-01 10:00:00  2010-01-01 09:00:00  2010-01-03 03:00:00  EAST COAST-CAPE ST FRANCIS AND SOUTH           150         20
2 2010-01-01 11:00:00  2010-01-01 09:00:00  2010-01-03 03:00:00  EAST COAST-CAPE ST FRANCIS AND SOUTH           150         20
3 2010-01-01 12:00:00  2010-01-01 09:00:00  2010-01-03 03:00:00  EAST COAST-CAPE ST FRANCIS AND SOUTH           150         20
4 2010-01-01 13:00:00  2010-01-01 09:00:00  2010-01-03 03:00:00  EAST COAST-CAPE ST FRANCIS AND SOUTH           150         20

这篇关于扩展datetime列以包括所有数据行-Pandas的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆