Google Sheets date to Pandas datetime unexpected offset

user_623534 0
Viewed 210

Problem

I'm trying to accurately represent a date from Google Sheets in a DataFrame. I know that the "base" dates in Google Sheets are integers added to the date since 1/1/1900. Testing this is clear: I have a Sheet with the date 5/2/2019. Using the Python API, I download this Sheet with the parameter valueRenderOption='UNFORMATTED_VALUE' to ensure I'm getting raw values, and do a simple conversion to a DataFrame. The value shows up as 43587, and if I put that back into a Sheet and set the format to date, it appears as 5/2/2019. Sanity check complete.

The problem arises when I try to convert that date in the DataFrame to an actual datetime: it shows up as offset by two days, and I'm not sure why.

Attempts

In a DataFrame df, with datetime column timestamp, I do the following:

df['timestamp'] = pd.to_datetime(df['timestamp'], unit='d', origin='1900-01-01')

and I get a date of 2019-05-04, which is two days later than I would expect. I searched for this on SO and found a similar issue, but the accepted answer actually contains the exact same problem (albeit no mention of it): a two day offset.

This can be "solved" by setting the origin two days back, to 1899-12-30, though that feels almost like a cover, and not necessarily fixing the underlying issue (and could perhaps leads to further date inconsistencies down the road as more time has passed?).

Here's code for a toy DataFrame so that you don't have to type it out, if you want to experiment:

import pandas as pd

df = pd.DataFrame([{'timestamp': 43587}])

Question

I imagine this is on the Pandas side of things, but I'm not sure. Some internal conversion that happens differently than how they do it at Google? Does anyone have an idea of what's at play here, and if setting the origin date two days earlier is actually a solution?

pandas google-sheets dataframe python-3.x datetime
1 Answers

I have been banging my head against this as well, and think that I finally figured it out. While for the Date() function, Sheets uses 1900-1-1 as the base, for the date format and for the TO_DATE() function, the origin date is 1899-12-30.

You can see this in Sheets by either

  • entering 0 in a cell, and then formatting to a date → 12/30/1899
  • entering =TO_DATE(0), which will result in 12/30/1899

One origin story for this odd choice is here in a very old MSDN forum. I have no idea of its veracity.

At any rate, that explains the two-day discrepancy and then the solution becomes

df['timestamp'] = pd.to_datetime(df['timestamp'], unit='d', origin='1899-12-30')

which worked for me.

user_197026360
Related