Reading from and saving to Google Sheets¶

Setup¶

In [ ]:

!pip install gspread-dataframe

!pip install gspread-dataframe

In [ ]:

import pandas as pd
from gspread_dataframe import get_as_dataframe, set_with_dataframe

import pandas as pd from gspread_dataframe import get_as_dataframe, set_with_dataframe

Authorise Google Sheets¶

In [ ]:

# authorise google sheets
from google.colab import auth
auth.authenticate_user()

import gspread
from oauth2client.client import GoogleCredentials

gc = gspread.authorize(GoogleCredentials.get_application_default())

# authorise google sheets from google.colab import auth auth.authenticate_user() import gspread from oauth2client.client import GoogleCredentials gc = gspread.authorize(GoogleCredentials.get_application_default())

In [ ]:

# identify the specific google sheet to work with
sht = gc.open_by_key('1t762sD-lHhjPmIednCjxbp_T8waMlsaWW-6DFjgaU4c')

# identify the specific google sheet to work with sht = gc.open_by_key('1t762sD-lHhjPmIednCjxbp_T8waMlsaWW-6DFjgaU4c')

Load from Google Sheets¶

In [ ]:

stadium_worksheet = sht.worksheet("stadiums")

stadium_worksheet = sht.worksheet("stadiums")

In [ ]:

stadium_data = get_as_dataframe(stadium_worksheet, usecols=[0,1,2,3] )
stadium_data = stadium_data.dropna()

stadium_data = get_as_dataframe(stadium_worksheet, usecols=[0,1,2,3] ) stadium_data = stadium_data.dropna()

In [ ]:

stadium_data.shape

stadium_data.shape

Out[ ]:

(20, 4)

In [ ]:

stadium_data

stadium_data

Out[ ]:

	Team	Location	Stadium	Capacity
0	Arsenal	London (Highbury)	Arsenal Stadium	38419.0
1	Aston Villa	Birmingham (Aston)	Villa Park	42553.0
2	Birmingham City	Birmingham (Bordesley)	St Andrew's	30079.0
3	Blackburn Rovers	Blackburn	Ewood Park	31367.0
4	Bolton Wanderers	Bolton	Reebok Stadium	28723.0
5	Charlton Athletic	London (Charlton)	The Valley	27111.0
6	Chelsea	London (Fulham)	Stamford Bridge	42360.0
7	Everton	Liverpool (Walton)	Goodison Park	40569.0
8	Fulham	London (Fulham)	Loftus Road[a]	18493.0
9	Leeds United	Leeds	Elland Road	40242.0
10	Leicester City	Leicester	King Power Stadium	32312.0
11	Liverpool	Liverpool (Anfield)	Anfield	45276.0
12	Manchester City	Manchester	City of Manchester Stadium[b]	48000.0
13	Manchester United	Old Trafford	Old Trafford	68217.0
14	Middlesbrough	Middlesbrough	Riverside Stadium	35049.0
15	Newcastle United	Newcastle upon Tyne	St James' Park	52387.0
16	Portsmouth	Portsmouth	Fratton Park	20220.0
17	Southampton	Southampton	St Mary's Stadium	32505.0
18	Tottenham Hotspur	London (Tottenham)	White Hart Lane	36240.0
19	Wolverhampton Wanderers	Wolverhampton	Molineux Stadium	29303.0

Save to Google Sheets¶

In [ ]:

# read in all tables on html page into a list
datas = pd.read_html("https://en.wikipedia.org/wiki/2003%E2%80%9304_FA_Premier_League")

# read in all tables on html page into a list datas = pd.read_html("https://en.wikipedia.org/wiki/2003%E2%80%9304_FA_Premier_League")

In [ ]:

len(datas)

len(datas)

Out[ ]:

18

In [ ]:

# find the gold
datas[7].head()

# find the gold datas[7].head()

Out[ ]:

	Pos	Team	Pld	W	D	L	GF	GA	GD	Pts	Qualification or relegation
0	1	Arsenal (C)	38	26	12	0	73	26	+47	90	Qualification for the Champions League group s...
1	2	Chelsea	38	24	7	7	67	30	+37	79	Qualification for the Champions League group s...
2	3	Manchester United	38	23	6	9	64	35	+29	75	Qualification for the Champions League third q...
3	4	Liverpool	38	16	12	10	55	37	+18	60	Qualification for the Champions League third q...
4	5	Newcastle United	38	13	17	8	52	40	+12	56	Qualification for the UEFA Cup first round

In [ ]:

# identify the worksheet to work with
s1_worksheet = sht.worksheet("Sheet1")

# identify the worksheet to work with s1_worksheet = sht.worksheet("Sheet1")

In [ ]:

#save data into the worksheet
set_with_dataframe(s1_worksheet, datas[7])

#save data into the worksheet set_with_dataframe(s1_worksheet, datas[7])

In [ ]:

Video tutorial of working with Google Sheets in Python¶

In [ ]:

from IPython.display import YouTubeVideo

YouTubeVideo('cN7W2EPM-dw', width=800, height=600)

from IPython.display import YouTubeVideo YouTubeVideo('cN7W2EPM-dw', width=800, height=600)

Out[ ]:

In [ ]: