0

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!

user3757405
  • 51
  • 1
  • 8
  • 2
    use `df.fillna(method='ffill')` after you do `reindex` – moys Jan 22 '20 at 04:04
  • 2
    Does this answer your question? [How to replace NaNs by preceding values in pandas DataFrame?](https://stackoverflow.com/questions/27905295/how-to-replace-nans-by-preceding-values-in-pandas-dataframe) – BICube Jan 22 '20 at 04:08
  • The reindex method includes a parameter `method` which can be set to `ffill` to carry the last valid value forward. Documentation: https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.reindex.html – Brett Romero Jan 22 '20 at 05:56

2 Answers2

3

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')
Brett Romero
  • 343
  • 2
  • 12
-1

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
moys
  • 7,747
  • 2
  • 11
  • 42
  • There are several unnecessary steps here. The Series object also has a `reindex` method, no need to convert to a DataFrame. Also, reindex has a parameter built in (`method`) to fill in NaN values. – Brett Romero Jan 22 '20 at 06:04
  • The output of what you have put is a series, not a dataframe. OP suggested that we wanted to fill na values within a DATAFRAME (he may not be aware that what he has is a series). – moys Jan 22 '20 at 06:25
  • OP specifically initializes the data as a Series and does not even mention the word "dataframe" in the question... – Brett Romero Jan 22 '20 at 06:32