Datetime index replaced after creating csv file from dataframe

Question

data = pd.read_csv('data.csv')
print(data)

Output:

       code  trade_date   open   high    low  close       volume
0       2GO  2012-06-04  1.750  1.750  1.750  1.750       5000.0
1       2GO  2012-06-05  1.750  1.980  1.750  1.900       8000.0
2       2GO  2012-06-07  1.960  1.960  1.800  1.800       8000.0
3       2GO  2012-06-11  1.900  1.980  1.900  1.980      50000.0
4       2GO  2012-06-13  1.990  1.990  1.900  1.900      19000.0

I want to set the trade_date as index . . .

data.set_index('trade_date', inplace=True)
print(data)

Output:

           code   open   high    low  close       volume
trade_date
2012-06-04  2GO  1.750  1.750  1.750  1.750       5000.0
2012-06-05  2GO  1.750  1.980  1.750  1.900       8000.0
2012-06-07  2GO  1.960  1.960  1.800  1.800       8000.0
2012-06-11  2GO  1.900  1.980  1.900  1.980      50000.0
2012-06-13  2GO  1.990  1.990  1.900  1.900      19000.0

. . . then write it to a csv file.

data.to_csv('data_reidx.csv')

However when I read the csv file again, trade_date is back to being a column and was replaced by traditional indexing.

data = pd.read_csv('data_reidx.csv')

Output:

        trade_date code   open   high    low  close       volume
0       2012-06-04  2GO  1.750  1.750  1.750  1.750       5000.0
1       2012-06-05  2GO  1.750  1.980  1.750  1.900       8000.0
2       2012-06-07  2GO  1.960  1.960  1.800  1.800       8000.0
3       2012-06-11  2GO  1.900  1.980  1.900  1.980      50000.0
4       2012-06-13  2GO  1.990  1.990  1.900  1.900      19000.0

How do I preserve the datetime index when writing dataframe to csv?

Can you try data = pd.read_csv('data_reidx.csv', index_col='trade_date'). let me know if it worked. — ItayBenHaim
– ItayBenHaim, Commented May 5, 2019 at 12:58
@itaybenhaim this worked. In data.to_csv('data_reidx.csv', index_label=False), the date index is preserved even when reading the csv normally, but the 'trade_date' column name is gone. Both works just fine. The column name is not important in my case. — Yoyong
– Yoyong, Commented May 5, 2019 at 13:40

Vivek Kalyanarangan · Accepted Answer · 2019-05-05 13:06:31Z

2

Try to get rid of the index_label -

data.to_csv('data_reidx.csv', index_label=False )

Next time you read, read it normally -

data = pd.read_csv('data_reidx.csv')

Output

           code  open  high   low  close   volume
2012-06-04  2GO  1.75  1.75  1.75   1.75   5000.0
2012-06-05  2GO  1.75  1.98  1.75   1.90   8000.0
2012-06-07  2GO  1.96  1.96  1.80   1.80   8000.0
2012-06-11  2GO  1.90  1.98  1.90   1.98  50000.0
2012-06-13  2GO  1.99  1.99  1.90   1.90  19000.0

answered May 5, 2019 at 13:06

Vivek Kalyanarangan

9,1011 gold badge27 silver badges42 bronze badges

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

Comments

Steve · Accepted Answer · 2019-05-05 13:01:25Z

1

Try telling pandas which column is the index when you load it:

data = pd.read_csv('data_reidx.csv', index_col='trade_date')

Here is a nice guide that explains it better than i can if you need more help: https://chrisalbon.com/python/data_wrangling/pandas_dataframe_importing_csv/

answered May 5, 2019 at 13:01

Steve

305 bronze badges

Collectives™ on Stack Overflow

Datetime index replaced after creating csv file from dataframe

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related