problem-with-data-format-while-importing-pandas-df-from-python-into-google-sheet

Question

Used the second pattern suggested here: Problem with data format while Importing pandas DF from python into google sheets using df2gsheets

but I got the following error

TypeError: Object of type Timestamp is not JSON serializable

The error is related to the fact that one column in my data set is a Timestamp.

Is there a way to upload a pandas data frame to gsheets while keeping the user-defined data formats also in the case of Timestamps?

I used the following code lines, sorry for over-commenting:

#library to read Google Sheets
import gspread
#Library to write Google Sheets
from df2gspread import df2gspread as d2g
#Library to manage authorizations on Google Sheets
from oauth2client.service_account import ServiceAccountCredentials

#***set credential from JSON files stored in the same directory as current jupyter notebook
scope = ['https://spreadsheets.google.com/feeds']
credentials = ServiceAccountCredentials.from_json_keyfile_name('XXX', scope)
spreadsheet_key = 'YYY'
gc = gspread.authorize(credentials)
    
#***instructions to write with the proper format
# reference here https://stackoverflow.com/questions/63273092/problem-with-data-format-while-importing-pandas-df-from-python-into-google-sheet
wks = 'import'
spreadsheet = gc.open_by_key(spreadsheet_key)
values = [d_df.columns.values.tolist()]
values.extend(d_df.values.tolist())
spreadsheet.values_update(wks, params={'valueInputOption': 'USER_ENTERED'}, body={'values': values})`

First, I apologize that my answer was not useful for your situation. Although I'm not sure whether I could correctly understand your question, I proposed an answer. Please confirm it. If I misunderstood your question and that was not useful, I apologize again. — Tanaike
– Tanaike, Commented Jun 25, 2023 at 8:53
Hi Tanaike, first of all thanks for swing and actionable answer! Unfortunately I still get the same error: TypeError: Object of type date is not JSON serializable — Andrea Pagliari
– Andrea Pagliari, Commented Jun 25, 2023 at 20:49
and the error is referred to: 41 spreadsheet.values_update(wks, params={'valueInputOption': 'USER_ENTERED'}, body={'values': values}) — Andrea Pagliari
– Andrea Pagliari, Commented Jun 25, 2023 at 20:50
Your suggestion to some extent worked fine. Within d_df[c] there is the right content, which is the column [created_at] with the format you forced as following 2334 2023-06-23 08:12:59 Name: created_at, Length: 2335, dtype: object — Andrea Pagliari
– Andrea Pagliari, Commented Jun 25, 2023 at 20:54
Thank you for replying. About TypeError: Object of type date is not JSON Serializable, in this case, can you provide the sample value of your dataframe for correctly replicating your current situation? When I tested my modified script using my sample dataframe, no error occurs. So, I thought that in this case, it is required to use your sample value for correctly replicating your current issue. I think that this is due to my poor skill. I have to apologize for this. — Tanaike
– Tanaike, Commented Jun 25, 2023 at 22:58

Tanaike · Accepted Answer · 2023-06-25 08:53:46Z

0

About Is there a way to upload a pandas data frame to gsheets while keeping the user defined data formats also in case of Timestamps?, how about the following modification?

From:

spreadsheet = gc.open_by_key(spreadsheet_key)
values = [d_df.columns.values.tolist()]
values.extend(d_df.values.tolist())

To:

spreadsheet = gc.open_by_key(spreadsheet_key)

# --- I added the below script.
dateCols = d_df.select_dtypes(include=['datetime']).columns.values
for c in dateCols:
    d_df[c] = d_df[c].dt.strftime('%Y-%m-%d %H:%M:%S')
# ---

values = [d_df.columns.values.tolist()]
values.extend(d_df.values.tolist())

When this script is run, the columns of the date object are converted to string type. And, when those values are put into the cells of Spreadsheet with {"valueInputOption": "USER_ENTERED"}, those values are put as the date object.

answered Jun 25, 2023 at 8:53

Tanaike

203k12 gold badges126 silver badges222 bronze badges

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

Collectives™ on Stack Overflow

problem-with-data-format-while-importing-pandas-df-from-python-into-google-sheet

1 Answer 1

From:

To:

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

From:

To:

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related