Convert column in excel date format DDDDD tttt to datetime using pandas

0 votes

I have a dataframe with multiple columns and want to convert one of those columns which is a date of floats (excel date format - DDDDD.ttt) to DateTime.

At the moment the value of the columns are like this:

42411.0
42754.0

So I want to convert them to:

2016-02-11
2017-01-19
Oct 1, 2022 in Others by Kithuzzz
• 27,940 points
899 views

1 answer to this question.

0 votes

Given

# s = df['date']
s

0    42411.0
1    42754.0
Name: 0, dtype: float64

Convert from Excel to datetime using:

s_int = s.astype(int)
# Correcting Excel Leap Year bug.
days = pd.to_timedelta(np.where(s_int > 59, s_int - 1, s_int), unit='D')
secs = pd.to_timedelta(
    ((s - s_int) * 86400.0).round().astype(int), unit='s')

pd.to_datetime('1899/12/31') + days + secs

0   2016-02-11
1   2017-01-19
dtype: datetime64[ns]
answered Oct 2, 2022 by narikkadan
• 51,600 points

Related Questions In Others

0 votes
0 answers

Convert Rows to Columns with values in Excel using custom format

1 I having a Excel sheet with 1 ...READ MORE

Feb 17, 2022 in Others by Edureka
• 13,640 points
168 views
0 votes
1 answer

Change date format of cell in excel from dd.mm.yyyy to yyy/mm/dd ( excel version 2013 )

Hello :)   Excel’s Format Cells function can quickly ...READ MORE

answered Feb 9, 2022 in Others by gaurav
• 22,940 points
669 views
0 votes
1 answer

How to download SQL data in excel format using asp.net

Solution Download Open XML Download Close XML Library. import this Namespaces using System.IO; using System.Data; using ...READ MORE

answered Nov 14, 2022 in Others by narikkadan
• 51,600 points
151 views
0 votes
1 answer

How to save a new sheet in an existing excel file, using Pandas?

import pandas as pd import numpy as np path ...READ MORE

answered Dec 10, 2022 in Others by narikkadan
• 51,600 points
209 views
0 votes
1 answer

How to freeze the top row and the first column using XlsxWriter?

You can use worksheet.freeze_panes() to achieve this . There ...READ MORE

answered Oct 11, 2022 in Others by narikkadan
• 51,600 points
360 views
0 votes
1 answer

Compare 2 columns in same excel sheet in pandas

Try this: import pandas as pd import numpy as ...READ MORE

answered Dec 16, 2022 in Others by narikkadan
• 51,600 points
105 views
0 votes
1 answer

How to rename columns in pandas (Python)?

You can use the rename function in ...READ MORE

answered Apr 30, 2018 in Data Analytics by DeepCoder786
• 1,720 points

edited Jun 8, 2020 by MD 1,155 views
0 votes
1 answer

What is the Difference in Size and Count in pandas (python)?

The major difference is "size" includes NaN values, ...READ MORE

answered Apr 30, 2018 in Data Analytics by DeepCoder786
• 1,720 points

edited Jun 8, 2020 by Gitika 2,000 views
0 votes
1 answer

Convert table in a jpg image to excel using python

I believe you must execute OCR (optical ...READ MORE

answered Oct 16, 2022 in Others by narikkadan
• 51,600 points
515 views
0 votes
1 answer

Convert Feet to meters in excel with macro for entire column

You can convert Feet to Kilometers using ...READ MORE

answered Oct 27, 2022 in Others by narikkadan
• 51,600 points
49 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