Pandas Multiindex not working with read_csv and datetime objects

Question

I have a problem loading a dataframe from csv when I have a multiindex with more than one date in it.

I am running the following code:

import pandas as pd
import datetime
date1 = datetime.date.today()
date2 = datetime.date.today().replace(month=1)
date_cols=['date1', 'date2']
index = pd.MultiIndex.from_product([[date1],[date2]])

#create dataframe with a single row
df= pd.DataFrame([{'date1':date1, 'date2':date2, 'a':1, 'b':2}])
df.set_index(date_cols, inplace=True)
#print the single row -> correct
print df.loc[index]

# write to csv and load it again
df.to_csv('df.csv')
dfr = pd.read_csv('df.csv', parse_dates=date_cols, dayfirst=True)
dfr.set_index(date_cols, inplace=True)
# print the single row -> incorrect, shows nan,
print dfr.loc[index]

Whilst I expect to get the same output, i.e. the single row in the dataframe, the second print statement prints out nan, because the index is not in the dataframe. When running df.index, I see that the multiindex object contains the two dates, but now also holds time information, where the time is 00:00:00

Is this a bug?

Jeff · Accepted Answer · 2014-07-31 12:57:07Z

1

What you are doing is subtlely different.

In [31]: df.index.levels[0]
Out[31]: Index([2014-07-31], dtype='object')

In [32]: dfr.index.levels[0]
Out[32]: 
<class 'pandas.tseries.index.DatetimeIndex'>
[2014-07-31]
Length: 1, Freq: None, Timezone: None

The initial creation (using MultiIndex.from_product is using datetimes. In a multi-index creation I suppose this could cause an automatic DatetimeIndex creation, rather that a plain Index of datetimes.

When reading it back in a proper DatetimeIndex is created. I'll open an issue to think about this. See here

answered Jul 31, 2014 at 12:57

Jeff

130k21 gold badges223 silver badges189 bronze badges

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

2 Comments

user3895587 Over a year ago

When I initialize date1 and date2 to be pandas.datetime objects instead of datetime.datetime objects it works fine. Thanks for the very fast response btw

Jeff Over a year ago

yep, they are then Timestamps (its equivalent but not identical to datetimes)

Collectives™ on Stack Overflow

Pandas Multiindex not working with read_csv and datetime objects

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related