Add missing date index in dataframe

Question

I have loaded a CSV files with index datetime which is the last day of months in a year. I wanted to fill missing dates with empty values as rows.

Following 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 the dates in a month with empty columns.

I tried the following code but it is not working.

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")

Fabio Lamanna · Accepted Answer · 2016-01-09 11:00:42Z

15

You have to set the DatetimeIndex on your dataframe, so I would modify your code into:

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')

This should work.

EDIT: add sample test output:

...
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
...

edited Jan 9, 2016 at 11:00

answered Jan 9, 2016 at 10:29

Fabio Lamanna

21.7k24 gold badges95 silver badges126 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Fabio Lamanna Over a year ago

@Unnikrishnan you're welcome, please consider upvoting the answer if it worked well for your purposes.

Mike Müller Over a year ago

Always nice when one additional line solves the problem.

Ali Pardhan Over a year ago

use np.nan, my next step was df.interpolate() and this caused me a lot of confusion as my output appeared to line up with the docs

Collectives™ on Stack Overflow

Add missing date index in dataframe

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related