Event places map

In [1]:
%run startup.ipy

Last notebook update: 2018-06-06
Git repo: git@bitbucket.org:loujine/musicbrainz-dataviz.git
Importing libs

Defining database parameters

Defining *sql* helper function
Last database update: 2018-06-02

Python packages versions:
numpy       1.14.3
pandas      0.23.0
sqlalchemy  1.2.8
CPython 3.7.0b5
IPython 6.4.0
In [2]:
def event_places(band_mbid):
    df = sql("""
SELECT e.name AS event_name,
       p.name AS place_name,
       p.coordinates,
       to_date(to_char(e.begin_date_year, '9999') || 
               to_char(e.begin_date_month, '99') || 
               to_char(e.begin_date_day, '99'), 'YYYY MM DD') AS start,
       to_date(to_char(e.end_date_year, '9999') || 
               to_char(e.end_date_month, '99') || 
               to_char(e.end_date_day, '99'), 'YYYY MM DD') AS end,
       e.gid AS mbid
  FROM event          AS e
  JOIN l_artist_event AS lae ON e.id = lae.entity1
  JOIN artist         AS a   ON a.id = lae.entity0
  JOIN l_event_place  AS lep ON e.id = lep.entity0
  JOIN place          AS p   ON p.id = lep.entity1
 WHERE a.gid = %(band_mbid)s
   AND p.coordinates IS NOT NULL
   ;""", band_mbid=band_mbid)
    df['lat'] = df.coordinates.apply(lambda t: eval(t)[0])
    df['lon'] = df.coordinates.apply(lambda t: eval(t)[1])
    df.drop('coordinates', axis=1, inplace=True)
    return df

band_mbid = 'a74b1b7f-71a5-4011-9441-d0b5e4122711' # Radiohead
events = event_places(band_mbid)
events.head()
Out[2]:
event_name place_name start end mbid lat lon
0 Southside 2003 Flugplatz Neuhausen ob Eck 2003-06-20 2003-06-22 db83a40b-bd7e-4731-b731-1563edf681b9 47.976389 8.903889
1 Main Square Festival 2008 Grand'Place 2008-07-04 2008-07-06 c3e6cdb2-2759-4c97-9dcd-6ae5e8cdbde8 50.292326 2.780925
2 Glastonbury Festival 1997 Worthy Farm 1997-06-27 1997-06-29 0bc43ba0-27fd-495d-94cb-0bc3374de145 51.155000 -2.586000
3 The Struggle Continues... AccorHotels Arena 1998-12-10 1998-12-10 a0f96c61-234d-4ec5-ac73-5e62afe73b5e 48.838541 2.378599
4 2006 Bonnaroo Music Festival, Day 3: What Stage Great Stage Park 2006-06-17 2006-06-17 2be74cd5-da0f-4481-aa4e-99d3f4b5e1cd 35.477806 -86.050611
In [3]:
iplot({'data': [{'lat': events.lat,
                 'lon': events.lon,
                 'text': events.event_name,
                 'type': 'scattergeo',
                 }],
       'layout': {'geo': {'showland': True,
                          'showcountries': True,
                          'projection': {'type': 'stereographic'},
                          },
                  'title': 'Radiohead events by place',
                  }
      })