Reading Excel file Date time Incorrectly

0 votes

I have an excel document with a column for time and another for dates. To read it, I'm employing the code below:

df = pd.read_excel(r'df.xlsx', parse_dates=[['date', 'time']])

While the date is the same, this works properly; but, if the dates vary, it will be misread. For instance, the excel file looks like this:

enter image description here

If I read it using this code the results will be like this:

2021-04-03 00:00:00 23:52:11,A
2021-04-03 00:00:00 23:56:05,A
2021-04-03 00:00:00 23:59:27,A
2021-04-04 00:00:00 1900-01-01 00:03:33,B
2021-04-04 00:00:00 1900-01-01 00:04:33,B
2021-04-04 00:00:00 1900-01-01 00:06:43,B
2021-04-04 00:00:00 1900-01-01 00:10:17,B

How can I fix this so the data frame has separate columns with the correct date and time like below?

2021-04-03  23:52:11,A
2021-04-03  23:56:05,A
2021-04-03  23:59:27,A
2021-04-04  00:03:33,B
2021-04-04  00:04:33,B
2021-04-04  00:06:43,B
2021-04-04  00:10:17,B

Excel file: https://www.apispreadsheets.com/table/lEooNma9w3X2XfaL/

Mar 20, 2023 in Others by Kithuzzz
• 38,010 points
367 views

1 answer to this question.

0 votes

Use dateutil.parser.

Example usage:

import dateutil.parser as parser

parser.parse("2021-04-04 00:00:00 1900-01-01 00:03:33,B")

The output will be a datetime object. I hope this helps you.

answered Mar 20, 2023 by narikkadan
• 63,420 points

Related Questions In Others

0 votes
1 answer

Want Tick mark and X-mark using current date and current time using Excel formula

I likewise use a checkmark to indicate ...READ MORE

answered Nov 10, 2022 in Others by narikkadan
• 63,420 points
575 views
0 votes
1 answer

Determine if calculation between 2 date time values is < 72 Hours in excel

Actually, Excel is quite accommodating in this ...READ MORE

answered Nov 12, 2022 in Others by narikkadan
• 63,420 points
593 views
0 votes
1 answer

Reading Excel file using node.js

Several distinct libraries perform Excel file parsing ...READ MORE

answered Dec 10, 2022 in Others by narikkadan
• 63,420 points
1,590 views
0 votes
1 answer

Excel Formula which places date/time in cell when data is entered in another cell in the same row

Here's how to accomplish things in another ...READ MORE

answered Dec 11, 2022 in Others by narikkadan
• 63,420 points
819 views
0 votes
1 answer

Excel formula in Pandas

 Use this: df['col2'] = df['col1'].shift().lt(df['col1']).astype(int) I hope this helps ...READ MORE

answered Jan 6, 2023 in Others by narikkadan
• 63,420 points
1,478 views
0 votes
2 answers
+1 vote
2 answers

how can i count the items in a list?

Syntax :            list. count(value) Code: colors = ['red', 'green', ...READ MORE

answered Jul 7, 2019 in Python by Neha
• 330 points

edited Jul 8, 2019 by Kalgi 4,058 views
0 votes
1 answer
0 votes
1 answer

Excel plot against a date time x series [closed]

Try using an X-Y Scatter graph with ...READ MORE

answered Oct 22, 2022 in Others by narikkadan
• 63,420 points
353 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP