<jupyter_start><jupyter_text>Import Libraries<jupyter_code>import requests
from requests.auth import HTTPDigestAuth
import json
import pandas as pd
from pandas.tseries.offsets import *
from pandas.tseries.holiday import get_calendar
from datetime import datetime

import re

import matplotlib
import matplotlib.pyplot as plt
%matplotlib inline
import seaborn as sns


import matplotlib.gridspec as gridspec

from nltk.stem.snowball import SnowballStemmer
from time import time


import numpy as np
from time import sleep
from io import StringIO

from geopy.distance import vincenty<jupyter_output><empty_output><jupyter_text>Base Data<jupyter_code>#
#
# Pull down the data from data.iowa.gov, format it as a dataframe, clean the city names, save.
#
#

def getfirstday(date):
    #Return the date of the Sunday of this week (corresponds to the dates used in the brand liquor data)
    firstday = 0
    dayofweek = pd.Timestamp(date).dayofweek
    if dayofweek == 6:
        firstday = date
    else:
        firstday = date + pd.Timedelta('{}D'.format(-1-dayofweek))
    return pd.to_datetime(firstday)

def getCityDailyData(offset=0):
    url='https://data.iowa.gov/resource/spsw-4jax.json'

    appToken='<app token>'
    appString='&$$app_token=' + appToken
    selectQuery='?$select=date,store_location_city,sum(sale_dollars),sum(sale_liters),county&$group=date,store_location_city,county'

    limitQuery='&$limit=50000'
    if (offset>0):
        offset='&$offset={}'.format(offset)
        query=url+selectQuery+limitQuery+appString+offset
    else:
        query=url+selectQuery+limitQuery+appString

    myResponse = requests.get(query,verify=True)
    #print (myResponse.status_code)

    jData=''
    # For successful API call, response code will be 200 (OK)
        
    try:
        if(myResponse.ok):
            jData = json.loads(myResponse.content.decode('utf-8').replace('/',' '))
            print("The response contains {0} properties".format(len(jData)))
        else:
            print(myResponse.status_code)
            print(myResponse.headers)
            
    except:
        print(myResponse.status_code)
        print(myResponse.headers)
        
        
    return jData

# Get all the data (it takes 2 calls)
ildfs=pd.io.json.json_normalize(getCityDailyData(0))
ildfs2=pd.io.json.json_normalize(getCityDailyData(50000))
ildfsj = ildfs.append(ildfs2)

#Clean up the columns
ildfsj['date'] = pd.to_datetime(ildfsj['date'])
ildfsj['sum_sale_dollars']= pd.to_numeric(ildfsj['sum_sale_dollars'])
ildfsj['sum_sale_liters']= pd.to_numeric(ildfsj['sum_sale_liters'])
ildfsj['city']=ildfsj['store_location_city'].str.upper()

def cleanCity(city):
    outcity = city.replace("'",'')
    if city == 'DEWITT':
        outcity = 'DE WITT'
    if city == 'FT. ATKINSON':
        outcity = 'FORT ATKINSON'
    if city == 'GUTTENBURG':
        outcity = 'GUTTENBERG'
    if city == 'JEWELL':
        outcity = 'JEWELL JUNCTION'
    if city == 'KELLOG':
        outcity = 'KELLOGG'
    if city == 'LECLAIRE':
        outcity = 'LE CLAIRE'
    if city == 'LEMARS':
        outcity = 'LE MARS'
    if city == 'MT PLEASANT':
        outcity = 'MOUNT PLEASANT'
    if city == 'MT VERNON':
        outcity = 'MOUNT VERNON'
    if city == 'OTTUWMA':
        outcity = 'OTTUMWA'
    if city == 'ST ANSGAR':
        outcity = 'ST. ANSGAR'
    if city == 'ST CHARLES':
        outcity = 'ST. CHARLES'
    if city == 'ST LUCAS':
        outcity = 'ST. LUCAS'
        
    return outcity

ildfsj['city'] = ildfsj['city'].apply(lambda x: cleanCity(x))

ildfsj['county']=ildfsj['county'].str.upper()

ildfsj = ildfsj.sort_values('date')

#fix the city duplicates
ildf = ildfsj[['date','city','county','sum_sale_dollars','sum_sale_liters']].groupby(['date','city','county'])[['sum_sale_liters','sum_sale_dollars']].agg(np.nansum)
ildfc = ildf.reset_index()

#Enrich the date information by adding the first-day-of-month and the year
ildfc['month'] = ildfc['date'].apply(lambda x: x.strftime('%Y-%m-01'))
ildfc['year'] = ildfc['date'].apply(lambda x: x.isocalendar()[0])
ildfc['week'] = ildfc['date'].apply(lambda x: getfirstday(x))


#Save the data
ildfc.to_csv('../data/iowa_liquor_sales_by_day.csv',index=False)<jupyter_output>The response contains 50000 properties
The response contains 29749 properties<jupyter_text>Census Data<jupyter_code>#
#
# Get the Census data from the US Census API
#
#

ildfc = pd.read_csv('../data/iowa_liquor_sales_by_day.csv')
#Get the list of cities for which we will need population information
citylist = ildfc['city'].unique()

#Read in the place names for the towns in the county
censusFIPS = pd.read_table('../data/2010_us_census_places.txt',sep='|',header=0, encoding='mbcs')
censusFIPS['city'] = censusFIPS['PLACENAME'].apply(lambda x: x.rsplit(' ',1)[0].upper().strip())
censusFIPS.head()

#Build a dataframe with the information that we will need to get city populations
statecensusFIPS = censusFIPS[censusFIPS['STATE']=='IA']
censusToGet = statecensusFIPS[['STATEFP','PLACEFP','PLACENAME','city']].loc[statecensusFIPS['city'].isin(citylist)]

def getPopulation(state,place):
    #Query the US census database to get the populations for all the cities in our list
    #Using the 2015 estimate database from api.census.gov
    
    url='http://api.census.gov/data/2015/pep/population?get=POP,GEONAME'
    appTokenUSCB='<token>'
    placeQuery='&for=place:{}'.format(place)
    stateQuery='&in=state:{}'.format(state)
    key='&key='+appTokenUSCB

    #Get population data
    query=url+placeQuery+stateQuery+key
    
    population=-1
    
    myResponse = requests.get(query,verify=True)
    # For successful API call, response code will be 200 (OK)
    try:
        if(myResponse.ok):
            poparray = np.array(json.loads(myResponse.content.decode('utf-8').replace('/',' ')))
            population=int(poparray[1,0])
        else:
            print(myResponse.status_code)
            print(myResponse.headers)
    except:
        print(myResponse.status_code)
        print(myResponse.headers)
    
    return population

#Iterate through the list and get the population for all the cities.
pops=list()
for row in censusToGet.iterrows():
    state=row[1]['STATEFP']
    place=row[1]['PLACEFP']
    d1=getPopulation(state,place)
    print(row[1]['city'] + " has population {}.".format(d1))
    pops.append(d1)
    sleep(0.3)
#Add in the population data
censusToGet['population'] = pops

#Save the data
censusToGet.to_csv('../data/iowa_town_census_2015.csv',index=False)<jupyter_output>ACKLEY has population 1560.
ADAIR has population 728.
ADEL has population 4245.
AFTON has population 829.
AKRON has population 1450.
ALBIA has population 3829.
ALDEN has population 764.
ALGONA has population 5470.
ALLISON has population 1029.
ALTA has population 1936.
ALTA VISTA has population 261.
ALTON has population 1264.
ALTOONA has population 16984.
ALVORD has population 193.
AMES has population 65060.
ANAMOSA has population 5469.
ANITA has population 956.
ANKENY has population 56764.
ANTHON has population 569.
ARLINGTON has population 410.
ARMSTRONG has population 889.
ARNOLDS PARK has population 1234.
ATLANTIC has population 6833.
AUDUBON has population 2017.
AURELIA has population 992.
AVOCA has population 1504.
BALDWIN has population 106.
BANCROFT has population 710.
BAXTER has population 1103.
BEDFORD has population 1415.
BELLE PLAINE has population 2475.
BELLEVUE has population 2176.
BELMOND has population 2314.
BETTENDORF has population 35505.
BEVINGTON has population 62.
B[...]<jupyter_text>Holiday Data<jupyter_code>#
#
# Set up a list of holidays and fill in the calendar with the proximity to the holiday.
#
#

cal = get_calendar('USFederalHolidayCalendar')
holidaylist=cal.holidays(datetime(2011, 11, 30), datetime(2016,7, 31))

currentholiday = (x for x in holidaylist)
datelist = pd.DataFrame(pd.date_range(datetime(2012,1,1), datetime(2016,5,31)),columns=['date'])
datelist['nearHoliday']=0

beforeholiday = next(currentholiday)
nextholiday = next(currentholiday)

for x in datelist.iterrows():
    date = x[1]['date']
    while( date > nextholiday):
        beforeholiday = nextholiday
        nextholiday = next(currentholiday)
        print(nextholiday)

        
    distancebefore= (date-beforeholiday).days
    distanceafter = (date-nextholiday).days
    if (distancebefore < -distanceafter):
        distancetouse = distancebefore
    else:
        distancetouse = distanceafter
    datelist['nearHoliday'][x[0]]=distancetouse
        
datelist['nearHoliday']=datelist['date'].apply(lambda x: np.array([nearDate(x,k,3) for k in holidaylist]).sum())
print(datelist.head())
datelist.to_csv('../data/holiday_proximity.csv',index=False)<jupyter_output>C:\Miniconda3\lib\site-packages\ipykernel\__main__.py:28: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame

See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy<jupyter_text>Geocode Data<jupyter_code>#
#
# Use the Google Maps API to get the approximate geocode location for every city.
#
#
#

def getLocation(state,place):
    #Query the Google Maps API for Latitude/Longitude Information
    
    appkey='<appkey>'
    url='https://maps.googleapis.com/maps/api/geocode/json'
    
    placeQuery='?address='+place.replace(' ','+')
    stateQuery='+'+state
    
    keyQuery='&key='+appkey
    
    query=url+placeQuery+stateQuery+keyQuery

    output=''
    lat=0.0
    lon=0.0
    print
    myResponse = requests.get(query,verify=True)
    # For successful API call, response code will be 200 (OK)
    try:
        if(myResponse.ok):
            output=json.loads(myResponse.content.decode('utf-8'))
            location=output['results'][0]['geometry']['location']
            lat = location['lat']
            lon = location['lng']
        else:
            print(myResponse.status_code)
            print(myResponse.headers)
    except:
        print(myResponse.status_code)
        print(myResponse.headers)
    
    return (lat,lon)

citylist = pd.DataFrame(censusToGet['city'])
citylist['state']='IA'

latlist = dict()
lonlist = dict()
nmax = len(citylist)
n=0
for place in citylist.iterrows():
    n += 1
    city = place[1]['city']
    state = place[1]['state']
    (lat, lon) = output =getLocation(state,city)
    latlist[city]=lat
    lonlist[city]=lon
    print ("{0} of {1}: {4} {2}, {3}".format(n,nmax,lat,lon,city))
    sleep(0.3)
    
citylist['lat']=citylist['city'].apply(lambda x: latlist[x])
citylist['lon']=citylist['city'].apply(lambda x: lonlist[x])

citylist.to_csv('../data/iowa_city_geocodes.csv',index=False)<jupyter_output>1 of 380: ACKLEY 42.55553400000001, -93.0529775
2 of 380: ADAIR 41.50043, -94.6433504
3 of 380: ADEL 41.6144325, -94.017453
4 of 380: AFTON 41.0284183, -94.1975786
5 of 380: AKRON 42.8288818, -96.5594773
6 of 380: ALBIA 41.0300078, -92.811307
7 of 380: ALDEN 42.52025709999999, -93.3760389
8 of 380: ALGONA 43.0699663, -94.233019
9 of 380: ALLISON 42.7535847, -92.7952244
10 of 380: ALTA 42.6749812, -95.30416559999999
11 of 380: ALTA VISTA 43.1985819, -92.4171216
12 of 380: ALTON 42.9874869, -96.01057349999999
13 of 380: ALTOONA 41.6435514, -93.47508359999999
14 of 380: ALVORD 43.3420659, -96.3022407
15 of 380: AMES 42.0307812, -93.63191309999999
16 of 380: ANAMOSA 42.1083371, -91.28515940000001
17 of 380: ANITA 41.4438761, -94.764146
18 of 380: ANKENY 41.7317884, -93.6001278
19 of 380: ANTHON 42.38832319999999, -95.8666772
20 of 380: ARLINGTON 42.7491501, -91.6712663
21 of 380: ARMSTRONG 43.3960697, -94.4783082
22 of 380: ARNOLDS PARK 43.3727408, -95.1238841
23 of 380: ATLANTIC 41.403600[...]<jupyter_text>GDELT Data<jupyter_code>#
#
# GDELT Data - read in data from Google BigQuery, parse the data to get the nearest city.
#
#
#
citylist.reset_index(inplace=True)
citylist.drop('index',inplace=True,axis=1)
cityvector = np.array([(citylist['lat'][k],citylist['lon'][k]) for k in range(len(citylist))],dtype=tuple)

#pull in the GDELT Data (from Google BigQuery) and clean it up.
gdeltdf = pd.read_csv('../data/gdelt/results-20160629-00.csv',parse_dates=[0])
for i in range(1,6):
    gdeltdf = gdeltdf.append(pd.read_csv('../data/gdelt/results-20160629-0{}.csv'.format(i),parse_dates=[0]))
gdeltdf.drop_duplicates(inplace=True)
gdeltdf.reset_index(inplace=True)
gdeltdf.drop('index',inplace=True,axis=1)
gdeltdf.sort_values('eventdate',inplace=True)
gdeltdf['geo']=gdeltdf.apply(lambda x: "{0},{1}".format(x['ActionGeo_Lat'],x['ActionGeo_Long']),axis=1)

def getKM(a,b):
    return vincenty(a,b).kilometers

def closestcity(a,b):
    return citylist['city'][np.array([getKM(a,k) for k in b]).argmin()]

gdeltdf['city']=gdeltdf['geo'].apply(lambda x: closestcity(x,cityvector))
gdeltdf['eventweek'] = gdeltdf['eventdate'].apply(lambda x: getfirstday(x))

gdeltdf.to_csv('../data/gdelt_iowa_data.csv',index=False)<jupyter_output><empty_output><jupyter_text>Facebook Data<jupyter_code>#
#
# Facebook Social Media Data - gather data from colleges about facebook/athletics
#
#
#

ildfc = pd.read_csv('../data/iowa_liquor_sales_by_day.csv')
#Get the list of cities for which we will need population information
citylist = ildfc['city'].unique()

from facepy import utils
from facepy import GraphAPI
import string

token = utils.get_application_access_token(982969551819444,'<token>')
graph = GraphAPI(token)

collegelist = pd.read_csv('../data/iowa_colleges_facebook.csv')
collegelist['City'] = collegelist['City'].apply(lambda x: x.upper())
#Check to make sure all the collge cities are in our dataframe:
print(np.array((collegelist['City'].apply(lambda x: x in citylist)).values).all())
#collegelist.head()

#Count the number of key words in each post - words related to games and big sporting events
def countKeyWords(x,wordlist):
    return np.array([1 if x.find(word)>=0 else 0 for word in wordlist]).sum()

def getFBdata(college):
    city = college['City']
    fbpage = college['FB Athletics']
    cname = college['Name']
    cenrollment = college['Enrollment'] 
    
    posts=graph.get(fbpage + '/posts',fields='created_time,message',page=True)
    
    #Pull posts down from Facebook:
    fbdict = dict()
    done=False
    for page in posts:
        for post in page['data']:
            if 'message' in post:
                text=post['message']
                #strip urls - they don't help us at all...
                text = re.sub('https?:\/*.*', '', text, flags=re.MULTILINE)
                text = re.sub('bit.ly\/.*', '', text, flags=re.MULTILINE)
                text = re.sub('\n', ' ', text, flags=re.MULTILINE)
                text = "".join([ch for ch in text if ch not in string.punctuation])
                fbdict[post['created_time']]=text.strip()
                done=pd.to_datetime(post['created_time']) < datetime(2012,1,1)
                if(done):
                    break
        print('College: ' + cname + ' Post: ' + post['created_time'])
        
        if(done):
            break
            
    #Convert them into a dataframe
    s = pd.Series(fbdict, name='message')
    s.index.name='date'
    fbdf=s.reset_index()
    fbdf['date'] = pd.DatetimeIndex(pd.to_datetime(fbdf['date'])).normalize()
    
    #Run the posts through a stemmer to prep for searching.

    stemmer=SnowballStemmer('english')
    fbdf['stemmed'] = fbdf['message'].map(lambda x: stemmer.stem(x))
    fbdf.drop('message',axis=1,inplace=True)

    wordlist = np.array(['football','basketball','game'])
    fbdf['keyCount']=fbdf['stemmed'].apply(lambda x: countKeyWords(x,wordlist))

    fbdf['week'] = fbdf['date'].apply(lambda x: getfirstday(x))
    fbdf['year'] = fbdf['date'].apply(lambda x: x.isocalendar()[0])
    
    #Add in the town and college information
    fbdf['city'] = city
    fbdf['college'] = cname
    
    return fbdf

allcollegedf= pd.DataFrame()
for index,college in collegelist.iterrows():
    allcollegedf = allcollegedf.append(getFBdata(college))
    

    

allcollegedf.to_csv('../data/college_fb_posts.csv',index=False)<jupyter_output>True
College: Iowa State University Post: 2016-06-30T14:22:58+0000
College: Iowa State University Post: 2016-06-26T13:07:46+0000
College: Iowa State University Post: 2016-06-22T14:00:00+0000
College: Iowa State University Post: 2016-06-15T22:07:34+0000
College: Iowa State University Post: 2016-06-10T14:20:00+0000
College: Iowa State University Post: 2016-06-05T14:00:00+0000
College: Iowa State University Post: 2016-05-30T11:30:00+0000
College: Iowa State University Post: 2016-05-20T21:45:00+0000
College: Iowa State University Post: 2016-05-09T23:00:00+0000
College: Iowa State University Post: 2016-04-29T17:30:00+0000
College: Iowa State University Post: 2016-04-16T13:01:00+0000
College: Iowa State University Post: 2016-04-09T22:31:35+0000
College: Iowa State University Post: 2016-03-30T17:21:19+0000
College: Iowa State University Post: 2016-03-24T16:15:00+0000
College: Iowa State University Post: 2016-03-18T17:00:01+0000
College: Iowa State University Post: 2016-03-10T15:31:09+0000
Col[...]<jupyter_text>Weather Data<jupyter_code>#
#
# NOAA Weather data by county, summary of each month
#
#
#

ildfc = pd.read_csv('../data/iowa_liquor_sales_by_day.csv')
countylist = ildfc['county'].unique()

#Read in the place names for the counties
countyFIPS = pd.read_csv('../data/2010_us_census_counties.txt',header=None,names=['STATE','STATEFP','COUNTYFP','COUNTYPLACE','H'], encoding='mbcs', dtype=str)
countyFIPS['county'] = countyFIPS['COUNTYPLACE'].apply(lambda x: x.rsplit(' ',1)[0].upper().strip())

statecensusFIPS = countyFIPS[countyFIPS['STATE']=='IA']
weatherToGet = statecensusFIPS[['STATEFP','COUNTYFP','county']].loc[statecensusFIPS['county'].isin(countylist)]
weatherToGet['FIPS'] = weatherToGet.apply(lambda x: x['STATEFP'] + x['COUNTYFP'],axis=1)
weatherToGet.drop(['STATEFP','COUNTYFP'],axis=1,inplace=True)

def getYearlyWeather (countyfips,year):
   
    startmonth = '{}-01-01'.format(year)
    endmonth = '{}-12-01'.format(year)
    
    url='http://www.ncdc.noaa.gov/cdo-web/api/v2/data?datasetid=GHCNDMS'
    appTokenNOAA='<token>'
    headers = {'token': appTokenNOAA}
    
    locationQuery='&locationid=FIPS:'+countyfips

    startDate='&startdate='+startmonth
    endDate='&enddate='+endmonth

    queryArgs='&unit=metric&limit=1000'
    
    #Get weather data
    query=url+locationQuery+startDate+endDate+queryArgs
    
    myResponse = requests.get(query,verify=True,headers=headers)
    
    # For successful API call, response code will be 200 (OK)
    wyear = pd.DataFrame(columns=['MMNT','MMXT','MNTM','TPCP','TSNW','month'])

    try:
        if(myResponse.ok):
            jData=json.loads(myResponse.content.decode('utf-8'))
            
            if jData is not {}:
                wd=pd.DataFrame(jData['results'])
                
                if jData['metadata']['resultset']['count']>jData['metadata']['resultset']['limit']:
                    #If there are more data, go get them and append them on before continuing
                    print ('Overflow warning on query ' + query )
                    
                    query += '&offset=1000'
    
                    myResponse = requests.get(query,verify=True,headers=headers)
                    jData=json.loads(myResponse.content.decode('utf-8'))
                    wd1=pd.DataFrame(jData['results'])
                    wd = wd.append(wd1)
            
            wdgroups=wd.groupby(['date'])
            for g in wdgroups:
                month=g[0]
                wdg=g[1].groupby('datatype').agg(np.mean)
                wtoreturn = wdg.transpose()
                
                if 'TSNW' not in wtoreturn:
                    wtoreturn['TSNW'] = 0
                if 'TPCP' not in wtoreturn:
                    wtoreturn['TPCP'] = 0
                if 'MMNT' not in wtoreturn:
                    wtoreturn['MMNT'] = np.NaN
                if 'MMXT' not in wtoreturn:
                    wtoreturn['MMXT'] = np.NaN
                if 'MNTM' not in wtoreturn:
                    wtoreturn['MNTM'] = np.NaN

                wtoreturn = wtoreturn[['MMNT','MMXT','MNTM','TPCP','TSNW']]/10.
                wtoreturn.reset_index(inplace=True)
                wtoreturn.drop('index',inplace=True,axis=1)
                wtoreturn['month'] = month
                wyear = wyear.append(wtoreturn) 
            wyear['FIPS']=countyfips  

        else:
            print(myResponse.status_code)
            print(myResponse.headers)
    except:
        print(myResponse.status_code)
        print(myResponse.headers)
    

    return wyear

#Get the county, year list of weather we need to get.
ildfcpf=ildfc.merge(weatherToGet,on='county')
ildftemp = ildfcpf[['year','county','FIPS','sum_sale_dollars','sum_sale_liters']].groupby(['year','county','FIPS'])[['sum_sale_liters','sum_sale_dollars']].agg(np.nansum)
ilwget = ildftemp.reset_index()
print(len(ilwget))

dfbase = pd.DataFrame(columns=['MMNT','MMXT','MNTM','TPCP','TSNW','month','FIPS'])
nmax = len(ilwget)
n=0
for row in ilwget.iterrows():
    rdata=row[1]
    n += 1
    print('{0} of {1}: {2}'.format(n,nmax,rdata['year']) + ' ' + rdata['county'] + ' FIPS: ' + rdata['FIPS'])
    dftemp=getYearlyWeather(row[1]['FIPS'],row[1]['year'])
    dftemp['county'] = rdata['county']

    sleep(0.3)
    dfbase = dfbase.append(dftemp)
    break
dfbase.to_csv('../data/iowa_weather_data_by_county.csv',index=False)<jupyter_output>495
1 of 495: 2012 ADAIR FIPS: 19001
{'fraction_non_alphanumeric': 0.1392840539284054, 'fraction_numerical': 0.05690376569037657, 'mean_word_length': 3.705972434915773, 'pattern_counts': {'":': 0, '<': 34, '<?xml version=': 0, '>': 36, 'https://': 2, 'lorem ipsum': 0, 'www.': 1, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 7, 'word_list_counts': {'cursed_substrings.json': 3, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '8', 'dir': 'jupyter-structured-clean-dedup', 'id': '640718', 'license': 'Apache-2.0', 'n_tokens_mistral': 8481, 'n_tokens_neox': 7438, 'n_words': 1599, 'path': 'src/IowaDataLibrary.ipynb', 'repo_name': 'madsenmj/data-iowa-liquor', 'size': '21510'}