In [1]:
import warnings
warnings.filterwarnings('ignore')
import numpy as np
import pandas as pd
%matplotlib inline
import matplotlib.pyplot as plt
import matplotlib.ticker as ticker
from pylab import *
import igraph as ig # Need to install this in your virtual environment
import psycopg2
from re import sub
In [2]:
import os
import sys
sys.path.append('/home/mmalik/optourism-repo' + "/pipeline")
from firenzecard_analyzer import *
In [3]:
# TODO: connect with dbutils
conn_str = ""
conn = psycopg2.connect(conn_str)
cursor = conn.cursor()
In [41]:
# df = get_firenze_data(conn)
In [42]:
# df.head()
In [43]:
# ft = extract_features(df)
# ft.head()
In [44]:
# ft[ft['user_id']==2036595][['user_id','entry_time','total_card_use_count','day_of_week','museum_name']]
In [45]:
# ft.columns
In [46]:
# test = ft.groupby('date')['total_users_per_card'].sum()
In [47]:
# test.head()
In [4]:
# temp = df.groupby(['user_id','museum_name','entry_time']).sum()
# temp[temp['is_card_with_minors']>0].head(50)
In [5]:
# temp[(temp['is_card_with_minors']>0)&(temp['entry_is_adult']==0)]
Then, load the data (takes a few moments):
In [4]:
nodes = pd.read_sql('select * from optourism.firenze_card_locations', con=conn)
nodes.head()
Out[4]:
In [5]:
df = pd.read_sql('select * from optourism.firenze_card_logs', con=conn)
df['museum_id'].replace(to_replace=38,value=39,inplace=True)
df['short_name'] = df['museum_id'].replace(dict(zip(nodes['id'],nodes['short_name'])))
df['string'] = df['museum_id'].replace(dict(zip(nodes['id'],nodes['string'])))
df['date'] = pd.to_datetime(df['entry_time'], format='%Y-%m-%d %H:%M:%S').dt.date
df['hour'] = pd.to_datetime(df['date']) + pd.to_timedelta(pd.to_datetime(df['entry_time'], format='%Y-%m-%d %H:%M:%S').dt.hour, unit='h')
df.head()
Out[5]:
In [6]:
# Helper function for making summary tables/distributions
def frequency(dataframe,columnname):
out = dataframe[columnname].value_counts().to_frame()
out.columns = ['frequency']
out.index.name = columnname
out.reset_index(inplace=True)
out.sort_values(columnname,inplace=True)
out['cumulative'] = out['frequency'].cumsum()/out['frequency'].sum()
out['ccdf'] = 1 - out['cumulative']
return out
I propose distinguishing paths from flows. A path is an itinerary, and the flow is the number of people who take the flow. E.g., a family or a tour group produces one path, but adds mulitple people to the overall flow.
We now build a transition graph, a directed graph where an edge represents a person going from one museum to another within the same day.
We also produce the transition matrix, a row-normalized n-by-n matrix of the frequency of transition from the row node to the column node. If you take a vector of the current volumes in each location, and multiply that my the transition matrix, you get a prediction for the number of people on each node at the next time. This prediction can be refined with corrections for daily/weekly patterns and such.
In [7]:
timeunitname = 'hour'
timeunitcode = 'h'
df1 = df.groupby(['short_name',timeunitname]).sum()
df1['total_people'] = df1['total_adults']+df1['minors']
df1.drop(['museum_id','user_id','adults_first_use','adults_reuse','total_adults','minors'], axis=1, inplace=True)
df1.head()
Out[7]:
In [8]:
df1 = df1.reindex(pd.MultiIndex.from_product([df['short_name'].unique(),pd.date_range('2016-06-01','2016-10-01',freq=timeunitcode)]), fill_value=0)
df1.reset_index(inplace=True)
df1.columns = ['short_name','hour','total_people']
df1.head()
Out[8]:
In [9]:
# multiline plot with group by
fig, ax = plt.subplots(nrows = 1, ncols = 1, figsize=(15,8), dpi=300)
for key, grp in df1.groupby(['short_name']):
if key in ['Accademia','Uffizi']:
ax.plot(grp['hour'], grp['total_people'], linewidth=.5, label=str(key))
plt.legend(bbox_to_anchor=(1.1, 1), loc='upper right')
ax.set_xlim(['2016-06-01','2016-06-15'])
plt.show()
In [10]:
# multiline plot with group by
fig, ax = plt.subplots(nrows = 1, ncols = 1, figsize=(15,8), dpi=300)
for key, grp in df1.groupby(['short_name']):
ax.plot(grp['hour'], grp['total_people'], linewidth=.5, label=str(key))
plt.legend(bbox_to_anchor=(1.1, 1), loc='upper right')
ax.set_xlim(['2016-06-01','2016-06-15'])
plt.show()
In [11]:
df2 = df.groupby('museum_name').sum()[['total_adults','minors']]
df2['total_people'] = df2['total_adults'] + df2['minors']
df2.sort_values('total_people',inplace=True,ascending=False)
df2.head()
Out[11]:
In [12]:
df2.plot.bar(figsize=(16,8))
plt.title('Number of Firenze card visitors')
plt.xlabel('Museum')
plt.ylabel('Number of people')
# plt.yscale('log')
plt.show()
Now, we make a graph of the transitions for museums. To do this, we make an edgelist out of the above.
Specifically, we want an edgelist where the first column is the origin site, the second column is the destination site, the third column is the number of people (total adults plus rows for minors), and the fourth column is the time stamp of the entry to the destination museum.
But, there's a twist. We want to track when people arrive at the first museum of their day. We can do this by adding a dummy "source" node that everybody starts each day from. We can then query this dummy node to see not only which museum people activate their Firenze card from, but also the museum where they start their other days. For visualizations, we can drop it (or not visualize it).
We could also have people return to this source node at the end of each day (or make a separate "target" node for this purpose), but there would be no timestamp for that arrival so it would complicate the data with missing values. However, we might still want to do this, analogously to find the last museum people tend to visit in a day.
I will create this source node by the following: first, create an indicator for if the previous record is the same day and the same Firenze card. If it is, we make a link from the museum of the previous row and the museum of that row.
If the previous row is either a different day and/or a different user_id, make a link between the dummy "source" node and that row's museum.
I do this below in a different order: I initialize a "from" column with all source, then overwrite with the museum of the previous row if the conditions are met.
In [13]:
# df3 = df.sort_values(['user_id','entry_time'],ascending=False,inplace=False)
# df3.reset_index(inplace=True)
# df3.drop(['index','museum_id'], axis=1, inplace=True)
# df3.head()
# df3.groupby(['user_id','date','museum_name','entry_time']).sum().head(10) # Even though this grouping's multiindex looks nicer
In [14]:
df4 = df.groupby(['user_id','entry_time','date','hour','museum_name','short_name','string']).sum() # Need to group in this order to be correct further down
df4['total_people'] = df4['total_adults'] + df4['minors']
df4.head()
Out[14]:
In [15]:
df4.reset_index(inplace=True)
df4.drop(['adults_first_use','adults_reuse','total_adults','minors','museum_id'], axis = 1, inplace=True)
df4.head(10)
Out[15]:
In [16]:
df4['from'] = 'source' # Initialize 'from' column with 'source'
df4['to'] = df4['short_name'] # Copy 'to' column with row's museum_name
df4.head(10)
Out[16]:
In [17]:
make_link = (df4['user_id'].shift(1)==df4['user_id'])&(df4['date'].shift(1)==df4['date']) # Row indexes at which to overwrite 'source'
df4['from'][make_link] = df4['short_name'].shift(1)[make_link]
df4.head(50)
Out[17]:
In [18]:
df4['s'] = ' ' # Initialize 'from' column with 'source'
df4['t'] = df4['string'] # Copy 'to' column with row's museum_name
df4['s'][make_link] = df4['string'].shift(1)[make_link]
df4.head()
Out[18]:
In [19]:
df5 = df4.groupby('user_id')['s'].sum().to_frame()
df5.head()
Out[19]:
In [20]:
df6 = df5['s'].apply(lambda x: pd.Series(x.strip().split(' ')))
df6.head()
Out[20]:
In [21]:
df6.describe()
Out[21]:
In [22]:
df6.head(50)
Out[22]:
In [23]:
# df4[df4['user_id']==2016016] # Do a check: before, my incorrect groupby order caused artifacts.
In [24]:
# df4[(df4['from']=="Galleria dell'Accademia di Firenze")&(df4['to']=="Galleria degli Uffizi")] # Before, this result was empty
In [25]:
# # This manually checked the above result, to make sure I didn't make a mistake in creating the columns
# df4[((df4['museum_name'].shift(1)=="Galleria dell'Accademia di Firenze")\
# &(df4['museum_name']=="Galleria degli Uffizi")\
# &(df4['user_id']==df4['user_id'].shift(1))
# &(df4['date']==df4['date'].shift(1))
# )\
# | \
# ((df4['museum_name']=="Galleria dell'Accademia di Firenze")\
# &(df4['museum_name'].shift(-1)=="Galleria degli Uffizi")\
# &(df4['user_id']==df4['user_id'].shift(-1))
# &(df4['date']==df4['date'].shift(-1))
# )]
In [26]:
# df4[(df4['to']=="Galleria dell'Accademia di Firenze")&(df4['from']=="Galleria degli Uffizi")] # Once the above was finished, had to make sure the opposite problem didn't happen
In [27]:
# Create the actual edgelist for the transition matrix (of a first-order Markov chain)
df5 = df4.groupby(['from','to'])['total_people'].sum().to_frame()
df5.columns = ['weight']
df5.reset_index(inplace=True)
df5.head(10)
Out[27]:
In [28]:
# Create and check the graph
g2 = ig.Graph.TupleList(df5.itertuples(index=False), directed=True, weights=True)
ig.summary(g2)
In [29]:
g2.vs['name']
Out[29]:
In [30]:
# Delete the dummy 'source' node
g2.delete_vertices([v.index for v in g2.vs if v['name']==u'source'])
ig.summary(g2)
In [31]:
# Put in graph attributes to help with plotting
g2.vs['label'] = g2.vs["name"] # [sub("'","",i.decode('unicode_escape').encode('ascii','ignore')) for i in g2.vs["name"]] # Is getting messed up!
g2.vs['size'] = [.00075*i for i in g2.strength(mode='in',weights='weight')] # .00075 is from hand-tuning
In [32]:
g2.vs['label']
Out[32]:
In [33]:
latlon = pd.DataFrame({'short_name':g2.vs['label']}).merge(nodes[['short_name','longitude','latitude']],left_index=True,how='left',on='short_name')
In [34]:
g2.vs['x'] = (latlon['longitude']).values.tolist()
g2.vs['y'] = (-1*latlon['latitude']).values.tolist()
# layout = ig.Layout(latlon[['longitude','latitude']].values.tolist())
# len(layout)
In [35]:
# layout[0:]
In [36]:
# layout = g2.layout('lgl')
# len(layout)
In [37]:
visual_style = {}
visual_style["edge_width"] = [.001*i for i in g2.es["weight"]] # Scale weights. .001*i chosen by hand. Try also .05*np.sqrt(i)
visual_style['edge_arrow_size'] = [.00025*i for i in g2.es["weight"]] # .00025*i chosen by hand. Try also .01*np.sqrt(i)
visual_style['vertex_label_size'] = 8
visual_style['vertex_color'] = "rgba(100, 100, 255, .75)"
visual_style['edge_color'] = "rgba(0, 0, 0, .25)"
visual_style['edge_curved'] = True
ig.plot(g2.as_undirected(), 'graph.svg', bbox = (1000,1000), **visual_style)
Out[37]:
In [38]:
# print(g2.get_adjacency()) # This was another check; before it was very nearly upper triangular. Now it looks much better. Copy into a text editor and resize to see the whole matrix.
In [39]:
transition_matrix = pd.DataFrame(g2.get_adjacency(attribute='weight').data, columns=g2.vs['name'], index=g2.vs['name'])
In [40]:
transition_matrix
Out[40]:
In [41]:
# transition_matrix.reindex()
order = transition_matrix.sum(1).sort_values(ascending=False).to_frame().index
transition_matrix[order].reindex(order)
Out[41]:
In [42]:
plt.matshow(np.log(transition_matrix[order].reindex(order)))
Out[42]:
In [74]:
[order[0:1],order[0:]]
Out[74]:
In [81]:
fig = plt.figure(figsize=(10,10))#,dpi=300)
ax = fig.add_subplot(111)
cmap=plt.cm.PuBu
# cax = ax.matshow(transition_matrix[order].reindex(order),cmap=cmap)
cax = ax.matshow(np.log(transition_matrix[order].reindex(order)),cmap=cmap)
fig.colorbar(cax)
ax.set_xticklabels(['']+order.tolist(),rotation=90)
ax.set_yticklabels(['']+order.tolist())
ax.xaxis.set_major_locator(ticker.MultipleLocator(1))
ax.yaxis.set_major_locator(ticker.MultipleLocator(1))
plt.show()
In [ ]: