Append Multiple Dataframes and Comments Above the Dataframe into One Worksheet using Gspread and pyt-CodePudding

I am looking for a solution where I can append multiple dataframes and some comments above the dataframe into one worksheet in google sheet using gspread.

Following is the example of what I need, can anyone help me with this.

data = [['tom', 10], ['nick', 15], ['juli', 14]]
df_1 = pd.DataFrame(data, columns=['Name', 'Age'])

data1 = {'Name':['Renault', 'Maruti', 'Honda'], 'Ratings':[9, 8, 5]}  
df_2 = pd.DataFrame(data1)  

data2 = [{'A': 10, 'B': 20}, {'x':10, 'y': 20}]  
df_3 = pd.DataFrame(data2) 

comments = ['Dataframe 1: ', 'Dataframe 2: ', 'Dataframe 3: ']

Edit: It is some how identical to this code, but it is in excel, i need the same using gspread.

  dfs = [df_1, df_2, df_3] 
  comments_1 = ['Dataframe 1: ', 'Dataframe 2: ', 'Dataframe 3: '] 
  with pd.ExcelWriter(filename, engine='openpyxl', if_sheet_exists='overlay', mode='a') as writer:
    for dataframe, comment in zip(dfs, comments_1): 
      pd.Series(comment).to_excel(writer,sheet_name=sheet_name, 
        startrow=1, startcol=0, index=False, header=False) 
      dataframe.to_excel(writer,sheet_name=sheet_name, startrow=1, startcol=0)

CodePudding user response：

In your situation, how about the following sample script?

Sample script:

import gspread
import pandas as pd

# --- This is from your question.
data = [["tom", 10], ["nick", 15], ["juli", 14]]
df_1 = pd.DataFrame(data, columns=["Name", "Age"])

data1 = {"Name": ["Renault", "Maruti", "Honda"], "Ratings": [9, 8, 5]}
df_2 = pd.DataFrame(data1)

data2 = [{"A": 10, "B": 20}, {"x": 10, "y": 20}]
df_3 = pd.DataFrame(data2)

comments = ["Dataframe 1: ", "Dataframe 2: ", "Dataframe 3: "]
# ---

# Create an array for putting to Spreadsheet.
dfs = [df_1, df_2, df_3]
values = []
for i, df in enumerate(dfs):
    df = df.fillna("")
    values.append([comments[i]])
    values.extend([df.columns.values.tolist()]   df.values.tolist())
    values.append([""])

# Put the array to Spreadsheet using gspread.
client = ### # Please use your gspread client.
spreadsheetId = "###" # Please set your spreadsheet ID.
sheetName = "Sheet1"
spreadsheet = client.open_by_key(spreadsheetId)
sheet = spreadsheet.worksheet(sheetName)
sheet.update("A1", values, value_input_option="USER_ENTERED")

When this script is run, the values are put to "Sheet1" of Spreadsheet of spreadsheetId.

Note:

From your reply, this sample script supposes that you have already been able to get and put values to Spreadsheet using Sheets API with gspread. Please be careful about this.

Reference:

update