CODEWITHSUNDEEP
pythonpandastime-seriesdataset
user3757405
user3757405

Reputation: 51

How to fill in missing dates and values in a Pandas DataFrame?

so the data set I am using is only business days but I want to change the date index such that it reflects every calendar day. When I use reindex and have to use reindex(), I am unsure how to use 'fill value' field of reindex to inherit the value above. 

import pandas as pd

idx = pd.date_range("12/18/2019","12/24/2019")

df = pd.Series({'12/18/2019':22.63,
                '12/19/2019':22.2,
                '12/20/2019':21.03,
                '12/23/2019':17,
                '12/24/2019':19.65})
df.index = pd.DatetimeIndex(df.index)
df = df.reindex()

Currently, my data set looks like this.

enter image description here

However, when I use reindex I get the below result

enter image description here

In reality I want it to inherit the values directly above if it is a NaN result so the data set becomes the following

enter image description here

Thank you guys for your help!

Upvotes: 0

Views: 1426

Answers (2)

Brett Romero
Brett Romero

Reputation: 353

You were close! You just need to pass the index you want to reindex on (idx in this case) as a parameter to the reindex method, and then you can set the method parameter to 'ffill' to propagate the last valid value forward. 

idx = pd.date_range("12/18/2019","12/24/2019")

df = pd.Series({'12/18/2019':22.63,
                '12/19/2019':22.2,
                '12/20/2019':21.03,
                '12/23/2019':17,
                '12/24/2019':19.65})
df.index = pd.DatetimeIndex(df.index)
df = df.reindex(idx, method='ffill')

Upvotes: 3

moys
moys

Reputation: 8033

It seems that you have created a 'Series', not a dataframe. See if the code below helps you.

df = df.to_frame().reset_index() #to convert series to dataframe
df = df.fillna(method='ffill')
print(df)

Output You will have to rename columns

         index  0
0   2019-12-18  22.63
1   2019-12-19  22.20
2   2019-12-20  21.03
3   2019-12-21  21.03
4   2019-12-22  21.03
5   2019-12-23  17.00
6   2019-12-24  19.65

Upvotes: -1

Related Questions