Google spreadsheet data into pandas dataframe

Question

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 Spreadsheet CSV into A Pandas Dataframe

This is what my data looks like in the spreadsheet:

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

I have used gspread to import and can get the data to look like this with each row a observation:

[['Date', 'letters', 'numbers', 'mixed'], ['1/1/2014', 'a', '3', 'z1'], ['1/2/2014', 'b', '2', 'y2'], ['1/3/2014', 'c', '1', 'x3']]

My question is how do I get it into the pandas dataframe with number indices? I don't want date to be the index. I need a general solution, my dataset is 1000 rows by 50 columns.

Answer 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
3        1/1/2014       a    z1
2        1/2/2014       b    y2
1        1/3/2014       c    x3

Answer 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.

Answer 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.