python how to change the time format from csv file

Question

After using pd.read_csv(), I get the data bellow. But in the first column, the time format is wrong. Could you please tell me how to correct it? Thanks. The data is like below, the first column should be 2017/4/10 9:25:00 rather than 42835.39236.

     datetime   open   high    low  close
0  42835.39236  20.72  20.72  20.72  20.72
1  42835.39583  20.72  20.72  20.67  20.67
2  42835.39653  20.66  20.67  20.62  20.63
3  42835.39722  20.63  20.65  20.59  20.59
4  42835.39792  20.59  20.59  20.52  20.52

How does the input look like? Are you passing any arguments to pd.read_csv()? — Anton vBR
– Anton vBR, Commented Nov 1, 2018 at 9:52

zipa · Accepted Answer · 2018-11-02 09:48:14Z

3

To get the correct date use:

import datetime as dt

df['datetime'] = pd.TimedeltaIndex(df['datetime'], unit='d') + dt.datetime(1899, 12, 30)

Which will produce:

                 datetime   open   high    low  close
0 2017-04-10 09:24:59.904  20.72  20.72  20.72  20.72
1 2017-04-10 09:29:59.712  20.72  20.72  20.67  20.67
2 2017-04-10 09:31:00.192  20.66  20.67  20.62  20.63
3 2017-04-10 09:31:59.808  20.63  20.65  20.59  20.59
4 2017-04-10 09:33:00.288  20.59  20.59  20.52  20.52

EDIT

To split datetime into date and time use:

df['date'] = df['datetime'].dt.date
df['time'] = df['datetime'].dt.ceil('min').dt.time
df
#                 datetime   open   high    low  close        date      time
#0 2017-04-10 09:24:59.904  20.72  20.72  20.72  20.72  2017-04-10  09:25:00
#1 2017-04-10 09:29:59.712  20.72  20.72  20.67  20.67  2017-04-10  09:30:00
#2 2017-04-10 09:31:00.192  20.66  20.67  20.62  20.63  2017-04-10  09:32:00
#3 2017-04-10 09:31:59.808  20.63  20.65  20.59  20.59  2017-04-10  09:32:00
#4 2017-04-10 09:33:00.288  20.59  20.59  20.52  20.52  2017-04-10  09:34:00

edited Nov 2, 2018 at 9:48

answered Nov 1, 2018 at 10:06

zipa

28k6 gold badges45 silver badges62 bronze badges

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

2 Comments

Hong Over a year ago

That's correct. And I also want to ask how to split the first column to two parts. Like the first row is "2017-04-10 09:24:59.904 ", I want to get two columns: 2017-04-10 and 09:25:00. Thanks. @zipa

Hong Over a year ago

I get it. The last question is since the time is too long. How can I change the time into our normal way. eg, 09:24:59.904000 change to 09:25:00. @zipa

Mayank Porwal · Accepted Answer · 2018-11-01 09:56:11Z

3

Just use pd.to_datetime:

In [741]: df
Out[741]: 
          date
0  42835.39236

In [742]: df['date'] = df['date'].apply(pd.to_datetime)

In [743]: df
Out[743]: 
                           date
0 1970-01-01 00:00:00.000042835

Pandas will convert it to datetime.

answered Nov 1, 2018 at 9:56

Mayank Porwal

34.2k9 gold badges45 silver badges65 bronze badges

Comments

Mayank Porwal · Accepted Answer · 2018-11-06 07:50:18Z

0

import datetime

try this:

tt = pd.read_csv('aa.csv', parse_dates=True)

print(tt)

It will print as follows / as per expectation, sorry I am new in this stackoverflow, but this answer works.

datetime        open       high       low       close

9:25:00 AM      20.72      20.72      20.72      20.72

9:30:00 AM      20.72      20.72      20.67      20.67

9:31:00 AM      20.66      20.67      20.62      20.63

9:32:00 AM      20.63      20.65      20.59      20.59

9:33:00 AM      20.59      20.59      20.52      20.52

edited Nov 6, 2018 at 7:50

Mayank Porwal

34.2k9 gold badges45 silver badges65 bronze badges

answered Nov 1, 2018 at 10:21

Anuprita

331 silver badge7 bronze badges

Collectives™ on Stack Overflow

python how to change the time format from csv file

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related