1

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})`
Amith A G
  • 378
  • 13
Andrea Pagliari
  • 13
  • 2
  • 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 Jun 25 '23 at 08: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 Jun 25 '23 at 20:49
  • and the error is referred to: 41 spreadsheet.values_update(wks, params={'valueInputOption': 'USER_ENTERED'}, body={'values': values}) – Andrea Pagliari Jun 25 '23 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 Jun 25 '23 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 Jun 25 '23 at 22:58
  • @Tanaike thanks for keeping on paying attention to this issue. – Andrea Pagliari Jun 29 '23 at 20:16
  • @Tanaike 0 2023-03-24 14:07:02 1 2023-03-24 14:07:25 2 2023-03-24 14:07:48 3 2023-03-24 14:08:12 4 2023-03-24 14:08:35 ... 2330 2023-06-23 06:06:39 2331 2023-06-23 06:38:15 2332 2023-06-23 07:09:51 2333 2023-06-23 07:41:27 2334 2023-06-23 08:12:59 Name: created_at, Length: 2335, dtype: datetime64[ns] this is a sample value of the data column which originates the error. – Andrea Pagliari Jun 29 '23 at 20:17
  • Thank you for replying. I apologize for my poor English skill. Unfortunately, from your reply, I cannot use your sample value. Can you provide your sample value for correctly replicating your current issue? Because when I tested my proposed modified script using my sample value, no error occurs. I think that this is due to my poor skill. I deeply apologize for my poor skill, again. If you can cooperate to resolve your issue, I'm glad. Can you cooperate to do it? – Tanaike Jun 30 '23 at 00:25
  • @Tanaike thanks it worked! There was just another time column that I had to convert to string. Thanks for wing and effective support – Andrea Pagliari Jun 30 '23 at 11:42
  • Thank you for replying and testing it again. I'm glad your issue was resolved. Thank you, too. – Tanaike Jun 30 '23 at 12:08

1 Answers1

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.
Tanaike
  • 181,128
  • 11
  • 97
  • 165