Add missing date index in dataframe

Question

I uploaded CSV files with a datetime index, which is the last day of the months of the year. I wanted to fill in the missing dates with blank values ​​as strings.

Below is my CSV file structure

Date Australia China 2011-01-31 4.75 5.81 2011-02-28 4.75 5.81 2011-03-31 4.75 6.06 2011-04-30 4.75 6.06 

I want to fill all dates in a month with empty columns.

I tried the following code, but it does not work.

 import pandas as pd df = pd.read_csv("data.csv", index_col="Date") df.reindex(pd.date_range("2011-01-01", "2011-10-31"), fill_value="NaN") 

Answer 1

You must install DatetimeIndex in your data framework, so I would change your code to:

 import pandas as pd df = pd.read_csv("data.csv", index_col="Date") df.index = pd.DatetimeIndex(df.index) df = df.reindex(pd.date_range("2011-01-01", "2011-10-31"), fill_value="NaN") df.to_csv('test.csv') 

That should work.

EDIT: add test result:

 ... 2011-01-24,NaN,NaN 2011-01-25,NaN,NaN 2011-01-26,NaN,NaN 2011-01-27,NaN,NaN 2011-01-28,NaN,NaN 2011-01-29,NaN,NaN 2011-01-30,NaN,NaN 2011-01-31,4.75,5.81 2011-02-01,NaN,NaN 2011-02-02,NaN,NaN 2011-02-03,NaN,NaN 2011-02-04,NaN,NaN 2011-02-05,NaN,NaN 2011-02-06,NaN,NaN ...