<file_sep>"""This function takes users interests as inputs and outputs a list of
ranked vacation destinations with top interests for each destination.
To rank destinations, the app uses data that has been collected from Twitter
and stored in MySQL.  user_interests is a assumed to be a string
consisting of interests separated by commas.  The output is a 2D list of
strings.  The left column has destinations and the right column has
the top three interests for that destination."""

# Packages
import re
import time
import pandas as pd
import pymysql as mdb
from sqlalchemy import create_engine

# Files in this directory
from mysql_login_info import sql_username, sql_password
import preprocessing

def get_rankings(user_interests):
    if user_interests == '':
        return []
    destinations=[]
    df_destinations=pd.read_csv('vacation_destinations.txt', header=None, names=['Destination'])
    destinations = sorted(df_destinations['Destination'].values.tolist())

    # Connect to the local mySQL server and open the vacation database.
    engine = create_engine('mysql+mysqldb://'+sql_username+':'+sql_password+'@127.0.0.1:3306/vacation', echo=False)

    # Parse user input, stem it, and make regexes.
    user_input = user_interests
    def stem(s):
            return preprocessing.preprocess_pipeline(s, return_as_str=True, do_remove_stopwords=True, do_clean_html=False)
    interests_regex={}
    max_interests = 10
    for interest in user_input.split(',')[:max_interests]:
        if interest == '':
            continue
        # Remove any characters that that aren't alphanumeric.
        interest = re.sub(r'[^\w]+', '', interest)
        if False:
            # Insert MySQL regex for spaces (this isn't working yet).  Once
            # this is working, edit the re.sub line above
            # so that it doesn't remove spaces.
            interests_regex[interest.strip()]= re.sub(' ',r'[[:space:]]',stem(interest))
        interests_regex[interest.strip()]= stem(interest)
    interests=interests_regex.keys()
    print len(interests), 'interests'
    print interests_regex

    # Put the destinations in a dataframe to use in the next step.
    df_destinations = pd.DataFrame({'Destination':destinations}).set_index('Destination')
    df_destinations.head()
       
    # Count the number of tweets for each destination for each interest.
    destinations_interests = df_destinations.copy()
    db = mdb.connect(user=sql_username, host="localhost", passwd=sql_password, db="vacation", charset='utf8')
    interests_processed = 0
    for interest in interests:
        sql_query = ("SELECT Destination, COUNT(*) AS " + interest +
                     " FROM timeline_tweets_stemmed_unique WHERE Tweet REGEXP '[[:<:]]" +
                     interests_regex[interest] + "[[:>:]]' GROUP BY Destination")
        with db:
            cur = db.cursor()
            cur.execute(sql_query)
            query_results = cur.fetchall()
        query_results = map(list, zip(*[list(i) for i in query_results]))
        if len(query_results) == 0:
            interests_processed += 1
            print interests_processed, 'interests processed'
            continue
        df_timeline = pd.DataFrame({'Destination': query_results[0], interest: query_results[1]}).set_index('Destination')
        destinations_interests = destinations_interests.join(df_timeline, how='outer').fillna(0)
        interests_processed += 1
        print interests_processed, 'interests processed'
        
    # To normalize rows, change normalize_rows to True.
    normalize_rows = False
    if normalize_rows:
        # Count the number of timeline tweets for each destination.
        db = mdb.connect(user=sql_username, host="localhost", passwd=sql_password, db="vacation", charset='utf8')
        sql_query = ("SELECT Destination, COUNT(*)" +
                     " FROM timeline_tweets_stemmed_unique GROUP BY Destination")
        with db:
            cur = db.cursor()
            cur.execute(sql_query)
            query_results = cur.fetchall()
        query_results = map(list, zip(*[list(i) for i in query_results]))
        df_tweets_per_destination = pd.DataFrame({'Destination': query_results[0],
                'Tweets': query_results[1]}).set_index('Destination')
        destinations_interests_rows_normalized = destinations_interests.join(df_tweets_per_destination, how='outer').fillna(0)

        for interest in interests:
            destinations_interests_rows_normalized[interest] = (
                    destinations_interests_rows_normalized[interest].astype(float) /
                    destinations_interests_rows_normalized['Tweets'].astype(float))
        destinations_interests_rows_normalized = destinations_interests_rows_normalized.drop('Tweets', axis=1)
    else:
        destinations_interests_rows_normalized = destinations_interests

    # Normalize each interest column.
    def normalize(x):
            if x.std() > 0.01:
                return (x-x.mean())/x.std()
            else:
                return x-x.mean()
    destinations_interests_scores = destinations_interests_rows_normalized.copy()
    destinations_interests_scores = destinations_interests_rows_normalized.apply(normalize)

    # Rank the destinations and then
    # store the top three interests for each destination in a dataframe.
    destination_col = destinations_interests_scores.sum(axis=1).sort(ascending=False, inplace=False).index.values.tolist()
    interest_col = []
    for destination in destination_col:
        interest_list=[]
        # Rank the interests.
        for interest in destinations_interests_scores.loc[destination].sort(ascending=False, inplace=False).index.values.tolist():
            # Only include interests with associated tweets.
            if destinations_interests.loc[[destination]][interest][0]>0:
                interest_list.append(interest)
        # Only use the top 3 interests.
        interest_col.append(', '.join(interest_list[:3]))  
    df_interest_destination = pd.DataFrame({'Interest': interest_col, 'Destination': destination_col})
    # Remove New York (The algorithm doens't work very well for
    # New York because it is both a city and a state).
    remove_new_york = True
    if remove_new_york:
        df_interest_destination = df_interest_destination[df_interest_destination.Destination != 'New York']
    # Reorder the columns.
    df_interest_destination = df_interest_destination[['Destination', 'Interest']]
    # Drop destinations without any interests.
    df_interest_destination=df_interest_destination[df_interest_destination['Interest'] != '']
    df_interest_destination.head()
    
    # Put the top interest for each destination in a list format.
    interest_col = df_interest_destination['Interest'].values.tolist()
    destination_col = df_interest_destination['Destination'].values.tolist()
    interest_destination = []
    length = len(interest_col)
    for i in range(length):
        interest_destination.append([destination_col[i],interest_col[i]])

    # Return the results as a 2D list.
    return interest_destination

<file_sep>"""This will stem all of the tweets in the timeline_tweets table and
put them in the timeline_tweets_stemmed table."""

import pandas as pd
from sqlalchemy import create_engine
from sqlalchemy_utils import database_exists, create_database
import time
import preprocessing
from mysql_login_info import sql_username, sql_password
import datetime

start_time = time.clock()

# connect to my local mySQL server and open the vacation database
engine = create_engine('mysql+mysqldb://'+sql_username+':'+sql_password+'@127.0.0.1:3306/vacation', echo=False)


chunk_size = 10000
i = 0
while True:
    # Pull the timeline data out of the mySQL server
    # and put it in a pandas dataframe.
    sql_query = 'SELECT * FROM timeline_tweets ORDER BY `Tweet ID` LIMIT ' + str(chunk_size) + ' OFFSET ' + str(i*chunk_size)
    df_timeline = pd.read_sql_query(sql_query,engine)

    if len(df_timeline.index)<1:
        break
    # Stem the Tweet column
    def stem(s):
        return preprocessing.preprocess_pipeline(s, return_as_str=True, do_remove_stopwords=True, do_clean_html=False)
    df_timeline['Tweet']=df_timeline['Tweet'].str.decode('utf-8').str.encode('ascii', errors='ignore').apply(stem)

    # Save the data on my mySQL server
    df_timeline.to_sql('timeline_tweets_stemmed', engine, if_exists='append', index=False)
    i += 1
    print datetime.datetime.today().__str__()
    print 'chunk size:', chunk_size
    print 'chunks processed:', i

end_time = time.clock()

print 'runtime:', (end_time - start_time) / 60, 'minutes'
<file_sep>"""Run this file every 15 minutes until all timeline data is pulled
from Twitter.  The process will take about about 12 hours."""

from twython import Twython
import pandas as pd
import re
import matplotlib as plt
from sqlalchemy import create_engine
from sqlalchemy_utils import database_exists, create_database
import datetime

from mysql_login_info import sql_username, sql_password
from twitter_keys import APP_KEY, APP_SECRET, OAUTH_TOKEN, OAUTH_TOKEN_SECRET

# Print log file entry
print ''
print datetime.datetime.today().__str__()

# Twitter authentication
twitter = Twython(APP_KEY, APP_SECRET,
                  OAUTH_TOKEN, OAUTH_TOKEN_SECRET)

# connect to my local mySQL server and open the vacation database
engine = create_engine('mysql+mysqldb://'+sql_username+':'+sql_password+'@127.0.0.1:3306/vacation', echo=False)

# Pull the vacation tweet data out of the mySQL server
# and put in in the original dataframe.
sql_query = 'SELECT * FROM vacation_tweets_w_name'
df_both = pd.read_sql_query(sql_query,engine).sort('Tweet ID')
print len(df_both.index), 'total user timelines to process'
df_both.head(2)

# Get the chron progress from the dataframe
sql_query = 'SELECT * FROM chron_progress'
df_progress = pd.read_sql_query(sql_query,engine)
progress = df_progress['Progress'][0]

# Filter out the vacation tweet data that has already been processed
df_both = df_both[df_both['Tweet ID']>progress].sort('Tweet ID')
print len(df_both.index), 'user timelines left to process'
df_both.head(2)


# Get the first 85 users' timelines
# count=200 is the maximum allowed amount
# 180 of such requests are allowed per 15 minutes
timelines = []
length = len(df_both['Screen Name'].values)
users_skipped = 0
user_timelines_to_query = 85
queries_per_timeline = 2
for i in range(user_timelines_to_query):
    if i >= length:
        break
    screen_name = df_both.iloc[i]['Screen Name']
    try:
        next_timeline = twitter.get_user_timeline(screen_name=screen_name, count=200)
    except Exception as e:
        print 'exception at query', i
        print e
        #break
        print 'skipping user', screen_name
        users_skipped += 1
        continue
    current_user_timeline=[next_timeline]
    for j in range(1, queries_per_timeline):
        if len(current_user_timeline[-1])<200:
            break
        max_id=min([status['id'] for status in current_user_timeline[-1]])-1
        try:
            next_timeline = twitter.get_user_timeline(screen_name=screen_name, count=200, max_id=max_id)
        except:
            break
        current_user_timeline.append(next_timeline)  
    timelines.append(current_user_timeline)
    progress = df_both.iloc[i]['Tweet ID']

    
print i+1-users_skipped, 'user timelines processed'
print users_skipped, 'user timelines skipped'
print len(df_both.index) - i - 1, 'user timelines left to process'

# Put the timeline tweets in a dataframe
time_timeline = []
time_texts = []
time_ids = []
time_times = []
time_destinations = []
time_names = []
time_screen_names = []
for i in range(len(timelines)):
    screen_name = df_both.iloc[i]['Screen Name']
    destination = df_both.iloc[i]['Destination']
    for user_timeline in timelines[i]:
        for tweet in user_timeline:
            time_timeline.append(screen_name)
            time_texts.append(tweet['text'])
            time_ids.append(tweet['id'])
            time_times.append(tweet['created_at'])
            time_destinations.append(destination)
            time_names.append(tweet['user']['name'])
            time_screen_names.append(tweet['user']['screen_name'])
    df_timeline = pd.DataFrame({'Timeline': time_timeline,
                                'Tweet': time_texts,
                                'Tweet ID': time_ids,
                                'Time': time_times,
                                'Destination': time_destinations,
                                'Full Name': time_names,
                                'Screen Name': time_screen_names
                               })


# Change the character encoding to utf-8.
# MySQL will complain otherwise.
for col in df_timeline.columns.values:
    if df_timeline[col].dtype=='object':
        df_timeline[col]=df_timeline[col].str.encode('utf-8', errors='ignore')

# Save the timelines to my mySQL server
df_timeline.to_sql('timeline_tweets', engine, if_exists='append', index=False)

# put the progress in a dataframe
df_progress = pd.DataFrame({'Progress':[progress]})

# copy the progress dataframe to the MySQL database
df_progress.to_sql('chron_progress', engine, if_exists='replace', index=False)

# Print the number of tweets processed
print len(df_timeline.index), 'tweets processed'

# Print the time remaining, assuming
# 85 timelines collected 3 times every hour
print '{0:.1f} hours remain'.format(len(df_both.index) * 1.0 / 85 / 3)
<file_sep>"""This dummy version of get_rankings imports pre-computed rankings
from the MySQL server and is used for testing purposes."""

# packages
import re
import time
import pandas as pd
import pymysql as mdb
from sqlalchemy import create_engine

# files in this directory
from mysql_login_info import sql_username, sql_password
import preprocessing

def get_rankings_dummy():
    # Connect to my local mySQL server and open the vacation database
    engine = create_engine('mysql+mysqldb://'+sql_username+':'+sql_password+'@127.0.0.1:3306/vacation', echo=False)
    
    # Pull the pre-computed rankings out of MySQL
    sql_query = 'SELECT * FROM interest_destination_dummy'
    df_interest_destination = pd.read_sql_query(sql_query,engine)

    # Put the pre-computed rankings in table format
    interest_col = df_interest_destination['Interest'].values.tolist()
    destination_col = df_interest_destination['Destination'].values.tolist()
    interest_destination = []
    length = len(interest_col)
    for i in range(length):
        interest_destination.append([destination_col[i],interest_col[i]])
    return interest_destination
<file_sep>"""This is the 'main' part of my app.  Flask uses it generate the
HTML on my website."""

# packages
from flask import render_template, request
from flask import Flask
import time

# files in this directory
import get_rankings
import get_rankings_dummy

app = Flask(__name__)

@app.route('/')
@app.route('/index')    
@app.route('/input')
def cities_input():
    return render_template("input.html")

@app.route('/output')
def cities_output():
    interests = request.args.get('ID') # pull interests from the input page
    start = time.time()
    # If the user inputs the string 'cached_results', then the app will return 
    # cached results for boats, surfing, kayaking, and swimming.  This is for 
    # testing and demonstration purposes only.
    if interests == 'cached_results':
        interests = 'boats, surfing, kayaking, swimming'
        rankings = get_rankings_dummy.get_rankings_dummy()
    else:
        rankings = get_rankings.get_rankings(interests)
    end = time.time()
    print 'runtime', end-start, 'seconds'
    length = min(20,len(rankings))
    return render_template("output.html", rankings = rankings, interests=interests, length=length, runtime=end-start)
  
@app.route('/slides')
def slides():
    return render_template("slides.html")

if __name__ == "__main__":
    app.run(host='0.0.0.0', port=5000)
<file_sep>"""This initializes the chron_progress table in my MySQL database
to zero so that I can then use timeline_chron.py to begin pulling Twitter
users' Timelines from Twitter every 15 minutes.  Each time that
timeline_chron.py runs, it will update the chron_progress table in MySQL."""

from twython import Twython
import pandas as pd
import re
import matplotlib as plt
from sqlalchemy import create_engine
from sqlalchemy_utils import database_exists, create_database
import datetime

from mysql_login_info import sql_username, sql_password
from twitter_keys import APP_KEY, APP_SECRET, OAUTH_TOKEN, OAUTH_TOKEN_SECRET

# connect to my local mySQL server and open the vacation database
engine = create_engine('mysql+mysqldb://'+sql_username+':'+sql_password+'@127.0.0.1:3306/vacation', echo=False)

# initialize progress to zero
df_progress = pd.DataFrame({'Progress':[0]})

# copy the progress to the database
df_progress.to_sql('chron_progress', engine, if_exists='replace', index=False)
<file_sep>Vacation Finder
by <NAME>

This web app uses Twitter data to recommend vacation destinations based on the user's interests and can be found at http://www.vacationfinder.xyz.  It comes preloaded with 40 destinations and the Twitter timelines of Twitter users who have tweeted about going to these destinations on vacation.  When the user of the app inputs interests, the app searches the stored Twitter timelines for those interests.  The current data set contains about one million tweets.

Scripts in the data_collection folder were used to gather the Twitter data and store it in a MySQL database.

Files in the webapp folder are used to run the app.

The app uses Python, MySQL, Flask, and Bootstrap.
<file_sep>"""This will create a timeline_tweets_stemmed_unique table in MySQL that contains
all unique tweets from the timeline_tweets_stemmed table."""

import pandas as pd
import re
import matplotlib as plt
from sqlalchemy import create_engine
from sqlalchemy_utils import database_exists, create_database

import numpy as np
import time

from mysql_login_info import sql_username, sql_password
import preprocessing

# connect to my local mySQL server and open the vacation database
engine = create_engine('mysql+mysqldb://'+sql_username+':'+sql_password+'@127.0.0.1:3306/vacation', echo=False)

# Pull the timeline data out of the mySQL server
# and put in in the original dataframe.

sql_query = 'SELECT * FROM timeline_tweets_stemmed GROUP BY `Tweet ID`, Destination'
df_timeline = pd.read_sql_query(sql_query,engine)
print len(df_timeline.index), 'rows'
df_timeline.head(2)

# Save the data on my mySQL server

if False:
    chunk_size = 10000
    chunk = 0
    while True:
        df_chunk = df_timeline.iloc[chunk*chunk_size:(chunk + 1)*chunk_size]
        if len(df_chunk.index) < 1:
            break
        df_chunk.to_sql('timeline_tweets_stemmed_unique', engine, if_exists='append', index=False)
        chunk += 1
        print chunk, 'chunks completed'
    print 'task completed'
