Fill the missing date values in a Pandas Dataframe column
I think you can use resample
with ffill
or bfill
, but before set_index
from column PriceDate
:
print (data) ID PriceDate OpenPrice HighPrice0 1 6/24/2016 1 21 2 6/23/2016 3 42 2 6/22/2016 5 63 2 6/21/2016 7 84 2 6/20/2016 9 105 2 6/17/2016 11 126 2 6/16/2016 13 14
data['PriceDate'] = pd.to_datetime(data['PriceDate'], format='%m/%d/%Y')data = data.sort_values(by=['PriceDate'], ascending=[True])data.set_index('PriceDate', inplace=True)print (data) ID OpenPrice HighPricePriceDate 2016-06-16 2 13 142016-06-17 2 11 122016-06-20 2 9 102016-06-21 2 7 82016-06-22 2 5 62016-06-23 2 3 42016-06-24 1 1 2data = data.resample('D').ffill().reset_index()print (data) PriceDate ID OpenPrice HighPrice0 2016-06-16 2 13 141 2016-06-17 2 11 122 2016-06-18 2 11 123 2016-06-19 2 11 124 2016-06-20 2 9 105 2016-06-21 2 7 86 2016-06-22 2 5 67 2016-06-23 2 3 48 2016-06-24 1 1 2
data = data.resample('D').bfill().reset_index()print (data) PriceDate ID OpenPrice HighPrice0 2016-06-16 2 13 141 2016-06-17 2 11 122 2016-06-18 2 9 103 2016-06-19 2 9 104 2016-06-20 2 9 105 2016-06-21 2 7 86 2016-06-22 2 5 67 2016-06-23 2 3 48 2016-06-24 1 1 2