时间戳

首页 > 解决方案 > 无法从 Dataframe 更新 Google 表格 - json 问题，单个单元格问题

python - 无法从 Dataframe 更新 Google 表格 - json 问题，单个单元格问题

问题描述

我有一个数据框，我想将非标题记录附加到谷歌表中。

import gspread
import csv

latest = pd.read_csv("/Users/sam/Downloads/transactions.csv")

updatesheet = sheet.worksheet("testpy")

updatesheet.append_row([update], table_range='A1')

这给出了一个错误Object of type DataFrame is not JSON serializable

我尝试了各种堆栈线程的其他选项，大多数会导致非 JSON 类型错误，但是当我将其转换df.to_json()时，会将其全部放入单个单元格中。

编辑：更新 table_range='A1:A' 没有修复。

标签： pythonjsonpandasdataframegoogle-sheets

解决方案

我可以在这里想到两种方法，我在下面都包括了。

您遇到的 JSON 类型错误可以通过将 df 行转换为列表来解决，如method2.

A1只需省略table_range.

import gspread
import pandas as pd


# set variables
docid = 'yourdocidhere'
worksheetid = 'transactions'
credfile = 'path/to/file/credentials.json'
csvfile = 'path/to/file/transactions.csv'


# load GSheet
gc = gspread.service_account(filename=credfile)
sheet = gc.open_by_key(docid)
worksheet = sheet.worksheet(worksheetid)


# load CSV file into df
df_csv = pd.read_csv(csvfile)


def method1(worksheet: gspread.Worksheet, df: pd.DataFrame):
    """This is the 'cheaty' way. Just load the existing worksheet
    into df, append other df and overwrite existing worksheet.
    Note that this doesn't check if the values in df are already
    in the worksheet...

    Args:
        worksheet (gspread.Worksheet): the worksheet with which to work
        df (pd.DataFrame): the df to append
    """
    # just load existing GSheet into a df
    df_cheat = pd.DataFrame(worksheet.get_all_records())
    # perform append. You might want to play with how you merge/append
    # This method has the advantage of easy sorting.
    df_cheat = df_cheat.append(df).sort_values(by='Date')
    # overwrite worksheet
    # you could easily create a new worksheet with this method as you have
    # the df_cheat to work with
    worksheet.update([df_cheat.columns.values.tolist()] + df_cheat.values.tolist())


def method2(worksheet: gspread.Worksheet, df: pd.DataFrame):
    """This method goes through the df row by row and adds
    it to the worksheet by using casting.
    https://www.w3schools.com/python/python_casting.asp

    Args:
        worksheet (gspread.Worksheet): the worksheet with which to work
        df (pd.DataFrame): the df to append
    """
    # go over the values in the df by row
    for row in df.values:
        # cast as list
        row = list(row)
        # if you leave out the table_range it will append at the end
        worksheet.append_row(row)


method1(worksheet, df_csv)
method2(worksheet, df_csv)

路径/到/文件/transactions.csv

Date,Time,Amount,Type,Description,Category
4/1/2019,21:49:02,-$11,Withdrawal,Intuit,*QuickBooks Work - Organization

之前 之后前 后

推荐阅读