Pandas：有没有办法1）在数据帧的每一行上方添加两个新的空行，2）用相同的值填充行？

Question

我有一个简单的数据框（来自热带降雨测量任务TRMM的数据，如果有助于提供上下文），一列是datetime，一列是降水测量，如下所示：

                        ppt
date            

1998-01-01 03:00:00     0.00    
1998-01-01 06:00:00     0.00    
1998-01-01 09:00:00     0.03    
1998-01-01 12:00:00     0.20

读数为每三小时一次，数值为前三小时每小时降雨量的3小时平均值。我想创建一个数据帧，其中包含每小时的降雨量测量值，因此它看起来是这样的：

                        ppt
date            
1998-01-01 01:00:00     0.00
1998-01-01 02:00:00     0.00    
1998-01-01 03:00:00     0.00
1998-01-01 04:00:00     0.00
1998-01-01 05:00:00     0.00    
1998-01-01 06:00:00     0.00
1998-01-01 07:00:00     0.03
1998-01-01 08:00:00     0.03    
1998-01-01 09:00:00     0.03
1998-01-01 10:00:00     0.20
1998-01-01 11:00:00     0.20    
1998-01-01 12:00:00     0.20    

你知道我该怎么做吗

Answer 1

IIUC

为了得到上面的结果：

# repeated decreasing number of hours
# [2 hr, 1 hr, 0 hr, 2 hr, 1 hr, 0 hr, ...]
d = np.tile(np.arange(3)[::-1], len(df)) * pd.Timedelta(1, unit='H')

# repeat the index 3 times for every entry
# [3:00, 3:00, 3:00, 6:00, 6:00, 6:00, ...]
i = df.index.repeat(3)
df_ = df.loc[i]

# take care of differences
# [3:00, 3:00, 3:00, 6:00, 6:00, 6:00, ...]
#  minus
# [2 hr, 1 hr, 0 hr, 2 hr, 1 hr, 0 hr, ...]
# [1:00, 2:00, 3:00, 4:00, 5:00, 6:00, ...]
df_.index -= d

df_

                      ppt
date                     
1998-01-01 01:00:00  0.00
1998-01-01 02:00:00  0.00
1998-01-01 03:00:00  0.00
1998-01-01 04:00:00  0.00
1998-01-01 05:00:00  0.00
1998-01-01 06:00:00  0.00
1998-01-01 07:00:00  0.03
1998-01-01 08:00:00  0.03
1998-01-01 09:00:00  0.03
1998-01-01 10:00:00  0.20
1998-01-01 11:00:00  0.20
1998-01-01 12:00:00  0.20

---

asfreq和resample

只会带你走这么远

df.asfreq('H').bfill()

                      ppt
date                     
1998-01-01 03:00:00  0.00
1998-01-01 04:00:00  0.00
1998-01-01 05:00:00  0.00
1998-01-01 06:00:00  0.00
1998-01-01 07:00:00  0.03
1998-01-01 08:00:00  0.03
1998-01-01 09:00:00  0.03
1998-01-01 10:00:00  0.20
1998-01-01 11:00:00  0.20
1998-01-01 12:00:00  0.20

我们错过了

1998-01-01 01:00:00  0.00
1998-01-01 02:00:00  0.00

一开始

Answer 2

如果正确指定开始时间，则可以将重采样与回填一起使用：

import pandas as pd
import numpy as np


#specify start and end times so that the range to fill is clear
start = pd.Timestamp('1998-01-01 00:00:00')
end = pd.Timestamp('1998-01-01 12:00:00')
t = np.linspace(start.value, end.value, 5)
t = pd.to_datetime(t)
df=pd.DataFrame(index=t)

#populate existing values
df['ppt']=[0.,0.,0.,0.03,0.2]

#resample and fill backwards
df.resample('1H').bfill()