BiciMAD is the e-bike sharing service of the Spanish Capital, started in 2014, and managed by the Municipal company of Madrid from May 2016.

The purpose of this analysis is to understand how many stations are part of the network, how much is their capability and the extent of the network. I will also try to classify each station in the Madrid neighborhood.

Please note that the figures, the dataset, the maps and the Python notebook discussed in this article can be found (for free) on GitHub.

Dataset

The company Empresa Municipal de Transportes de Madrid provides many data in open format on the Open data EMT Madrid. These dataset, limited to the BiciMAD service, concern the hourly situation of the stations for the months of July, August and September 2018 and the details of the individual rentals from April 2017 to September 2018. As this analysis concerns the stations, the most interesting files are without a doubt the first ones.

Let’s download the Situación estaciones bicimad por día y hora de Septiembre de 2018 archive to find out what data we have. Unlike data related to the rentals of bicycles, there is no guide explaining the fields and how the data were pretreated before being extracted, saved and uploaded online

The file is a RAR archive that contains a single JSON file. Each line of the file is a separate JSON file entity, that contains two elements at the first level: the date-time and a series of data related to the situation of the stations at that specific time.

The data have a good level of detail (hourly) and would allow a more in-depth analysis if the availability of months covered more than just three months. Considering the seasonality of the city’s tourist flow and the continental climate which is characterised by high temperatures during summer and low temperatures during winter, I cannot consider that these three summer months as significant to estimate the rest of the year.

In this regard, as our goal is to build a registry of the stations, I considered the most updated data available, that is the first row of the September 2018 file, which is the situation of 2018-09-30T23:42:38.647290″

{      
"_id" : "2018-07-02T10:29:17.579515",      
"stations" : [
{
"activate" : NumberInt(1),
"name" : "Puerta del Sol A",
"reservations_count" : NumberInt(0),
"light" : NumberInt(0),
"total_bases" : NumberInt(24),
"free_bases" : NumberInt(21),
"number" : "1a",
"longitude" : "-3.7024255",
"no_available" : NumberInt(0),
"address" : "Puerta del Sol n� 1",
"latitude" : "40.4168961",
"dock_bikes" : NumberInt(2),
"id" : NumberInt(1)
},
{
"activate" : NumberInt(1),
"name" : "Puerta del Sol B",
"reservations_count" : NumberInt(1),
"light" : NumberInt(2),
"total_bases" : NumberInt(24),
"free_bases" : NumberInt(15),
"number" : "1b",
"longitude" : "-3.7024207",
"no_available" : NumberInt(0),
"address" : "Puerta del Sol n� 1",
"latitude" : "40.4170009",
"dock_bikes" : NumberInt(8),
"id" : NumberInt(2)
}, 

Since the file refers to September 2018, it counts in total (30 * 24) -1 = 719 lines.


activate
addressdock_bikesfree_basesidlatitudelightlongitudenameno_availablenumberreservations_counttotal_bases
01Puerta del Sol nº 1121140.41689610-3.7024255Puerta del Sol A01a124
11Puerta del Sol nº 1716240.41700090-3.7024207Puerta del Sol B01b024
21Calle Miguel Moya nº 1021340.42058860-3.7058415Miguel Moya02024
31Plaza del Conde Suchil nº 2-4414440.43029370-3.7069171Plaza Conde Suchil03018
41Calle Manuela Malasaña nº 5915540.42855242-3.7025875Malasaña04024


At first glance it is evident that the data (in term of columns) we need are much less than those made available by EMT, taking into account the purpose of this work and all the considerations made so far.

Classification by neighborhood

The first step of the analysis consists in mapping each BiciMAD station in the neighborhood it belongs. This has as a side effect, a verification that all the GPS coordinates of the stations are geographically within the metropolitan area of Madrid.

In practical terms it is necessary to find a list of Madrid neighborhoods and their boundaries in a lat / lon coordinate system, to construct polygons using the available data and to evaluate which of these each station belongs.

What is the top web site for downloading free geographical data? obviously Open Street Map!

From this table I could find the tag used to identify neighborhoods in spanish cities, which is 9, so I generated a GeoJSON file from Madrid data which have admin_level=9 AND boundary=administrative. The resulting file is available for download here on this website.

# Dictionary for features data
neighbours_multply = []
# List for polygon names
neighbours_names = []

def GeoClass_LoadFile(fname):
    global GeoData
    with open(fname, encoding="utf-8") as geoJsonFile:
        GeoData = json.load(geoJsonFile)
        
def GeoClass_BuildPolygon():
    global neighboursPolygon
    for neighbours in GeoData['features']:
        if neighbours.get('properties').get('type')=='boundary':
            neighbours_shape = asShape(neighbours['geometry'])
            neighbours_multply.append(neighbours_shape)
            neighbours_names.append(neighbours['properties']['name'])
    neighboursPolygon = MultiPolygon(neighbours_multply)

def GeoClass_GetPName(point):
    i=0
    while i < len(neighbours_names):
        if neighboursPolygon[i].contains(point):
            Resp=neighbours_names[i]
        i+=1
    return Resp

At this point, I have defined three Python functions that can generate polygons and verify that each station belongs to one of them.

Prima di procedere oltre occorre fare alcune valutazioni legate ai dati estratti verificando che le colonne che abbiamo selezionato non contengano campi vuoti

df_stations['neighbours'] = df_stations.apply(lambda row: GeoClass_GetPName(Point(pd.to_numeric(row['longitude']),pd.to_numeric(row['latitude']))), axis=1) df_stations['neighbours'].unique() 

array([‘Centro’, ‘Chamberí’, ‘Moncloa-Aravaca’, ‘Arganzuela’, ‘Retiro’, ‘Salamanca’, ‘Tetuán’, ‘Chamartín’], dtype=object)

The results of this elaboration is saved into this CSV file, it will be useful for future applications.

Evaluation of number of bases

A second level of analysis has been conducted considering the number of bicycle bases for each station and, contrary to my expectations, they are made up almost of standard modules, in fact the number is always between 18 and 30.

BiciMAD bases per stations
BiciMAD bases per stations

As it is easy to verify, in the case of particularly barycentric points for cyclist flows (for example close to other nodes of the transport network), the company that manages BiciMAD bikes has positioned more than one stations rather than “at the logical level” consider a single station with more bases.

df_stations.nlargest(5, 'total_bases')[['name','total_bases']]

name
total_bases
id
21Banco de España A30
95General Pardiñas30
6Fuencarral27
34San Quintín27
35Calle Mayor27
df_stations.nsmallest(5, 'total_bases')[['name','total_bases']]

name
total_bases
id
165Pº Castellana – Glorieta de Emilio Castelar12
4Plaza Conde Suchil18
36Plaza de la Provincia18
111Colón A18
112Colón B18

At this point I can also make some general assessments considering the total number of e-bike that the network can support (which is the total number of bases considering that every time a user should have al least one bases where park a bike), 4095 bases and evaluate their distribution in the individual neighborhoods.

df_stations.groupby(['neighbours'])['total_bases'].agg('sum')
  • Arganzuela 342
  • Centro 1380
  • Chamartín 306
  • Chamberí 437
  • Moncloa-Aravaca 243
  • Retiro 550
  • Salamanca 669
  • Tetuán 168

As was easily expected, the Madrid Center neighborhood contains the largest number of seats. This is evident as it is in this district that the tourist flows are more concentrated and moreover it is here that we expect that the flows of workers who use the BiciMAD service mainly go to work.

Making a map of BiciMAD station

As a “bonus” I decided to generate a map of the individual stations, this with a dual purpose: to give an easy tool to view roughly any areas of Madrid not covered by the stations and to provide not Madrid people with a representation of their location, to facilitate their life in case they decide to go to the Spanish capital city and use the BiciMAD service.

m = folium.Map([40.417000, -3.703000], zoom_start=13,tiles='http://{s}.tiles.wmflabs.org/bw-mapnik/{z}/{x}/{y}.png', attr="<a href=https://www.simboli.eu/>Simboli.EU</a>")

for index, row in df_stations.iterrows():
    folium.Marker([float(row['latitude']), float(row['longitude'])],
                  popup='<h4>Station '+row['name']+'</h4>\
                         <b>Number: </b>'+row['number']+'<br/>\
                         <b>Neighbours: </b>'+row['neighbours']+'<br/>\
                         <b>Total bases: </b>'+str(row['total_bases'])+'<br/>\
                         <b>Address: </b>'+row['address']+'<br/>\
                         <b>Latitude: </b>'+str(row['latitude'])[0:8]+'<br/>\
                         <b>Longitude: </b>'+str(row['longitude'])[0:8]+'<br/><br/>\
                         <a href="http://www.google.com/maps/place/'+str(row['latitude'])+','+str(row['longitude'])+'">Open with Google Maps</a>',
                  icon=folium.Icon(color='red' if row.neighbours == 'Centro' else 'orange', 
                                                      prefix='fa', icon='bicycle'),).add_to(m)

legendHTML = '''
    <style>@import url('https://fonts.googleapis.com/css?family=Roboto+Slab');</style>
    <p style="font-family: 'Roboto Slab', sans-serif;color:blue;">This map is part of the article <a href="https://www.simboli.eu/blog/lets-analyze-e-bike-sharing-stations-of-madrid/">'let’s analyze e-bike sharing stations of Madrid'</a>.</p>

    <div style="position: 
        fixed;
        background-color:white;
        bottom: 50px; 
        left: 50px; 
        width: 150px; 
        height: 90px; 
        border:2px solid black; 
        padding: 3px;
        z-index:9999; 
        font-size:14px;
        font-family:Aleo, Times New Roman">Legend<br/>
        <i style="color:red">City center</i><br>
        <i style="color:orange">Other neighborhoods</i>
    </div>
'''
m.get_root().html.add_child(folium.Element(legendHTML))

titleHTML = '''
    <style>@import url('https://fonts.googleapis.com/css?family=Roboto+Slab');</style>
    <div style="position: 
        fixed;
        top: 50px; 
        left: 100px; 
        width: 450px; 
        height: 60px; 
        background-color: white
        border:2px solid red; 
        padding: 3px;
        z-index:9999; 
        font-size:13px;
        font-family:font-family: 'Roboto Slab', sans-serif">
        <h3 style="text-shadow: 0 0 2px white; color:#0000b3">Map of BiciMAD stations in Madrid</h3>
    </div>
'''
m.get_root().html.add_child(folium.Element(titleHTML))

m.save('map_stations.html')
BiciMAD map
BiciMAD map

The map, generated with Folium module, is available here.