Google Spreadsheet Data Into Pandas Dataframe

I'm trying to get data from Google spreadsheet into pandas for analysis. I have several datasets in one sheet so I can't use the import as a CSV example shown here: Getting Google

Solution 1:

Here is what I would do

import pandas as pd
d=[['Date', 'letters', 'numbers', 'mixed'],\
   ['1/1/2014', 'a', '3', 'z1'],\
   ['1/2/2014', 'b', '2', 'y2'],\
   ['1/3/2014', 'c', '1', 'x3']]
df = pd.DataFrame.from_records(d[1:],columns=d[0])
df.set_index('numbers')

Here is the result

             Date letters mixed
numbers
31/1/2014a    z1
21/2/2014b    y2
11/3/2014       c    x3

Solution 2:

For anyone else who wants to connect pandas dataframes with Google Sheets, look no further! gspread-dataframe is here. It provides the missing link between gspread and pandas.

Install with pip install gspread-dataframe.

Solution 3:

Another way to transfer google spreadsheet to python pandas and vice versa would be by using df2gspread: http://df2gspread.readthedocs.io/en/latest/overview.html#usage1

It takes 10 minutes to set up and only 3 lines of code to do the trick:

from df2gspread import gspread2df as g2d
df = g2d.download(gfile="your_spreadsheet_ID", col_names=True, row_names=True)

I just set this up so if you have any questions, feel free to ask.