Network analysis

First, import relevant libraries:


In [1]:
import warnings
warnings.filterwarnings('ignore')

import numpy as np
import pandas as pd
%matplotlib inline
import matplotlib.pyplot as plt
from pylab import *

import igraph as ig # Need to install this in your virtual environment
from re import sub

In [2]:
import os
import sys
sys.path.append('/home/mmalik/optourism-repo' + "/pipeline")
from firenzecard_analyzer import *

sys.path.append('../../src/')
from utils.database import dbutils

conn = dbutils.connect()
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 [34]:
# temp = df.groupby(['user_id','museum_name','entry_time']).sum()
# temp[temp['is_card_with_minors']>0].head(50)


Out[34]:
adults_first_use adults_reuse total_adults minors museum_id entry_is_adult is_card_with_minors day_of_week
user_id museum_name entry_time
1473906 Museo di San Marco 2016-07-24 11:58:00 0 1 1 1 50 1 1 12
2017453 Torre di Palazzo Vecchio 2016-06-17 20:04:00 0 1 1 1 82 1 1 8
2017468 Battistero di San Giovanni 2016-06-16 10:46:00 1 0 1 2 9 1 2 9
Galleria degli Uffizi 2016-06-18 10:45:00 0 1 1 2 30 1 2 15
Galleria dell'Accademia di Firenze 2016-06-16 12:56:00 0 1 1 2 33 1 2 9
Museo Galileo 2016-06-18 15:56:00 0 1 1 2 87 1 2 15
Museo di Palazzo Vecchio 2016-06-18 13:17:00 0 1 1 2 69 1 2 15
Palazzo Medici Riccardi 2016-06-16 12:00:00 0 1 1 2 111 1 2 9
Palazzo Pitti 2 Ð Giardino di Boboli, Museo degli Argenti, Museo delle Porcellan 2016-06-17 12:34:00 0 1 1 2 114 1 2 12
Torre di Palazzo Vecchio 2016-06-18 14:58:00 0 1 1 2 123 1 2 15
2017470 Battistero di San Giovanni 2016-06-16 12:07:00 0 1 1 2 9 1 2 9
Galleria degli Uffizi 2016-06-16 09:41:00 1 0 1 2 30 1 2 9
Galleria dell'Accademia di Firenze 2016-06-16 14:19:00 0 1 1 2 33 1 2 9
2017487 Battistero di San Giovanni 2016-06-15 13:13:00 0 1 1 2 9 1 2 6
Galleria degli Uffizi 2016-06-15 11:26:00 0 1 1 2 30 1 2 6
Galleria dell'Accademia di Firenze 2016-06-15 16:10:00 0 1 1 2 33 1 2 6
Museo Galileo 2016-06-14 12:14:00 1 0 1 2 87 1 2 3
Museo Nazionale del Bargello 2016-06-14 14:04:00 0 1 1 2 96 1 2 3
Museo di Palazzo Vecchio 2016-06-14 21:35:00 0 1 1 2 69 1 2 3
2017489 Galleria degli Uffizi 2016-06-14 12:03:00 1 0 1 1 20 1 1 2
Museo Galileo 2016-06-15 13:46:00 0 1 1 1 58 1 1 4
2017800 Battistero di San Giovanni 2016-06-20 14:22:00 0 1 1 1 6 1 1 0
2017818 Museo Nazionale del Bargello 2016-06-22 10:24:00 1 0 1 2 96 1 2 6
Palazzo Pitti 2 Ð Giardino di Boboli, Museo degli Argenti, Museo delle Porcellan 2016-06-24 10:58:00 0 1 1 2 114 1 2 12
2017821 Museo Galileo 2016-06-22 14:33:00 0 1 1 1 58 1 1 4
2017822 Battistero di San Giovanni 2016-06-22 08:46:00 0 1 1 1 6 1 1 4
Museo Nazionale del Bargello 2016-06-21 14:12:00 1 0 1 1 64 1 1 2
Museo di Palazzo Vecchio 2016-06-22 17:43:00 0 1 1 1 46 1 1 4
Torre di Palazzo Vecchio 2016-06-22 18:10:00 0 1 1 1 82 1 1 4
2017844 Museo di San Marco 2016-06-20 11:13:00 0 1 1 5 150 1 5 0
2019284 Basilica di Santa Croce 2016-07-03 15:07:00 0 1 1 1 2 1 1 12
Battistero di San Giovanni 2016-07-04 11:43:00 0 1 1 1 6 1 1 0
Palazzo Pitti 2 Ð Giardino di Boboli, Museo degli Argenti, Museo delle Porcellan 2016-07-02 12:48:00 1 0 1 1 76 1 1 10
2019298 Battistero di San Giovanni 2016-06-20 17:06:00 0 1 1 1 6 1 1 0
2019299 Battistero di San Giovanni 2016-06-20 17:06:00 0 1 1 1 6 1 1 0
2024301 Museo Galileo 2016-06-01 11:28:00 0 1 1 1 58 1 1 4
2024302 Museo Galileo 2016-06-01 11:28:00 0 1 1 1 58 1 1 4
2027552 Basilica San Lorenzo 2016-06-02 16:32:00 0 1 1 2 6 1 2 9
Basilica di Santa Croce 2016-06-04 09:29:00 0 1 1 2 3 1 2 15
Battistero di San Giovanni 2016-06-02 13:08:00 0 1 1 2 9 1 2 9
Casa Buonarroti 2016-06-04 10:16:00 0 1 1 2 21 1 2 15
Galleria degli Uffizi 2016-06-02 09:15:00 1 0 1 2 30 1 2 9
Galleria dell'Accademia di Firenze 2016-06-02 15:46:00 0 1 1 2 33 1 2 9
La Specola 2016-06-03 12:42:00 0 1 1 2 36 1 2 12
Museo Casa Dante 2016-06-04 10:47:00 0 1 1 2 45 1 2 15
Museo Galileo 2016-06-03 16:58:00 0 1 1 2 87 1 2 12
Museo Novecento 2016-06-03 18:30:00 0 1 1 2 99 1 2 12
Museo di Palazzo Vecchio 2016-06-03 15:23:00 0 1 1 2 69 1 2 12
Museo di Santa Maria Novella 2016-06-02 17:37:00 0 1 1 2 78 1 2 9
Palazzo Pitti 2 Ð Giardino di Boboli, Museo degli Argenti, Museo delle Porcellan 2016-06-03 09:37:00 0 1 1 2 114 1 2 12

In [35]:
# temp[(temp['is_card_with_minors']>0)&(temp['entry_is_adult']==0)]


Out[35]:
adults_first_use adults_reuse total_adults minors museum_id entry_is_adult is_card_with_minors day_of_week
user_id museum_name entry_time

Then, load the data (takes a few moments):


In [48]:
nodes = pd.read_sql('select * from optourism.firenze_card_locations', con=conn)
nodes.head()


Out[48]:
name longitude latitude id short_name string
0 Basilica di Santa Croce 11.262598 43.768754 1 Santa Croce 0
1 Basilica San Lorenzo 11.254430 43.774932 2 San Lorenzo 1
2 Battistero di San Giovanni 11.254966 43.773131 3 San Giovanni 2
3 Biblioteca Medicea Laurenziana 11.253924 43.774799 4 Laurenziana 3
4 Cappella Brancacci 11.243859 43.768334 5 Brancacci 4

In [49]:
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[49]:
user_id museum_name entry_time adults_first_use adults_reuse total_adults minors museum_id short_name string date hour
0 2070971 Palazzo Pitti Cumulativo 2016-08-08 11:25:00 0 1 1 0 39 Palazzo Pitti a 2016-08-08 2016-08-08 11:00:00
1 2070972 Palazzo Pitti Cumulativo 2016-08-08 11:25:00 0 1 1 0 39 Palazzo Pitti a 2016-08-08 2016-08-08 11:00:00
2 2071063 Palazzo Pitti Cumulativo 2016-08-08 11:40:00 0 1 1 0 39 Palazzo Pitti a 2016-08-08 2016-08-08 11:00:00
3 2070258 Palazzo Pitti Cumulativo 2016-08-08 11:43:00 0 1 1 0 39 Palazzo Pitti a 2016-08-08 2016-08-08 11:00:00
4 2069915 Palazzo Pitti Cumulativo 2016-08-08 11:43:00 0 1 1 0 39 Palazzo Pitti a 2016-08-08 2016-08-08 11:00:00

In [50]:
# 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.

Other exploratory/summary plots


In [16]:
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[16]:
total_people
short_name hour
Accademia 2016-06-01 08:00:00 17
2016-06-01 09:00:00 37
2016-06-01 10:00:00 51
2016-06-01 11:00:00 33
2016-06-01 12:00:00 36

In [17]:
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[17]:
short_name hour total_people
0 Palazzo Pitti 2016-06-01 00:00:00 0
1 Palazzo Pitti 2016-06-01 01:00:00 0
2 Palazzo Pitti 2016-06-01 02:00:00 0
3 Palazzo Pitti 2016-06-01 03:00:00 0
4 Palazzo Pitti 2016-06-01 04:00:00 0

In [17]:
# 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 [18]:
# 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 [18]:
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[18]:
total_adults minors total_people
museum_name
Battistero di San Giovanni 44047 5842 49889
Galleria degli Uffizi 40622 3717 44339
Galleria dell'Accademia di Firenze 39364 3053 42417
Museo di Palazzo Vecchio 29403 3354 32757
Palazzo Pitti 2 Ð Giardino di Boboli, Museo degli Argenti, Museo delle Porcellan 29142 3155 32297

In [11]:
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()


Transition/Origin-Destination (OD) matrix

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 [51]:
# 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


Out[51]:
index user_id museum_name entry_time adults_first_use adults_reuse total_adults minors museum_id short_name string date hour
0 396910 2095767 Battistero di San Giovanni 2016-09-30 17:55:00 1 0 1 0 3 San Giovanni 2 2016-09-30 2016-09-30 17:00:00
1 396909 2095766 Battistero di San Giovanni 2016-09-30 17:55:00 1 0 1 0 3 San Giovanni 2 2016-09-30 2016-09-30 17:00:00
2 396841 2095765 Battistero di San Giovanni 2016-09-30 17:24:00 1 0 1 0 3 San Giovanni 2 2016-09-30 2016-09-30 17:00:00
3 396842 2095765 Battistero di San Giovanni 2016-09-30 17:24:00 0 0 0 1 3 San Giovanni 2 2016-09-30 2016-09-30 17:00:00
4 396849 2095764 Museo di Palazzo Vecchio 2016-09-30 17:31:00 1 0 1 0 23 M. Palazzo Vecchio K 2016-09-30 2016-09-30 17:00:00

In [55]:
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[55]:
adults_first_use adults_reuse total_adults minors museum_id total_people
user_id entry_time date hour museum_name short_name string
1459702 2016-06-22 10:04:00 2016-06-22 2016-06-22 10:00:00 Galleria degli Uffizi Uffizi 9 1 0 1 0 10 1
2016-06-22 14:26:00 2016-06-22 2016-06-22 14:00:00 Museo Casa Dante M. Casa Dante C 0 1 1 0 15 1
2016-06-22 15:49:00 2016-06-22 2016-06-22 15:00:00 Galleria dell'Accademia di Firenze Accademia _ 0 1 1 0 11 1
2016-06-23 09:43:00 2016-06-23 2016-06-23 09:00:00 Battistero di San Giovanni San Giovanni 2 0 1 1 0 3 1
2016-06-23 11:14:00 2016-06-23 2016-06-23 11:00:00 Museo Galileo M. Galileo Q 0 1 1 0 29 1

In [56]:
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[56]:
user_id entry_time date hour museum_name short_name string total_people
0 1459702 2016-06-22 10:04:00 2016-06-22 2016-06-22 10:00:00 Galleria degli Uffizi Uffizi 9 1
1 1459702 2016-06-22 14:26:00 2016-06-22 2016-06-22 14:00:00 Museo Casa Dante M. Casa Dante C 1
2 1459702 2016-06-22 15:49:00 2016-06-22 2016-06-22 15:00:00 Galleria dell'Accademia di Firenze Accademia _ 1
3 1459702 2016-06-23 09:43:00 2016-06-23 2016-06-23 09:00:00 Battistero di San Giovanni San Giovanni 2 1
4 1459702 2016-06-23 11:14:00 2016-06-23 2016-06-23 11:00:00 Museo Galileo M. Galileo Q 1
5 1459702 2016-06-23 12:57:00 2016-06-23 2016-06-23 12:00:00 Museo di Palazzo Vecchio M. Palazzo Vecchio K 1
6 1459702 2016-06-23 13:41:00 2016-06-23 2016-06-23 13:00:00 Museo Nazionale del Bargello M. Bargello T 1
7 1459702 2016-06-23 15:05:00 2016-06-23 2016-06-23 15:00:00 Basilica di Santa Croce Santa Croce 0 1
8 1473903 2016-06-19 11:24:00 2016-06-19 2016-06-19 11:00:00 Galleria degli Uffizi Uffizi 9 1
9 1473903 2016-06-20 12:05:00 2016-06-20 2016-06-20 12:00:00 Battistero di San Giovanni San Giovanni 2 1

In [58]:
df4['from'] = u'source' # Initialize 'from' column with 'source'
df4['to'] = df4['short_name'] # Copy 'to' column with row's museum_name
df4.head(10)


Out[58]:
user_id entry_time date hour museum_name short_name string total_people from to
0 1459702 2016-06-22 10:04:00 2016-06-22 2016-06-22 10:00:00 Galleria degli Uffizi Uffizi 9 1 source Uffizi
1 1459702 2016-06-22 14:26:00 2016-06-22 2016-06-22 14:00:00 Museo Casa Dante M. Casa Dante C 1 source M. Casa Dante
2 1459702 2016-06-22 15:49:00 2016-06-22 2016-06-22 15:00:00 Galleria dell'Accademia di Firenze Accademia _ 1 source Accademia
3 1459702 2016-06-23 09:43:00 2016-06-23 2016-06-23 09:00:00 Battistero di San Giovanni San Giovanni 2 1 source San Giovanni
4 1459702 2016-06-23 11:14:00 2016-06-23 2016-06-23 11:00:00 Museo Galileo M. Galileo Q 1 source M. Galileo
5 1459702 2016-06-23 12:57:00 2016-06-23 2016-06-23 12:00:00 Museo di Palazzo Vecchio M. Palazzo Vecchio K 1 source M. Palazzo Vecchio
6 1459702 2016-06-23 13:41:00 2016-06-23 2016-06-23 13:00:00 Museo Nazionale del Bargello M. Bargello T 1 source M. Bargello
7 1459702 2016-06-23 15:05:00 2016-06-23 2016-06-23 15:00:00 Basilica di Santa Croce Santa Croce 0 1 source Santa Croce
8 1473903 2016-06-19 11:24:00 2016-06-19 2016-06-19 11:00:00 Galleria degli Uffizi Uffizi 9 1 source Uffizi
9 1473903 2016-06-20 12:05:00 2016-06-20 2016-06-20 12:00:00 Battistero di San Giovanni San Giovanni 2 1 source San Giovanni

In [59]:
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['museum_name'].shift(1)[make_link]
df4.head(50)


Out[59]:
user_id entry_time date hour museum_name short_name string total_people from to
0 1459702 2016-06-22 10:04:00 2016-06-22 2016-06-22 10:00:00 Galleria degli Uffizi Uffizi 9 1 source Uffizi
1 1459702 2016-06-22 14:26:00 2016-06-22 2016-06-22 14:00:00 Museo Casa Dante M. Casa Dante C 1 Galleria degli Uffizi M. Casa Dante
2 1459702 2016-06-22 15:49:00 2016-06-22 2016-06-22 15:00:00 Galleria dell'Accademia di Firenze Accademia _ 1 Museo Casa Dante Accademia
3 1459702 2016-06-23 09:43:00 2016-06-23 2016-06-23 09:00:00 Battistero di San Giovanni San Giovanni 2 1 source San Giovanni
4 1459702 2016-06-23 11:14:00 2016-06-23 2016-06-23 11:00:00 Museo Galileo M. Galileo Q 1 Battistero di San Giovanni M. Galileo
5 1459702 2016-06-23 12:57:00 2016-06-23 2016-06-23 12:00:00 Museo di Palazzo Vecchio M. Palazzo Vecchio K 1 Museo Galileo M. Palazzo Vecchio
6 1459702 2016-06-23 13:41:00 2016-06-23 2016-06-23 13:00:00 Museo Nazionale del Bargello M. Bargello T 1 Museo di Palazzo Vecchio M. Bargello
7 1459702 2016-06-23 15:05:00 2016-06-23 2016-06-23 15:00:00 Basilica di Santa Croce Santa Croce 0 1 Museo Nazionale del Bargello Santa Croce
8 1473903 2016-06-19 11:24:00 2016-06-19 2016-06-19 11:00:00 Galleria degli Uffizi Uffizi 9 1 source Uffizi
9 1473903 2016-06-20 12:05:00 2016-06-20 2016-06-20 12:00:00 Battistero di San Giovanni San Giovanni 2 1 source San Giovanni
10 1473903 2016-06-20 15:44:00 2016-06-20 2016-06-20 15:00:00 Basilica San Lorenzo San Lorenzo 1 1 Battistero di San Giovanni San Lorenzo
11 1473903 2016-06-20 17:34:00 2016-06-20 2016-06-20 17:00:00 Museo di Palazzo Vecchio M. Palazzo Vecchio K 1 Basilica San Lorenzo M. Palazzo Vecchio
12 1473903 2016-06-21 11:22:00 2016-06-21 2016-06-21 11:00:00 Palazzo Pitti 2 Ð Giardino di Boboli, Museo de... Palazzo Pitti a 1 source Palazzo Pitti
13 1473903 2016-06-21 15:35:00 2016-06-21 2016-06-21 15:00:00 Museo Archeologico Nazionale di Firenze M. Archeologico B 1 Palazzo Pitti 2 Ð Giardino di Boboli, Museo de... M. Archeologico
14 1473904 2016-06-19 11:24:00 2016-06-19 2016-06-19 11:00:00 Galleria degli Uffizi Uffizi 9 1 source Uffizi
15 1473904 2016-06-20 12:05:00 2016-06-20 2016-06-20 12:00:00 Battistero di San Giovanni San Giovanni 2 1 source San Giovanni
16 1473904 2016-06-20 15:44:00 2016-06-20 2016-06-20 15:00:00 Basilica San Lorenzo San Lorenzo 1 1 Battistero di San Giovanni San Lorenzo
17 1473904 2016-06-20 17:34:00 2016-06-20 2016-06-20 17:00:00 Museo di Palazzo Vecchio M. Palazzo Vecchio K 1 Basilica San Lorenzo M. Palazzo Vecchio
18 1473904 2016-06-21 11:22:00 2016-06-21 2016-06-21 11:00:00 Palazzo Pitti 2 Ð Giardino di Boboli, Museo de... Palazzo Pitti a 1 source Palazzo Pitti
19 1473904 2016-06-21 15:35:00 2016-06-21 2016-06-21 15:00:00 Museo Archeologico Nazionale di Firenze M. Archeologico B 1 Palazzo Pitti 2 Ð Giardino di Boboli, Museo de... M. Archeologico
20 1473905 2016-07-01 13:56:00 2016-07-01 2016-07-01 13:00:00 Museo di Santa Maria Novella M. Santa Maria Novella N 1 source M. Santa Maria Novella
21 1473905 2016-07-02 11:21:00 2016-07-02 2016-07-02 11:00:00 Cappelle Medicee Cappelle Medicee 5 1 source Cappelle Medicee
22 1473905 2016-07-02 12:07:00 2016-07-02 2016-07-02 12:00:00 Battistero di San Giovanni San Giovanni 2 1 Cappelle Medicee San Giovanni
23 1473905 2016-07-02 13:29:00 2016-07-02 2016-07-02 13:00:00 Galleria dell'Accademia di Firenze Accademia _ 1 Battistero di San Giovanni Accademia
24 1473905 2016-07-02 15:06:00 2016-07-02 2016-07-02 15:00:00 Basilica di Santa Croce Santa Croce 0 1 Galleria dell'Accademia di Firenze Santa Croce
25 1473906 2016-07-23 09:38:00 2016-07-23 2016-07-23 09:00:00 Palazzo Pitti 2 Ð Giardino di Boboli, Museo de... Palazzo Pitti a 1 source Palazzo Pitti
26 1473906 2016-07-23 15:10:00 2016-07-23 2016-07-23 15:00:00 Palazzo Strozzi Palazzo Strozzi b 1 Palazzo Pitti 2 Ð Giardino di Boboli, Museo de... Palazzo Strozzi
27 1473906 2016-07-23 16:30:00 2016-07-23 2016-07-23 16:00:00 Galleria degli Uffizi Uffizi 9 1 Palazzo Strozzi Uffizi
28 1473906 2016-07-23 19:15:00 2016-07-23 2016-07-23 19:00:00 Museo di Palazzo Vecchio M. Palazzo Vecchio K 1 Galleria degli Uffizi M. Palazzo Vecchio
29 1473906 2016-07-24 09:10:00 2016-07-24 2016-07-24 09:00:00 Galleria dell'Accademia di Firenze Accademia _ 1 source Accademia
30 1473906 2016-07-24 10:48:00 2016-07-24 2016-07-24 10:00:00 Museo degli Innocenti M. Innocenti D 1 Galleria dell'Accademia di Firenze M. Innocenti
31 1473906 2016-07-24 11:58:00 2016-07-24 2016-07-24 11:00:00 Museo di San Marco M. San Marco M 2 Museo degli Innocenti M. San Marco
32 1473906 2016-07-24 14:19:00 2016-07-24 2016-07-24 14:00:00 Basilica di Santa Croce Santa Croce 0 1 Museo di San Marco Santa Croce
33 1473906 2016-07-24 16:29:00 2016-07-24 2016-07-24 16:00:00 Basilica San Lorenzo San Lorenzo 1 1 Basilica di Santa Croce San Lorenzo
34 1473906 2016-07-25 09:43:00 2016-07-25 2016-07-25 09:00:00 Cappelle Medicee Cappelle Medicee 5 1 source Cappelle Medicee
35 1473906 2016-07-25 10:06:00 2016-07-25 2016-07-25 10:00:00 Biblioteca Medicea Laurenziana Laurenziana 3 1 Cappelle Medicee Laurenziana
36 1473907 2016-07-23 09:38:00 2016-07-23 2016-07-23 09:00:00 Palazzo Pitti 2 Ð Giardino di Boboli, Museo de... Palazzo Pitti a 1 source Palazzo Pitti
37 1473907 2016-07-23 15:10:00 2016-07-23 2016-07-23 15:00:00 Palazzo Strozzi Palazzo Strozzi b 1 Palazzo Pitti 2 Ð Giardino di Boboli, Museo de... Palazzo Strozzi
38 1473907 2016-07-23 16:30:00 2016-07-23 2016-07-23 16:00:00 Galleria degli Uffizi Uffizi 9 1 Palazzo Strozzi Uffizi
39 1473907 2016-07-23 19:15:00 2016-07-23 2016-07-23 19:00:00 Museo di Palazzo Vecchio M. Palazzo Vecchio K 1 Galleria degli Uffizi M. Palazzo Vecchio
40 1473907 2016-07-24 09:10:00 2016-07-24 2016-07-24 09:00:00 Galleria dell'Accademia di Firenze Accademia _ 1 source Accademia
41 1473907 2016-07-24 10:48:00 2016-07-24 2016-07-24 10:00:00 Museo degli Innocenti M. Innocenti D 1 Galleria dell'Accademia di Firenze M. Innocenti
42 1473907 2016-07-24 11:58:00 2016-07-24 2016-07-24 11:00:00 Museo di San Marco M. San Marco M 1 Museo degli Innocenti M. San Marco
43 1473907 2016-07-24 14:19:00 2016-07-24 2016-07-24 14:00:00 Basilica di Santa Croce Santa Croce 0 1 Museo di San Marco Santa Croce
44 1473907 2016-07-24 16:29:00 2016-07-24 2016-07-24 16:00:00 Basilica San Lorenzo San Lorenzo 1 1 Basilica di Santa Croce San Lorenzo
45 1473907 2016-07-25 09:43:00 2016-07-25 2016-07-25 09:00:00 Cappelle Medicee Cappelle Medicee 5 1 source Cappelle Medicee
46 1473907 2016-07-25 10:06:00 2016-07-25 2016-07-25 10:00:00 Biblioteca Medicea Laurenziana Laurenziana 3 1 Cappelle Medicee Laurenziana
47 1474634 2016-06-09 13:36:00 2016-06-09 2016-06-09 13:00:00 Basilica San Lorenzo San Lorenzo 1 1 source San Lorenzo
48 1474634 2016-06-09 14:07:00 2016-06-09 2016-06-09 14:00:00 Battistero di San Giovanni San Giovanni 2 1 Basilica San Lorenzo San Giovanni
49 1474634 2016-06-10 16:02:00 2016-06-10 2016-06-10 16:00:00 Galleria degli Uffizi Uffizi 9 1 source Uffizi

In [62]:
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[62]:
user_id entry_time date hour museum_name short_name string total_people from to s t
0 1459702 2016-06-22 10:04:00 2016-06-22 2016-06-22 10:00:00 Galleria degli Uffizi Uffizi 9 1 source Uffizi 9
1 1459702 2016-06-22 14:26:00 2016-06-22 2016-06-22 14:00:00 Museo Casa Dante M. Casa Dante C 1 Galleria degli Uffizi M. Casa Dante 9 C
2 1459702 2016-06-22 15:49:00 2016-06-22 2016-06-22 15:00:00 Galleria dell'Accademia di Firenze Accademia _ 1 Museo Casa Dante Accademia C _
3 1459702 2016-06-23 09:43:00 2016-06-23 2016-06-23 09:00:00 Battistero di San Giovanni San Giovanni 2 1 source San Giovanni 2
4 1459702 2016-06-23 11:14:00 2016-06-23 2016-06-23 11:00:00 Museo Galileo M. Galileo Q 1 Battistero di San Giovanni M. Galileo 2 Q

In [67]:
df5 = df4.groupby('user_id')['s'].sum().to_frame()
df5.head()


Out[67]:
s
user_id
1459702 9C 2QKT
1473903 21 a
1473904 21 a
1473905 52_
1473906 ab9 _DM0 5

In [70]:
df6 = df5['s'].apply(lambda x: pd.Series(x.strip().split(' ')))
df6.head()


Out[70]:
0 1 2 3
user_id
1459702 9C 2QKT NaN NaN
1473903 21 a NaN NaN
1473904 21 a NaN NaN
1473905 52_ NaN NaN NaN
1473906 ab9 _DM0 5 NaN

In [76]:
df6.describe()


Out[76]:
0 1 2 3
count 51031 35338 17054 1548
unique 4891 4939 2941 294
top 2 9 a _
freq 4433 2812 1062 112

In [77]:
df6.head(50)


Out[77]:
0 1 2 3
user_id
1459702 9C 2QKT NaN NaN
1473903 21 a NaN NaN
1473904 21 a NaN NaN
1473905 52_ NaN NaN NaN
1473906 ab9 _DM0 5 NaN
1473907 ab9 _DM0 5 NaN
1474634 1 M_T NaN
1474636 1 M_T NaN
2014298 a NaN NaN NaN
2016016 2 _ NaN NaN
2016021 2 NaN NaN NaN
2016022 2 NaN NaN NaN
2016024 a NaN NaN NaN
2017368 _ NaN NaN NaN
2017369 _ NaN NaN NaN
2017450 9 NaN NaN NaN
2017451 9 NaN NaN NaN
2017452 9 NaN NaN NaN
2017453 a_K NaN NaN NaN
2017454 92015Y NaN NaN NaN
2017455 92015Y NQK NaN
2017456 29 15B _TK NaN
2017457 a_K NaN NaN NaN
2017458 29 15B _TK NaN
2017459 9_ NaN NaN NaN
2017460 9b 2310 NT NaN
2017461 9b 2310 NT NaN
2017462 9b 2310 NT NaN
2017463 9b 2310 NT NaN
2017464 0 9 4aYM NaN
2017465 0 NaN NaN NaN
2017466 90 M Y15 NaN
2017467 90 M Y15 NaN
2017468 2Y 9Kc NaN
2017469 92 NaN NaN NaN
2017470 92 NaN NaN NaN
2017471 2 F531Y a NaN
2017472 2 F531Y a NaN
2017473 9 NaN NaN NaN
2017474 9 NaN NaN NaN
2017475 9 NaN NaN NaN
2017476 9Q NaN NaN NaN
2017477 9 NaN NaN NaN
2017478 9 NaN NaN NaN
2017479 9 NaN NaN NaN
2017480 9 2 NaN NaN
2017481 9 2 NaN NaN
2017482 9QK 531_ a NaN
2017483 9_ NaN NaN NaN
2017484 9_ NaN NaN NaN

In [24]:
# df4[df4['user_id']==2016016] # Do a check: before, my incorrect groupby order caused artifacts.

In [25]:
# df4[(df4['from']=="Galleria dell'Accademia di Firenze")&(df4['to']=="Galleria degli Uffizi")] # Before, this result was empty

In [26]:
# # 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 [27]:
# 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 [28]:
# 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[28]:
from to weight
0 Basilica San Lorenzo Basilica San Lorenzo 1
1 Basilica San Lorenzo Basilica di Santa Croce 521
2 Basilica San Lorenzo Battistero di San Giovanni 1282
3 Basilica San Lorenzo Biblioteca Medicea Laurenziana 2528
4 Basilica San Lorenzo Cappella Brancacci 60
5 Basilica San Lorenzo Cappelle Medicee 4519
6 Basilica San Lorenzo Casa Buonarroti 43
7 Basilica San Lorenzo Fondazione Scienza e Tecnica Ð Planetario 3
8 Basilica San Lorenzo Galleria degli Uffizi 457
9 Basilica San Lorenzo Galleria dell'Accademia di Firenze 1235

In [29]:
# Create and check the graph
g2 = ig.Graph.TupleList(df5.itertuples(index=False), directed=True, weights=True)
ig.summary(g2)


IGRAPH DNW- 43 1293 -- 
+ attr: name (v), weight (e)

In [30]:
g2.vs['name']


Out[30]:
['Basilica San Lorenzo',
 'Basilica di Santa Croce',
 'Battistero di San Giovanni',
 'Biblioteca Medicea Laurenziana',
 'Cappella Brancacci',
 'Cappelle Medicee',
 'Casa Buonarroti',
 'Fondazione Scienza e Tecnica \xc3\x90 Planetario',
 'Galleria degli Uffizi',
 "Galleria dell'Accademia di Firenze",
 'La Specola',
 'Musei Civici Fiesole',
 'Museo Archeologico Nazionale di Firenze',
 'Museo Casa Dante',
 'Museo Ebraico',
 'Museo Ferragamo',
 'Museo Galileo',
 'Museo Horne',
 'Museo Marini',
 'Museo Nazionale del Bargello',
 'Museo Novecento',
 'Museo Stefano Bardini',
 'Museo Stibbert',
 'Museo degli Innocenti',
 "Museo dell'Opificio delle Pietre Dure",
 'Museo di Antropologia',
 'Museo di Geologia',
 'Museo di Mineralogia',
 'Museo di Palazzo Davanzati',
 'Museo di Palazzo Vecchio',
 'Museo di San Marco',
 'Museo di Santa Maria Novella',
 'Orto Botanico',
 'Palazzo Medici Riccardi',
 'Palazzo Pitti 2 \xc3\x90 Giardino di Boboli, Museo degli Argenti, Museo delle Porcellan',
 'Palazzo Pitti Cumulativo',
 'Palazzo Strozzi',
 'Torre di Palazzo Vecchio',
 'Villa Bardini',
 'Museo del Calcio',
 'Museo di Preistoria',
 'Fondazione Primo Conti',
 u'source']

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]:
['Basilica San Lorenzo',
 'Basilica di Santa Croce',
 'Battistero di San Giovanni',
 'Biblioteca Medicea Laurenziana',
 'Cappella Brancacci',
 'Cappelle Medicee',
 'Casa Buonarroti',
 'Fondazione Scienza e Tecnica \xc3\x90 Planetario',
 'Galleria degli Uffizi',
 "Galleria dell'Accademia di Firenze",
 'La Specola',
 'Musei Civici Fiesole',
 'Museo Archeologico Nazionale di Firenze',
 'Museo Casa Dante',
 'Museo Ebraico',
 'Museo Ferragamo',
 'Museo Galileo',
 'Museo Horne',
 'Museo Marini',
 'Museo Nazionale del Bargello',
 'Museo Novecento',
 'Museo Stefano Bardini',
 'Museo Stibbert',
 'Museo degli Innocenti',
 "Museo dell'Opificio delle Pietre Dure",
 'Museo di Antropologia',
 'Museo di Geologia',
 'Museo di Mineralogia',
 'Museo di Palazzo Davanzati',
 'Museo di Palazzo Vecchio',
 'Museo di San Marco',
 'Museo di Santa Maria Novella',
 'Orto Botanico',
 'Palazzo Medici Riccardi',
 'Palazzo Pitti 2 \xc3\x90 Giardino di Boboli, Museo degli Argenti, Museo delle Porcellan',
 'Palazzo Pitti Cumulativo',
 'Palazzo Strozzi',
 'Torre di Palazzo Vecchio',
 'Villa Bardini',
 'Museo del Calcio',
 'Museo di Preistoria',
 'Fondazione Primo Conti',
 u'source']

In [33]:
layout = g2.layout('lgl')

In [34]:
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, bbox = (700,1000), layout = layout, margin=20, **visual_style)
ig.plot(g2, 'graph.svg', bbox = (1000,1000), **visual_style)


Out[34]:

In [ ]:
# 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 [ ]:
transition_matrix = pd.DataFrame(g2.get_adjacency(attribute='weight').data, columns=g2.vs['name'], index=g2.vs['name'])

In [ ]:
plt.matshow(np.log(transition_matrix))

In [ ]: