python - 如何将 CSV 数据(从 GoogleSheets - Google Python API 获取)转换为表格格式(数据框)？

Question

我编写了一段代码，通过 IFTTT、DialogFlow 和 Hiroku 定义的路径从 Google-Sheets 文档获取数据，这是通过以下代码片段实现的:

# Finding a workbook by name and opening the first sheet
sheet = client.open("IFTTT_Webhooks").sheet1

# Extract and print all of the values
list_of_answers = sheet.get_all_records()

#put the values from the list_of_answers into a csv and store locallyy.
with open('user_answers.csv', 'w') as myfile:
    wr = csv.writer(myfile, quoting=csv.QUOTE_ALL)
    wr.writerow(list_of_answers)

如果我打开“user_answers.csv”，csv 看起来如下:

[{'Date/Time': 'February 19, 2020 at 09:49PM', 'Intent': 'poll_completed', 'Statement 1': 'yes', 'Statement 2': 'Sometimes', 'Statement 3': 'sometimes', 'Statement 4': 'yes'}, {'Date/Time': 'February 19, 2020 at 09:50PM', 'Intent': 'poll_completed', 'Statement 1': 'yes', 'Statement 2': 'never', 'Statement 3': 'never', 'Statement 4': 'no'}, {'Date/Time': 'February 19, 2020 at 10:48PM', 'Intent': 'poll_completed', 'Statement 1': 'yes', 'Statement 2': 'often', 'Statement 3': 'sometimes', 'Statement 4': 'yes'}, {'Date/Time': 'February 20, 2020 at 12:14AM', 'Intent': 'poll_completed', 'Statement 1': 'now', 'Statement 2': 'often', 'Statement 3': 'sometimes', 'Statement 4': 'yes'}]

如何以表格格式获取此数据，其中包含列及其各自的值:

Date/time,                    Statement 1, Statement 2, Statement 3, Statement 4
February 19, 2020 at 09:49PM.  yes         Sometimes    Sometimes.   no

如果 csv 格式保持不变就可以了。但我希望能够更有效地处理另一个文件中的数据。所以我想创建一个考虑这种新的(请求的)表格格式的数据框。如有任何帮助，我们将不胜感激。

Answer 1

我没有从谷歌中提取，但也许将代码更改为类似这样的代码，以便它直接提取到数据帧中？

import gspread
from oauth2client.service_account import ServiceAccountCredentials
import pandas as pd

scope = ['https://spreadsheets.google.com/feeds',
         'https://www.googleapis.com/auth/drive']

credentials = ServiceAccountCredentials.from_json_keyfile_name(
         'ForMedium-8f02213e02aa.json', scope) # Your json file here

gc = gspread.authorize(credentials)

wks = gc.open("NYC subway data").sheet1

data = wks.get_all_values()
headers = data.pop(0)

df = pd.DataFrame(data, columns=headers)