How to read gsheet files

Ulf Hamster 1 min.
python Google Docs gsheet

Load Packages

%%capture
!pip install --upgrade -q gspread
from google.colab import auth
import gspread
from oauth2client.client import GoogleCredentials
import numpy as np
import pandas as pd

Authenticate

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

Read gsheet

worksheet = gc.open('demo2').sheet1
rows = worksheet.get_all_values()
df = pd.DataFrame(data=rows[1:], columns=rows[0])
rows
[['ID', 'Column1', 'Some Name', 'Cool Col'],
 ['1', '2019-10-15 13:00', 'Lorem ipsum dolor sit amet', '0.5912229648'],
 ['2', '2019-10-15 15:30', 'consectetur', '9.185521593'],
 ['3', '2019-10-15 18:00', 'adipiscing elit', '9.992377256'],
 ['4', '2019-10-15 20:30', 'Mauris quis dui', '0.04129697971'],
 ['5', '2019-10-15 23:00', 'eu justo', '5.437268887'],
 ['6', '10/16/2019', 'feugiat', '897.87%'],
 ['7', '2019-10-16 4:00', '', '165.31%'],
 ['8', '10/16/2019', 'egestas', '449.19%'],
 ['9', '2019-10-16 9:00', 'Cras auctor', '325.85%'],
 ['10', '2019-10-16 11:30', 'felis eget fringilla vehicula', '829.85%']]
df
.dataframe tbody tr th {
    vertical-align: top;
}

.dataframe thead th {
    text-align: right;
}

Some Preprocessing

df.dtypes
ID           object
Column1      object
Some Name    object
Cool Col     object
dtype: object
df['ID'] = df['ID'].astype(np.uint8)
df['Column1'] = pd.to_datetime(df['Column1'])
df['Some Name'] = df['Some Name'].astype(str)
df['Cool Col'] = df['Cool Col'].apply(lambda e: np.float64(e[:-1])/100.0 if '%' in e else np.float64(e))
df.dtypes
ID                    uint8
Column1      datetime64[ns]
Some Name            object
Cool Col            float64
dtype: object
df
.dataframe tbody tr th {
    vertical-align: top;
}

.dataframe thead th {
    text-align: right;
}