From Pandas to Google Sheets

For communication purposes

This is a great trick that I learned working at Mutt Data. On the development side, on a day-to-day basis, working with Pandas is a very common thing, both for exploratory data analysis and data manipulation in general.

But when there are non-IT members in the team of a project, especially business people, using Pandas is not the best medium to share information, since many people are not familiar with it and are instead used to see information on spreadsheets. At Mutt we addressed this issue by converting DataFrames into Google Sheets . It is quite a convenient solution as both work with tabular data and Google documents are easy to share.

The package that you should install for this is called gspread and it is a Python API for Google Sheets.

pip install gspread

Authentication

You will need a service account—which is a file with credentials—to enable the program to write into Google Sheets. These are the steps.

1. Go to the Google Cloud Platform (GCP) console and create a new project or select an existing one (I created my-project). If you've never used GCP before, you will have to go through a few extra steps to enable APIs.
2. In the navigation menu in the top left corner, go to Products > APIs & Services, look for Google Drive API (you can also use the search bar) and also for Google Sheets API and enable both of them.
3. In the Dashboard go to Credentials > Create Credentials > Service Account bar look for service accounts, and once there do the following:

• Choose a name for the service account (I chose google-sheets) and create it.
• Then select the service account that you just created and go to Keys > Add Key > Create New Key > JSON > Create . Download the account (in my case, my-project-80a030363d28.json) and save that file in an appropriate folder to work.

The service account will be used for all the spreadsheets that we need within the same GCP project.

Access to the spreadsheet

This step is very important, it must be done every time we use a new spreadsheet.

Go to the spreadsheet and share it with the email of the service account (the one that appears in the account detail) in the same way that we would share it with another person through their email account.

In my case, I have to share the sheets with google-sheets@my-project.iam.gserviceaccount.com.

Pandas

This is the function I use to write a DataFrame in Google Sheets. Some data type conversions are necessary since Pandas and Google Sheets don't support the same types.

import gspread

GSHEETS_CREDENTIALS = 'my-project-80a030363d28.json'


def save_to_gsheets(df, sheet_name, worksheet_name='Sheet1'):
    client = gspread.service_account(GSHEETS_CREDENTIALS)
    sheet = client.open(sheet_name)

    worksheet = sheet.worksheet(worksheet_name)

    # we convert the type of datetime columns to string
    for column in df.columns[df.dtypes == 'datetime64[ns]']:
        df[column] = df[column].astype(str)

    # we replace NaN values for empty strings
    worksheet.update([df.columns.values.tolist()] + df.fillna('').values.tolist())

    print(f'DataFrame written in the sheet {sheet_name} / {worksheet_name}.')

Let's go with an example:

import numpy as np
import pandas as pd

df = pd.DataFrame(np.random.randint(0, 100, size=(7, 4)), columns=list('ABCD'))

df

Previously I had to create the Pandas Example sheet and give it access to the service account.

save_to_gsheets(df, 'Pandas Example', worksheet_name='Sheet1')

DataFrame saved in Pandas Example / Sheet1

This is the result