Restrictions in the Madrid region for nearly 5 million people
Restricted areas from early October are 10 large municipalities and 3 basic health areas
!pip install geopandas
import geopandas
import pandas as pd
import re
import matplotlib.pyplot as plt
import plotnine
from plotnine import *
Focus on Municipalities
For the latest round of restrictions due to Covid19 in the Madrid region the focus has switched from basic health areas to municipalities.
This notebook explores the municipalities of the region and the current rates of infection.
The Madrid region has 179 municipalities with population ranging from 48 people in Madarcos to 3.3 million people in Madrid capital (based on the 2019 population register). There are 10 municipalities with more than 100_000 inhabitants: Madrid, Móstoles, Alcalá de Henares, Fuenlabrada, Leganés, Getafe, Alcorcón, Torrejón de Ardoz, Parla, Alcobendas.
The regional authority provides weekly figures for Covid19 infections for 178 municipalities and the 21 districts of Madrid capital.
The health ministry has imposed new restrictions on municipalities with a population of over 100_000 that have an accumulated incidence rate for the last 14 days of over 500 per 100_000 inhabitants (and meet additional criteria).
The 10 municipalities in the Madrid region with a population of over 100_000 had a rate of between 525 (Alcalá de Henares) and 1_166 (Fuenlabrada) at 29 September 2020. Madrid capital had a rate of 776.
72% of the population of the Madrid region live in these municipalities.
These municipalities now have Covid19 restrictions based on the criteria of the health ministry.
At the same date, there are an additional 19 municipalities with rates over 1_000, with a total population of 67_300: Humanes de Madrid, Cobeña, Villa del Prado, Cubas de la Sagra, Moraleja de Enmedio, Torrelaguna, Casarrubuelos, Villaconejos, Navalagamella, Fuentidueña de Tajo, Villar del Olmo, Orusco de Tajuña, El Berrueco, Rozas de Puerto Real, Canencia, Braojos, Cervera de Buitrago. El Atazar, Puebla de la Sierra.
The regional authority has imposed restrictions on 3 basic health areas outside of the 10 large municipalities that have restrictions: Humanes de Madrid, Reyes Católicos, Villa del Prado.
df=pd.read_excel('./maps/Municipios/20codmun28.xls', header=2)
# source https://www.ine.es/daco/daco42/codmun/codmunmapa.htm
df.head()
munis_df=pd.read_csv('./fastpages/covid19_tia_muni_y_distritos_s.csv', delimiter=';', encoding='latin')
# source http://datos.comunidad.madrid/catalogo/dataset/covid19_tia_muni_y_distritos
for col in ['tasa_incidencia_acumulada_activos_ultimos_14dias', 'tasa_incidencia_acumulada_ultimos_14dias', 'tasa_incidencia_acumulada_total']:
munis_df[col] = pd.to_numeric(munis_df[col].apply(lambda x: re.sub(',', '.', x)))
munis_df.rename(columns={'tasa_incidencia_acumulada_ultimos_14dias':'tasa', 'casos_confirmados_ultimos_14dias':'casos', 'codigo_geometria':'codigo_geo'}, inplace=True)
munis_df.fecha_informe=munis_df.fecha_informe.apply(lambda x: x[5:10])
munis_df.casos_confirmados_activos_ultimos_14dias=munis_df.casos_confirmados_activos_ultimos_14dias.fillna(0).astype('int')
munis_df.casos=munis_df.casos.fillna(0).astype('int')
munis_df.casos_confirmados_totales=munis_df.casos_confirmados_totales.fillna(0).astype('int')
munis_df.codigo_geo=munis_df.codigo_geo.fillna(0).astype('int')
munis_df.head()
gdf=geopandas.read_file('./maps/Municipios/municipios_y_distritos_madrid.shp')
# source http://datos.comunidad.madrid/catalogo/dataset/covid19_tia_muni_y_distritos
gdf[gdf.pob_pad19==gdf.pob_pad19.min()] # municipality with smallest population
print(gdf.loc[:20,].pob_pad19.sum(), 'population Madrid capital')
print (len(gdf),' municipalities/districts')
gdf.loc[21:,][gdf.loc[21:,].pob_pad19>100000].sort_values('pob_pad19',ascending=False)[['municipio_','pob_pad19']]
# 9 other municipalities with a population over 100 000
gdf.loc[:20].sort_values('pob_pad19',ascending=False)[['municipio_','pob_pad19']]
# population of the 21 districts of Madrid
munis_df.tasa.fillna(0, inplace=True)
# allocate to bins of accumulated incidence rate for the last 14 days
cut_labels=['<200','200-400','400-600','600-800','800-1000','>1000']
cut_bins = [-1., 200., 400., 600., 800., 1000., max(munis_df.tasa)]
munis_df['tasa_bin'] = pd.cut(munis_df.tasa, bins=cut_bins, labels=cut_labels)
gdf['restricted']='0'
gdf.loc[:20,'restricted']='1'
gdf.loc[gdf.pob_pad19>100000,'restricted']='1'
# all districts in Madrid capital restricted
munis_df['restricted']='0'
munis_df.loc[gdf[gdf.restricted=='1'].index,'restricted']='1'
for muni in gdf[gdf.restricted=='1'].municipio_:
munis_df.loc[munis_df.municipio_distrito==muni, 'restricted']='1'
gdf.head()
print (gdf[gdf.restricted=='1'].pob_pad19.sum(), 'people restricted in 10 municipalities,',
int(.5+100*gdf[gdf.restricted=='1'].pob_pad19.sum()/gdf.pob_pad19.sum()),'% of the population of the region')
def col_func(fecha): return fecha[3]+fecha[4]+fecha[2]+fecha[0]+fecha[1]
plotnine.options.figure_size = (14, 8)
ggplot(munis_df[munis_df.fecha_informe>'08/24'], aes(x='tasa_bin', fill='restricted')) \
+ geom_histogram(binwidth=1, alpha=0.6, position='stack') \
+ facet_wrap('fecha_informe', labeller=labeller(cols=col_func)) \
+ theme_minimal() \
+ labs(title="Evolución de municipios/distritos con restricciones en las últimas 6 semanas",
x='Tasa incidencia acumulada ultimos 14 días',
y="Número de municipios / distritos")
week_df=munis_df[munis_df.fecha_informe==munis_df.fecha_informe.unique().max()][['municipio_distrito','tasa','casos','tasa_bin','restricted']]
gdf=gdf.merge(week_df,left_index=True, right_index=True)
print('Restricted area in 10 municipalities')
week_df[week_df.restricted=='1'].sort_values('tasa', ascending=False)[['municipio_distrito', 'tasa', 'casos']]
gdf['unrestricted']='0'
gdf.loc[week_df[20:][(week_df[20:].tasa>1000) & (week_df[20:].restricted=='0')].municipio_distrito.index, 'unrestricted']='1'
print((100_000*munis_df.loc[:20].casos.sum()/gdf.loc[:20].pob_pad19.sum()+.5).astype('int'),'rate for Madrid capital')
print(len(week_df[20:][(week_df[20:].tasa>1000) & (week_df[20:].restricted=='0')]),'other municipalities with a rate over 1000')
week_df[20:][(week_df[20:].tasa>1000) & (week_df[20:].restricted=='0')].sort_values('tasa', ascending=False)[['municipio_distrito', 'tasa', 'casos']]
print('total population of these municipalities is only',gdf.iloc[week_df[20:][(week_df[20:].tasa>1000) & (week_df[20:].restricted=='0')].municipio_distrito.index].pob_pad19.sum())
gdf.iloc[week_df[20:][(week_df[20:].tasa>1000) & (week_df[20:].restricted=='0')].municipio_distrito.index].sort_values('pob_pad19', ascending=False)[['municipio_','pob_pad19']]
zones_df=pd.read_csv('./fastpages/covid19_tia_zonas_basicas_salud_s.csv', delimiter=';', encoding='latin')
# source: http://datos.comunidad.madrid/catalogo/dataset/covid19_tia_zonas_basicas_salud
for col in ['tasa_incidencia_acumulada_activos_ultimos_14dias', 'tasa_incidencia_acumulada_ultimos_14dias', 'tasa_incidencia_acumulada_total']:
zones_df[col] = pd.to_numeric(zones_df[col].apply(lambda x: re.sub(',', '.', x)))
zones_df.rename(columns={'zona_basica_salud':'zona_basic','tasa_incidencia_acumulada_ultimos_14dias':'tasa', 'casos_confirmados_ultimos_14dias':'casos'}, inplace=True)
zones_df.fecha_informe=zones_df.fecha_informe.apply(lambda x: x[5:10])
print(len(zones_df[(zones_df.tasa>1000) & (zones_df.fecha_informe=='09/29')]),'basic health areas with a rate of over 1 000 per 100 000 population in the last 14 days')
zones_df[(zones_df.tasa>1000) & (zones_df.fecha_informe=='09/29')].sort_values('tasa', ascending=False)[['zona_basic','tasa']]
zones_df.tasa.fillna(0, inplace=True)
# allocates rates to bins
cut_labels=['<200','200-400','400-600','600-800','800-1000','>1000']
cut_bins = [-1., 200., 400., 600., 800., 1000., max(zones_df.tasa)]
zones_df['tasa_bin'] = pd.cut(zones_df.tasa, bins=cut_bins, labels=cut_labels)
week_df=zones_df[zones_df.fecha_informe==zones_df.fecha_informe.unique().max()][['zona_basic','tasa','casos','tasa_bin']]
df=(geopandas.read_file('./maps/zonas_basicas_salud/zonas_basicas_salud.shp')).merge(week_df)
# basic health areas with restrictions
df['restricted']='0'
for zone in ['Humanes de Madrid','Reyes Católicos','Villa del Prado']:
df.loc[df.zona_basic==zone, 'restricted']='1'
df['>1000']=df.tasa.apply(lambda x: 1 if x>1000 else 0)
fig, (ax1, ax2, ax3) = plt.subplots(ncols=3, sharex=True, sharey=True, figsize=(18,9))
gdf.plot(ax=ax1, column=gdf.tasa_bin, cmap='Reds', legend=True)
gdf.plot(ax=ax1, color='white', edgecolor='grey', alpha=0.1)
ax1.set_title('Rate of accumulated incidence\n by municipality')
ax1.axis('off')
gdf.plot(ax=ax2, column=gdf.restricted_x, cmap='Reds', legend=False)
gdf.plot(ax=ax2, color='white', edgecolor='grey', alpha=0.1)
ax2.set_title('Restricted municipalities\npopulation >100 000\nrate of over 500 per 100 000')
ax2.axis('off');
gdf.plot(ax=ax3, column=gdf.unrestricted, cmap='Reds', legend=False)
gdf.plot(ax=ax3, color='white', edgecolor='grey', alpha=0.1)
ax3.set_title('Municipalities with a rate > 1000\npopulation < 100 000')
ax3.axis('off');
fig, (ax1, ax2, ax3) = plt.subplots(ncols=3, sharex=True, sharey=True, figsize=(18,9))
df.plot(ax=ax1, column=df.tasa_bin, cmap='Reds', legend=True)
df.plot(ax=ax1, color='white', edgecolor='grey', alpha=0.1)
ax1.set_title('Rate of accumulated incidence\n by basic health area')
ax1.axis('off')
df.plot(ax=ax2, column=df['>1000'], cmap='Reds', alpha=1, legend=False)
df.plot(ax=ax2, color='white', edgecolor='grey', alpha=0.1)
ax2.set_title('Basic health areas with a rate > 1000')
ax2.axis('off');
df.plot(ax=ax3, column=df.restricted, cmap='Reds', alpha=1, legend=False)
gdf[gdf.restricted_x=='1'].plot(ax=ax3, color='red')
df.plot(ax=ax3, color='white', edgecolor='grey', alpha=0.1)
ax3.set_title('Restricted basic health areas\n(rate > 1 000 per 100 000)\nand restricted 10 large municipalities')
ax3.axis('off');
Closing Remarks
Colour coded maps showing the infection rates, whether for municipalities or basic health areas give a misleading impression because the population of the Madrid region is concentrated in the capital and the surrounding densely populated municipalities. Covid19 affects people but people are not uniformly distributed across the region.
Basic health areas are more heterogeneous, being based on population and access to local health centres, than municipalities, which vary enormously in population.