data science, google sheet, pandas, python,

Read Google Spreadsheet data into Pandas Dataframe

Posted on Dec 25, 2018 · 1 min read
Share this

Many a times it happens that we have our data stored on a Google drive and to analyze that data we have to export the data as csv or xlsx and store it on a disk to convert into a dataframe.

To over come this problem of Exporting and loading the data into Pandas Dataframe, I am going to show how you can directly read the data from a Google Sheet into a Pandas Dataframe.

For this Exercise I am going to use the UCI Wine Data Set: source:https://archive.ics.uci.edu/ml/datasets/wine

import pandas as pd

Ensure that the Spreadsheet containing the data is opened in a GoogleSheet:

Copy the URL from the Address Bar:

google_sheet_url = ‘https://docs.google.com/spreadsheets/d/19nK-I3FIgLLCK9XHSKNOPYknuq4b8-qnAuAyKUegoNQ/edit#gid=280140380’

Replace “edit#gid” text in the google_sheet_url variable above with “export?format=csv&gid” so your new google_sheet_url should look like this

new_google_sheet_url = ‘https://docs.google.com/spreadsheets/d/19nK-I3FIgLLCK9XHSKNOPYknuq4b8-qnAuAyKUegoNQ/export?format=csv&gid=280140380’

**import pandas as pd**

Use Pandas read_csv function to read the WineQuality Data Spreadsheet:

**df=pd.read_csv(new_google_sheet_url)**

Voila!! the data is converted into a Dataframe without downloading the csv file

**df.head()**