|
| 1 | +""" |
| 2 | +See http://pbpython.com/pandas-google-forms-part1.html for more details |
| 3 | +and explanation of how to create the SECRETS_FILE |
| 4 | +
|
| 5 | +Purpose of this example is to pull google sheet data into |
| 6 | +a pandas DataFrame. |
| 7 | +
|
| 8 | +""" |
| 9 | +from __future__ import print_function |
| 10 | +import gspread |
| 11 | +from oauth2client.client import SignedJwtAssertionCredentials |
| 12 | +import pandas as pd |
| 13 | +import json |
| 14 | + |
| 15 | +SCOPE = ["https://spreadsheets.google.com/feeds"] |
| 16 | +SECRETS_FILE = "Pbpython-key.json" |
| 17 | +SPREADSHEET = "PBPython User Survey (Responses)" |
| 18 | +# Based on docs here - http://gspread.readthedocs.org/en/latest/oauth2.html |
| 19 | +# Load in the secret JSON key (must be a service account) |
| 20 | +json_key = json.load(open(SECRETS_FILE)) |
| 21 | +# Authenticate using the signed key |
| 22 | +credentials = SignedJwtAssertionCredentials(json_key['client_email'], |
| 23 | + json_key['private_key'], SCOPE) |
| 24 | + |
| 25 | +gc = gspread.authorize(credentials) |
| 26 | +print("The following sheets are available") |
| 27 | +for sheet in gc.openall(): |
| 28 | + print("{} - {}".format(sheet.title, sheet.id)) |
| 29 | +# Open up the workbook based on the spreadsheet name |
| 30 | +workbook = gc.open(SPREADSHEET) |
| 31 | +# Get the first sheet |
| 32 | +sheet = workbook.sheet1 |
| 33 | +# Extract all data into a dataframe |
| 34 | +data = pd.DataFrame(sheet.get_all_records()) |
| 35 | +# Do some minor cleanups on the data |
| 36 | +# Rename the columns to make it easier to manipulate |
| 37 | +# The data comes in through a dictionary so we can not assume order stays the |
| 38 | +# same so must name each column |
| 39 | +column_names = {'Timestamp': 'timestamp', |
| 40 | + 'What version of python would you like to see used for the examples on the site?': 'version', |
| 41 | + 'How useful is the content on practical business python?': 'useful', |
| 42 | + 'What suggestions do you have for future content?': 'suggestions', |
| 43 | + 'How frequently do you use the following tools? [Python]': 'freq-py', |
| 44 | + 'How frequently do you use the following tools? [SQL]': 'freq-sql', |
| 45 | + 'How frequently do you use the following tools? [R]': 'freq-r', |
| 46 | + 'How frequently do you use the following tools? [Javascript]': 'freq-js', |
| 47 | + 'How frequently do you use the following tools? [VBA]': 'freq-vba', |
| 48 | + 'How frequently do you use the following tools? [Ruby]': 'freq-ruby', |
| 49 | + 'Which OS do you use most frequently?': 'os', |
| 50 | + 'Which python distribution do you primarily use?': 'distro', |
| 51 | + 'How would you like to be notified about new articles on this site?': 'notify' |
| 52 | + } |
| 53 | +data.rename(columns=column_names, inplace=True) |
| 54 | +data.timestamp = pd.to_datetime(data.timestamp) |
| 55 | +print(data.head()) |
0 commit comments