set_dataframe() Adding and reading Pandas DataFrame to Google sheets using pygsheets

Inserting Pandas DataFrame to google sheets by using set_dataframe() with options to copy index

Creating Google sheets

Create one folder inside your google drive, give the name as python-test.
Give Share authorization ( Share with people and group ) to Google API service account you are using.
Google API authorization and Share at Part 1 here
Creating one new File my_sheet1 ( google sheet ) inside python-test directory.

import pygsheets
path='G:\\My drive\\testing\\google-sheet\\creds1.json'
gc=pygsheets.authorize(service_account_file=path)
sh=gc.create('my_sheet1',folder_name='python-test')

set_dataframe

set_dataframe(df, start, copy_index=False, copy_head=True, 
	extend=False, fit=False, escape_formulae=False, **kwargs)

Pandas DataFrame

Options	Details
df	Pandas DataFrame
start	Staring Point tuple ( row, column). It is (1,1) in below example
copy_index	Default False, Copy Index data from DataFrame
copy_head	True ( default ), column headers of Pandas DataFrame is copied ( see example below ).
extend	False( default) If required add columns and rows ( see example )
fit	False(default), Resize to fit all data in DataFrame if required ( see example)
escape_formulae	How to handle formula ( value with = or +- sign ) ( see example below )
nan	Replace NaN with input value ( see example below )

Create Pandas DataFrame and then store the same inside our google sheets my_sheets1. Here we have used one blank google sheet and given permission to service account before running this code.

DataFrame to Google sheets

import pygsheets
path='G:\\My drive\\testing\\google-sheet\\creds1.json'
gc=pygsheets.authorize(service_account_file=path)
sh=gc.open('my_gsheets1') # open the existing file 
wk1=sh[0] # first worksheet 
#wk1.clear() # to remove all data from sheet
import pandas as pd # using pandas library 
my_dict={
	'NAME':['Ravi','Raju','Alex'],
	'ID':[1,2,3],'MATH':[30,40,50],
	'ENGLISH':[20,30,60]
	} # Create dictionary 
df = pd.DataFrame(data=my_dict) # Create DataFrame using dictionary
wk1.set_dataframe(df,(1,1)) # Place DataFrame from row 1 column 1 
print(wk1.get_row(4,include_tailing_empty=False)) # for testing

Output ( for last line )

['Alex', '3', '50', '60']

copy_index

Let us add the DataFrame with index.

wk1.set_dataframe(df,(1,1),copy_index=True) #

copy_head

We can remove the headers by using copy_head option with False

wk1.set_dataframe(df,(1,1),copy_index=True, copy_head=False)

extend

If DataFrame has more rows and columns than the sheet , then it will generate error saying exceeds grid limits. Max rows: 3, max columns: 5.

However we can use extend option with True ( default is False ) to expand the sheet to match the DataFrame rows and columns. We are reducing the default rows and columns of the sheet by using resize()

wk1.resize(2,2)
wk1.set_dataframe(df,(1,1),copy_index=True, extend=True)

fit

Our sheet has 10 rows and 10 columns and our DataFrame has 5 rows and 4 columns. We can shrink our sheet to exactly match the size of our DataFrame and don't want to leave any blank cell outside the DataFrame by using fit option.

wk1.resize(10,10) # resize rows and columns of sheet
wk1.set_dataframe(df,(1,1),copy_index=True, fit=True)

escape_formulae

We will change the input DataFrame to include a formula like this '=40+5'. Default value for escape_Formulae is False.

my_dict={
	'NAME':['Ravi','Raju','Alex'],
	'ID':[1,2,3],'MATH':[30, '=40+5',50],
	'ENGLISH':[20,30,60]
	} # Create dictionary 
df = pd.DataFrame(data=my_dict) # Create DataFrame using dictionary
wk1.set_dataframe(df,(1,1), escape_formulae=False)

set_dataframe() with escape_formulae=False

Let us change the escape_formulae option to True

wk1.set_dataframe(df,(1,1), escape_formulae=True)

set_dataframe() with escape_formulae=True

nan

We can replace the NaN with values. Here we are using Numpy to add some NaN values to our DataFrame. Check the np.NaN data

import pandas as pd # using pandas library 
import numpy as np
my_dict={'NAME':['Ravi','Raju','Alex',None,'King',None],
         'ID':[1,2,np.NaN,4,5,6],
         'MATH':[80,40,70,70,82,30],
         'ENGLISH':[81,70,40,50,np.NaN,30]}
df = pd.DataFrame(data=my_dict) # Create DataFrame using dictionary
wk1.set_dataframe(df,(1,1))

We will add nan option with * value.

wk1.set_dataframe(df,(1,1), nan='*')

Pygsheets and google API authorization get_as_df()

Pandas DataFrame Python- Tutorials

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author Watch on YouTube

Connect: