Can't upload df with non-string column object types

[jasonqng]

Set up dataframe like so:

import pandas
from datetime import datetime
df = pd.DataFrame([[datetime.today().date(), 
                   datetime.today(),
                   "2018-05-07",
                   20180507]], 
                  columns=["date_object","timestamp","string","int"])

Check object dtypes:

print(df.dtypes)

date_object            object
timestamp      datetime64[ns]
string                 object
int                     int64
dtype: object

Uploading the date_object, timestamp, and int64 columns all yield the below error. Probably should convert everything to string before upload I guess, but maybe there is a better solution.

d2g.upload(df[['date_object']],sh,"temp")


---------------------------------------------------------------------------
TypeError                                 Traceback (most recent call last)
<ipython-input-34-1982f2c8ec17> in <module>()
----> 1 d2g.upload(df,xxxxx,"temp")

/usr/local/lib/python3.6/dist-packages/df2gspread/df2gspread.py in upload(df, gfile, wks_name, chunk_size, col_names, row_names, clean, credentials, start_cell, df_size, new_sheet_dimensions)
    146                 cell_list[i + j * len(df.columns.values)].value = df[col][idx]
    147 
--> 148     wks.update_cells(cell_list)
    149     return wks
    150 

/usr/local/lib/python3.6/dist-packages/gspread/models.py in update_cells(self, cell_list, value_input_option)
    607             },
    608             body={
--> 609                 'values': values_rect
    610             }
    611         )

/usr/local/lib/python3.6/dist-packages/gspread/models.py in values_update(self, range, params, body)
    113     def values_update(self, range, params=None, body=None):
    114         url = SPREADSHEET_VALUES_URL % (self.id, quote(range, safe=''))
--> 115         r = self.client.request('put', url, params=params, json=body)
    116         return r.json()
    117 

/usr/local/lib/python3.6/dist-packages/gspread/client.py in request(self, method, endpoint, params, data, json, files, headers)
     71             data=data,
     72             files=files,
---> 73             headers=headers
     74         )
     75 

/usr/local/lib/python3.6/dist-packages/requests/sessions.py in put(self, url, data, **kwargs)
    564         """
    565 
--> 566         return self.request('PUT', url, data=data, **kwargs)
    567 
    568     def patch(self, url, data=None, **kwargs):

/usr/local/lib/python3.6/dist-packages/requests/sessions.py in request(self, method, url, params, data, headers, cookies, files, auth, timeout, allow_redirects, proxies, hooks, stream, verify, cert, json)
    492             hooks=hooks,
    493         )
--> 494         prep = self.prepare_request(req)
    495 
    496         proxies = proxies or {}

/usr/local/lib/python3.6/dist-packages/requests/sessions.py in prepare_request(self, request)
    435             auth=merge_setting(auth, self.auth),
    436             cookies=merged_cookies,
--> 437             hooks=merge_hooks(request.hooks, self.hooks),
    438         )
    439         return p

/usr/local/lib/python3.6/dist-packages/requests/models.py in prepare(self, method, url, headers, files, data, params, auth, cookies, hooks, json)
    306         self.prepare_headers(headers)
    307         self.prepare_cookies(cookies)
--> 308         self.prepare_body(data, files, json)
    309         self.prepare_auth(auth, url)
    310 

/usr/local/lib/python3.6/dist-packages/requests/models.py in prepare_body(self, data, files, json)
    456             # provides this natively, but Python 3 gives a Unicode string.
    457             content_type = 'application/json'
--> 458             body = complexjson.dumps(json)
    459             if not isinstance(body, bytes):
    460                 body = body.encode('utf-8')

/usr/lib/python3.6/json/__init__.py in dumps(obj, skipkeys, ensure_ascii, check_circular, allow_nan, cls, indent, separators, default, sort_keys, **kw)
    229         cls is None and indent is None and separators is None and
    230         default is None and not sort_keys and not kw):
--> 231         return _default_encoder.encode(obj)
    232     if cls is None:
    233         cls = JSONEncoder

/usr/lib/python3.6/json/encoder.py in encode(self, o)
    197         # exceptions aren't as detailed.  The list call should be roughly
    198         # equivalent to the PySequence_Fast that ''.join() would do.
--> 199         chunks = self.iterencode(o, _one_shot=True)
    200         if not isinstance(chunks, (list, tuple)):
    201             chunks = list(chunks)

/usr/lib/python3.6/json/encoder.py in iterencode(self, o, _one_shot)
    255                 self.key_separator, self.item_separator, self.sort_keys,
    256                 self.skipkeys, _one_shot)
--> 257         return _iterencode(o, 0)
    258 
    259 def _make_iterencode(markers, _default, _encoder, _indent, _floatstr,

/usr/lib/python3.6/json/encoder.py in default(self, o)
    178         """
    179         raise TypeError("Object of type '%s' is not JSON serializable" %
--> 180                         o.__class__.__name__)
    181 
    182     def encode(self, o):

TypeError: Object of type 'Timestamp' is not JSON serializable

Uploading the string (d2g.upload(df[['string']],sh,"temp")) works fine.

[maybelinot]

Hi @jasonqng it's a known issue in gspread after moving to the new version of Google API. I did a workaround by converting all the values to string by calling df.applymap(str). As gspread will have its own solution - this change could be reverted.

Thanks for raising this up!

[madreyg]

Hello, this code df.applymap(str) breaks with google sheet, because all values have type string. And formulas in sheet don't work. =(

[monipip3]

Hi
When I use d2g.upload() it works fine but now every value has this ' attached to it eg: '65

When I try to do find and replace with ' it can't find it in the values?

[P1etrodev]

@monipip3 did you find any solution to this? I'm having the same problem with dates, and it's breaking the whole script.