CODEWITHSUNDEEP
pythonpandascsv
Valip
Valip

Reputation: 4610

How to change excel wrong number format with pandas

I'm trying to import and process some csv files which are exported from excel.
Is there any way to change a wrong format of date applied to a cell that should have a number format, using python with pandas?

As shown in the attached image,
"7/25/1965" is equivalent to 23,948.00
"11/10/1933" is equivalent to 12,368.00
etc.

enter image description here

Upvotes: 0

Views: 317

Answers (1)

Pouple
Pouple

Reputation: 96

I believe the number of a given date correspond to the number of days since 1/1/1900. So you could in python count the number of days between those two dates using datetime.date object.

For example : date(2020, 7, 11) - date(2020, 7, 2) = 9

Then you replace the date value by the number you just calculated.

That said I believe it is still better to fix the problem at the source rather than in python as suggested in the comments

Upvotes: 1

Related Questions