googlesheet.py

import gspread
from oauth2client.service_account import ServiceAccountCredentials
import pandas as pd
 
# use creds to create a client to interact with the Google Drive API
scope = ['https://spreadsheets.google.com/feeds']
creds = ServiceAccountCredentials.from_json_keyfile_name('client_secret.json', scope)
client = gspread.authorize(creds)
 
# Find a workbook by name and open the first sheet
# Make sure you use the right name here.
sheet = client.open("Copy of Legislators 2017").sheet1
 
#extract columns, python starts counting at 0 but google sheets starts at 1
twitter = list(sheet.col_values(14))
twitter.pop(0)
states = list(sheet.col_values(6))
#remove the first item on the list which is the column names
states.pop(0)
twitter_states = list(zip(twitter,states))
df = pd.DataFrame(twitter_states)
#add column names to the data frame
df.columns = ['twitter','states']
#groupby data frame function to find out the counts of twitter handles of officials by state
count_twitter = df.groupby(['states'],sort=True).size()
print(count_twitter )