問題我有一個包含以 1 分鐘間隔采樣的財務數據的數據框。有時可能會丟失一兩行數據。我正在尋找一種好的（簡單而有效的）方法將新行插入到數據框中缺少數據的點。除了包含時間戳的索引外，新行可以為空。例如： #Example Input--------------------------------------------- open high low close 2019-02-07 16:01:00 124.624 124.627 124.647 124.617 2019-02-07 16:04:00 124.646 124.655 124.664 124.645 # Desired Ouput-------------------------------------------- open high low close 2019-02-07 16:01:00 124.624 124.627 124.647 124.617 2019-02-07 16:02:00 NaN NaN NaN NaN 2019-02-07 16:03:00 NaN NaN NaN NaN 2019-02-07 16:04:00 124.646 124.655 124.664 124.645 我目前的方法基于這篇文章 - 使用 Pandas 在時間序列數據中查找缺失的分鐘數據- 僅建議如何識別差距。不是如何填充它們。我正在做的是創建一個 1 分鐘間隔的 DateTimeIndex。然后使用這個索引，我創建了一個全新的數據幀，然后可以將其合并到我的原始數據幀中，從而填補空白。代碼如下所示。這樣做的方式似乎很復雜。我想知道是否有更好的方法。也許重新采樣數據？import pandas as pdfrom datetime import datetime# Initialise prices dataframe with missing dataprices = pd.DataFrame([[datetime(2019,2,7,16,0), 124.634, 124.624, 124.65, 124.62],[datetime(2019,2,7,16,4), 124.624, 124.627, 124.647, 124.617]])prices.columns = ['datetime','open','high','low','close']prices = prices.set_index('datetime')print(prices)# Create a new dataframe with complete set of time intervalsidx_ref = pd.DatetimeIndex(start=datetime(2019,2,7,16,0), end=datetime(2019,2,7,16,4),freq='min')df = pd.DataFrame(index=idx_ref)# Merge the two dataframes prices = pd.merge(df, prices, how='outer', left_index=True, right_index=True)print(prices)
3 回答
繁花不似錦
TA貢獻1851條經驗 獲得超4個贊
使用DataFrame.asfreq與Datetimeindex：
prices = prices.set_index('datetime').asfreq('1Min')
print(prices)
open high low close
datetime
2019-02-07 16:00:00 124.634 124.624 124.650 124.620
2019-02-07 16:01:00 NaN NaN NaN NaN
2019-02-07 16:02:00 NaN NaN NaN NaN
2019-02-07 16:03:00 NaN NaN NaN NaN
2019-02-07 16:04:00 124.624 124.627 124.647 124.617
largeQ
TA貢獻2039條經驗 獲得超8個贊
更手動的答案是：
from datetime import datetime, timedelta
from dateutil import parser
import pandas as pd
df = pd.DataFrame({
'a': ['2021-02-07 11:00:30', '2021-02-07 11:00:31', '2021-02-07 11:00:35'],
'b': [64.8, 64.8, 50.3]
})
max_dt = parser.parse(max(df['a']))
min_dt = parser.parse(min(df['a']))
dt_range = []
while min_dt <= max_dt:
dt_range.append(min_dt.strftime("%Y-%m-%d %H:%M:%S"))
min_dt += timedelta(seconds=1)
complete_df = pd.DataFrame({'a': dt_range})
final_df = complete_df.merge(df, how='left', on='a')
它轉換以下數據幀：
a b
0 2021-02-07 11:00:30 64.8
1 2021-02-07 11:00:31 64.8
2 2021-02-07 11:00:35 50.3
到：
a b
0 2021-02-07 11:00:30 64.8
1 2021-02-07 11:00:31 64.8
2 2021-02-07 11:00:32 NaN
3 2021-02-07 11:00:33 NaN
4 2021-02-07 11:00:34 NaN
5 2021-02-07 11:00:35 50.3
我們可以稍后填充它的空值
添加回答
舉報
0/150
提交
取消