使用df2gsheets将python的 pandas DF从python导入谷歌表格时的数据格式问题 [英] Problem with data format while Importing pandas DF from python into google sheets using df2gsheets

问题描述

我正在使用df2gspread将某些熊猫df导入到Google表格中.该过程运行没有任何问题，但是我想在Gsheets中处理的数字信息已作为文本导入.当我使用基本数学运算并将数据存储为文本时，它可以工作，但是当我尝试使用Sheets函数(例如求和，平均值和几乎所有其他功能)时，返回的值始终为零.另外，如果我尝试在gsheet本身中将文本手动转换为数字，则不会产生任何效果.

I'm using df2gspread to import a certain pandas df into google sheets. The process runs without any issues, but the numeric information which I'd like to manipulate within Gsheets is imported as text. When I use basic math operations with the data stored as text it works, but when I try to use Sheets functions such as sum, average and pretty much anything else, the value returned is always a zero. Also, if I try to manually convert text into numbers within gsheet itself, it doesn't have any effect.

代码如下:

import pandas as pd
import gspread as gs
from df2gspread import df2gspread as d2g

result = tera.execute_response("select * from table_drive")
df = pd.DataFrame(result)

scope = ['https://spreadsheets.google.com/feeds',
         'https://www.googleapis.com/auth/drive']
credentials = ServiceAccountCredentials.from_json_keyfile_name(
    'json_gsheets.json', scope)
gc = gs.authorize(credentials)

spreadsheet_key = 'insert_wks_key_here'
wks = 'import'
d2g.upload(df, spreadsheet_key, wks, credentials=credentials, row_names=False,start_cell = 'B3')

这可以正确插入数据，但其中的所有内容都将不可避免地以文本形式显示.

This inserts the data correctly, but everything is in there irrevocably as text.

任何人都可以帮忙吗?

提前谢谢！

推荐答案

此答案如何?

当我看到 df2gspread脚本时，，似乎 upload 的方法使用了 update_cells() .在这种情况下，在gspread处，默认值为"valueInputOption".是 RAW .df2gspread使用默认值.这样，放置数字值的顶部字符就带有单引号'.我认为您遇到问题的原因是由于这个原因.

When I saw the script of df2gspread, it seems that the method of upload uses the method of update_cells(). In this case, at gspread, the default value of "valueInputOption" is RAW. And df2gspread uses the default value. By this, the put number values have the single quote ' at the top character. I think that the reason of your issue is due to this.

在这里，为了实现您的目标，我想提出以下两种模式.

Here, in order to achieve your goal, I would like to propose the following 2 patterns.

在此模式下，df2gspread的脚本被修改.请修改 上传 如下.在目前阶段，我认为可以分为三个部分.

In this pattern, the script of df2gspread is modified. Please modify the function of upload as follows. In the current stage, I think that there are 3 parts.

wks.update_cells(cell_list)

收件人:

wks.update_cells(cell_list, value_input_option='USER_ENTERED')

模式2:

在此模式中，"values_update"方法在gspread中使用.

Pattern 2:

In this pattern, the method of "values_update" in gspread is used.

import pandas as pd
import gspread as gs
from df2gspread import df2gspread as d2g

result = tera.execute_response("select * from table_drive")
df = pd.DataFrame(result)

scope = ['https://spreadsheets.google.com/feeds', 'https://www.googleapis.com/auth/drive']
credentials = ServiceAccountCredentials.from_json_keyfile_name('json_gsheets.json', scope)

gc = gs.authorize(credentials)
spreadsheet_key = 'insert_wks_key_here'
wks = 'import'
spreadsheet = gc.open_by_key(spreadsheet_key)
values = [df.columns.values.tolist()]
values.extend(df.values.tolist())
spreadsheet.values_update(wks, params={'valueInputOption': 'USER_ENTERED'}, body={'values': values})

• 您会发现在这种情况下也使用了 USER_ENTERED .
• valueInputOption
• values_update

这篇关于使用df2gsheets将python的 pandas DF从python导入谷歌表格时的数据格式问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！