Show notebooks in Drive
This notebook is open with private outputs. Outputs will not be saved. You can disable this in .
02_reading_and_saving_to_google_sheets.ipynb_
Rename
Rename notebook
Star
Star/unstar notebook in Google Drive
File
Edit
View
Insert
Runtime
Tools
Help
Last edited on December 11
Comment
Open comments pane
Share
Share notebook
Open settings
Code
Insert code cell below
Ctrl+M B
Text
Add text cell
Connect
Click to connect
Additional connection options
Editing
Toggle header visibility
Code
Text
Setup
Code
Text
[ ]
!
pip install gspread-dataframe
!pip install gspread-dataframe
Enter to Rename, Shift+Enter to Preview
Code
Text
[ ]
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
Enter to Rename, Shift+Enter to Preview
Authorise Google Sheets
[ ]
# authorise google sheets
from
google.colab
import
auth
auth.authenticate_user()
import
gspread
from
oauth2client.client
import
GoogleCredentials
gc = gspread.authorize(GoogleCredentials.get_appli
cation_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())
Enter to Rename, Shift+Enter to Preview
[ ]
# 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')
Enter to Rename, Shift+Enter to Preview
Load from Google Sheets
[ ]
stadium_worksheet = sht.worksheet(
"stadiums"
)
stadium_worksheet = sht.worksheet("stadiums")
Enter to Rename, Shift+Enter to Preview
[ ]
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()
Enter to Rename, Shift+Enter to Preview
[ ]
stadium_data.shape
stadium_data.shape
Enter to Rename, Shift+Enter to Preview
(20, 4)
[ ]
stadium_data
stadium_data
Enter to Rename, Shift+Enter to Preview
Save to Google Sheets
[ ]
# read in all tables on html page into a list
datas = pd.read_html(
"
https://en.wikipedia.org/wiki/2003%E2%80%9304_FA_P
remier_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")
Enter to Rename, Shift+Enter to Preview
[ ]
len
(datas)
len(datas)
Enter to Rename, Shift+Enter to Preview
18
[ ]
# find the gold
datas[
7
].head()
# find the gold datas[7].head()
Enter to Rename, Shift+Enter to Preview
[ ]
# identify the worksheet to work with
s1_worksheet = sht.worksheet(
"Sheet1"
)
# identify the worksheet to work with s1_worksheet = sht.worksheet("Sheet1")
Enter to Rename, Shift+Enter to Preview
[ ]
#save data into the worksheet
set_with_dataframe(s1_worksheet, datas[
7
])
#save data into the worksheet set_with_dataframe(s1_worksheet, datas[7])
Enter to Rename, Shift+Enter to Preview
[ ]
Enter to Rename, Shift+Enter to Preview
Video tutorial of working with Google Sheets in Python
[ ]
from
IPython.display
import
YouTubeVideo
YouTubeVideo(
'cN7W2EPM-dw'
, width=
800
, height=
600
)
from IPython.display import YouTubeVideo YouTubeVideo('cN7W2EPM-dw', width=800, height=600)
Enter to Rename, Shift+Enter to Preview
[ ]
Enter to Rename, Shift+Enter to Preview
Loading...