London2 v20
lgp
01/6/2021
library(tidyverse)
library(sf)INFORMACIÓN GEOGRÁFICA DE LA CIUDAD DE LONDRES
Los datos de shapes de los distritos de Londres fueron tomadas de https://joshuaboyd1.carto.com/tables/london_boroughs_proper/public/map
Mientras que otros datos estadísticos de Londres fueron tomados del portal de datos abiertos de dicha ciudad https://data.london.gov.uk/
Definimos un dataframe con las geometrias de los distritos de Londres con sus nombres
london_area <- st_read("london_boroughs_proper.geojson", stringsAsFactors=TRUE)## Reading layer `london_boroughs_proper' from data source `C:\Users\LUIS.PEREZ\Documents\curso UTDT\Modulo2\london_boroughs_proper.geojson' using driver `GeoJSON'
## Simple feature collection with 33 features and 4 fields
## Geometry type: MULTIPOLYGON
## Dimension: XY
## Bounding box: xmin: -0.508813 ymin: 51.28691 xmax: 0.335677 ymax: 51.69207
## Geodetic CRS: WGS 84head(london_area, 10)## Simple feature collection with 10 features and 4 fields
## Geometry type: MULTIPOLYGON
## Dimension: XY
## Bounding box: xmin: -0.41808 ymin: 51.28691 xmax: 0.225322 ymax: 51.69207
## Geodetic CRS: WGS 84
## name cartodb_id created_at updated_at
## 1 Barking and Dagenham 1 2015-07-01 06:57:45 2015-07-01 06:57:45
## 2 Barnet 2 2015-07-01 06:57:45 2015-07-01 06:57:45
## 3 Bexley 3 2015-07-01 06:57:45 2015-07-01 06:57:45
## 4 Brent 4 2015-07-01 06:57:45 2015-07-01 06:57:45
## 5 Bromley 5 2015-07-01 06:57:45 2015-07-01 06:57:45
## 6 Camden 6 2015-07-01 06:57:45 2015-07-01 06:57:45
## 7 City of London 7 2015-07-01 06:57:45 2015-07-01 06:57:45
## 8 Croydon 8 2015-07-01 06:57:45 2015-07-01 06:57:45
## 9 Ealing 9 2015-07-01 06:57:45 2015-07-01 06:57:45
## 10 Enfield 10 2015-07-01 06:57:45 2015-07-01 06:57:45
## geometry
## 1 MULTIPOLYGON (((0.148209 51...
## 2 MULTIPOLYGON (((-0.183361 5...
## 3 MULTIPOLYGON (((0.158044 51...
## 4 MULTIPOLYGON (((-0.212138 5...
## 5 MULTIPOLYGON (((0.076463 51...
## 6 MULTIPOLYGON (((-0.140804 5...
## 7 MULTIPOLYGON (((-0.083712 5...
## 8 MULTIPOLYGON (((-0.077495 5...
## 9 MULTIPOLYGON (((-0.334018 5...
## 10 MULTIPOLYGON (((-0.010576 5...summary(london_area)## name cartodb_id created_at
## Barking and Dagenham: 1 Min. : 1 Min. :2015-07-01 06:57:45
## Barnet : 1 1st Qu.: 9 1st Qu.:2015-07-01 06:57:45
## Bexley : 1 Median :17 Median :2015-07-01 06:57:45
## Brent : 1 Mean :17 Mean :2015-07-01 06:57:45
## Bromley : 1 3rd Qu.:25 3rd Qu.:2015-07-01 06:57:45
## Camden : 1 Max. :33 Max. :2015-07-01 06:57:45
## (Other) :27
## updated_at geometry
## Min. :2015-07-01 06:57:45 MULTIPOLYGON :33
## 1st Qu.:2015-07-01 06:57:45 epsg:4326 : 0
## Median :2015-07-01 06:57:45 +proj=long...: 0
## Mean :2015-07-01 06:57:45
## 3rd Qu.:2015-07-01 06:57:45
## Max. :2015-07-01 06:57:45
## Graficamos el mapa de Londres donde cada área es un distrito
ggplot()+
geom_sf(data=london_area, fill="deepskyblue3", color="white")+
labs(title="Distritos de Londres",
subtitle="Inner & Outer London",
x="longitud",
y="latitud")
Analizando el dataset a combinar con london_area
Teniendo en cuenta que el dataset london_area solo tiene los nombres de los distritos y los polígonos de sus áreas, estaría bueno agregar cierta información censal que sirva para futuros análisis
london_profiles <- read.csv("london-borough-profiles.csv", stringsAsFactors = TRUE)dim(london_profiles)## [1] 38 84head(london_profiles)## Code Area_name Inner._Outer_London
## 1 E09000001 City of London Inner London
## 2 E09000002 Barking and Dagenham Outer London
## 3 E09000003 Barnet Outer London
## 4 E09000004 Bexley Outer London
## 5 E09000005 Brent Outer London
## 6 E09000006 Bromley Outer London
## GLA_Population_Estimate_2017 GLA_Household_Estimate_2017
## 1 8800 5326
## 2 209000 78188
## 3 389600 151423
## 4 244300 97736
## 5 332100 121048
## 6 327900 140602
## Inland_Area_.Hectares. Population_density_.per_hectare._2017
## 1 290 30.3
## 2 3,611 57.9
## 3 8,675 44.9
## 4 6,058 40.3
## 5 4,323 76.8
## 6 15,013 21.8
## Average_Age._2017 Proportion_of_population_aged_0.15._2015
## 1 43.2 11.4
## 2 32.9 27.2
## 3 37.3 21.1
## 4 39.0 20.6
## 5 35.6 20.9
## 6 40.2 19.9
## Proportion_of_population_of_working.age._2015
## 1 73.1
## 2 63.1
## 3 64.9
## 4 62.9
## 5 67.8
## 6 62.6
## Proportion_of_population_aged_65_and_over._2015 Net_internal_migration_.2015.
## 1 15.5 -7
## 2 9.7 -1176
## 3 14.0 -3379
## 4 16.6 413
## 5 11.3 -7739
## 6 17.5 1342
## Net_international_migration_.2015. Net_natural_change_.2015.
## 1 665 30
## 2 2509 2356
## 3 5407 2757
## 4 760 1095
## 5 7640 3372
## 6 796 1445
## X._of_resident_population_born_abroad_.2015.
## 1 .
## 2 37.8
## 3 35.2
## 4 16.1
## 5 53.9
## 6 18.3
## Largest_migrant_population_by_country_of_birth_.2011.
## 1 United States
## 2 Nigeria
## 3 India
## 4 Nigeria
## 5 India
## 6 India
## X._of_largest_migrant_population_.2011.
## 1 2.8
## 2 4.7
## 3 3.1
## 4 2.6
## 5 9.2
## 6 1.1
## Second_largest_migrant_population_by_country_of_birth_.2011.
## 1 France
## 2 India
## 3 Poland
## 4 India
## 5 Poland
## 6 Ireland
## X._of_second_largest_migrant_population_.2011.
## 1 2
## 2 2.3
## 3 2.4
## 4 1.5
## 5 3.4
## 6 1.1
## Third_largest_migrant_population_by_country_of_birth_.2011.
## 1 Australia
## 2 Pakistan
## 3 Iran
## 4 Ireland
## 5 Ireland
## 6 Nigeria
## X._of_third_largest_migrant_population_.2011.
## 1 1.9
## 2 2.3
## 3 2
## 4 0.9
## 5 2.9
## 6 0.7
## X._of_population_from_BAME_groups_.2016.
## 1 27.5
## 2 49.5
## 3 38.7
## 4 21.4
## 5 64.9
## 6 18.9
## X._people_aged_3._whose_main_language_is_not_English_.2011_Census.
## 1 17.1
## 2 18.7
## 3 23.4
## 4 6
## 5 37.2
## 6 5.8
## Overseas_nationals_entering_the_UK_.NINo.._.2015.16.
## 1 975
## 2 7,538
## 3 13,094
## 4 2,198
## 5 22,162
## 6 2,924
## New_migrant_.NINo._rates._.2015.16.
## 1 152.2
## 2 59.1
## 3 53.1
## 4 14.4
## 5 100.9
## 6 14.4
## Largest_migrant_population_arrived_during_2015.16
## 1 India
## 2 Romania
## 3 Romania
## 4 Romania
## 5 Romania
## 6 Romania
## Second_largest_migrant_population_arrived_during_2015.16
## 1 France
## 2 Bulgaria
## 3 Poland
## 4 Poland
## 5 Italy
## 6 Italy
## Third_largest_migrant_population_arrived_during_2015.16
## 1 United States
## 2 Lithuania
## 3 Italy
## 4 Nigeria
## 5 Portugal
## 6 Spain
## Employment_rate_..._.2015. Male_employment_rate_.2015.
## 1 64.6 .
## 2 65.8 75.6
## 3 68.5 74.5
## 4 75.1 82.1
## 5 69.5 76
## 6 75.3 80.4
## Female_employment_rate_.2015. Unemployment_rate_.2015.
## 1 . .
## 2 56.5 11
## 3 62.9 8.5
## 4 68.5 7.6
## 5 62.6 7.5
## 6 70.4 5.3
## Youth_Unemployment_.claimant._rate_18.24_.Dec.15.
## 1 1.6
## 2 4.5
## 3 1.9
## 4 2.9
## 5 3.1
## 6 2.5
## Proportion_of_16.18_year_olds_who_are_NEET_..._.2014.
## 1 .
## 2 5.7
## 3 2.5
## 4 3.4
## 5 2.6
## 6 4.3
## Proportion_of_the_working.age_population_who_claim_out.of.work_benefits_..._.May.2016.
## 1 3.4
## 2 10.5
## 3 6.2
## 4 6.8
## 5 8.3
## 6 6
## X._working.age_with_a_disability_.2015.
## 1 .
## 2 17.2
## 3 14.9
## 4 15.9
## 5 17.7
## 6 15.9
## Proportion_of_working_age_people_with_no_qualifications_..._2015
## 1 .
## 2 11.3
## 3 5.2
## 4 10.8
## 5 6.2
## 6 4.3
## Proportion_of_working_age_with_degree_or_equivalent_and_above_..._2015
## 1 .
## 2 32.2
## 3 49
## 4 33.5
## 5 45.1
## 6 46.7
## Gross_Annual_Pay._.2016. Gross_Annual_Pay_._Male_.2016.
## 1 . .
## 2 27886 30104
## 3 33443 36475
## 4 34350 37881
## 5 29812 30129
## 6 37682 42026
## Gross_Annual_Pay_._Female_.2016.
## 1 .
## 2 24602
## 3 31235
## 4 28924
## 5 29600
## 6 32491
## Modelled_Household_median_income_estimates_2012.13
## 1 £63,620
## 2 £29,420
## 3 £40,530
## 4 £36,990
## 5 £32,140
## 6 £43,060
## X._adults_that_volunteered_in_past_12_months_.2010.11_to_2012.13.
## 1 .
## 2 20.5
## 3 33.2
## 4 22.1
## 5 17.3
## 6 29
## Number_of_jobs_by_workplace_.2014.
## 1 500400
## 2 58900
## 3 167300
## 4 80700
## 5 133600
## 6 127800
## X._of_employment_that_is_in_public_sector_.2014. Jobs_Density._2015
## 1 3.4 84.3
## 2 21.1 0.5
## 3 18.7 0.7
## 4 15.9 0.6
## 5 17.6 0.6
## 6 13.9 0.6
## Number_of_active_businesses._2015
## 1 26130
## 2 6560
## 3 26190
## 4 9075
## 5 15745
## 6 15695
## Two.year_business_survival_rates_.started_in_2013.
## 1 64.3
## 2 73.0
## 3 73.8
## 4 73.5
## 5 74.4
## 6 78.6
## Crime_rates_per_thousand_population_2014.15
## 1 .
## 2 83.4
## 3 62.7
## 4 51.8
## 5 78.8
## 6 64.1
## Fires_per_thousand_population_.2014.
## 1 12.3
## 2 3
## 3 1.6
## 4 2.3
## 5 1.8
## 6 2.3
## Ambulance_incidents_per_hundred_population_.2014. Median_House_Price._2015
## 1 . 799999
## 2 13.7 243500
## 3 11.1 445000
## 4 11.8 275000
## 5 12.1 407250
## 6 11.2 374975
## Average_Band_D_Council_Tax_charge_...._2015.16
## 1 931.2
## 2 1354.03
## 3 1397.07
## 4 1472.43
## 5 1377.24
## 6 1347.27
## New_Homes_.net._2015.16_.provisional. Homes_Owned_outright._.2014._.
## 1 80 .
## 2 730 16.4
## 3 1460 32.4
## 4 -130 38.1
## 5 1050 22.2
## 6 700 37.8
## Being_bought_with_mortgage_or_loan._.2014._.
## 1 .
## 2 27.4
## 3 25.2
## 4 35.3
## 5 22.6
## 6 34.9
## Rented_from_Local_Authority_or_Housing_Association._.2014._.
## 1 .
## 2 35.9
## 3 11.1
## 4 15.2
## 5 20.4
## 6 13.2
## Rented_from_Private_landlord._.2014._. X._of_area_that_is_Greenspace._2005
## 1 . 4.8
## 2 20.3 33.6
## 3 31.1 41.3
## 4 11.4 31.7
## 5 34.8 21.9
## 6 14.1 57.8
## Total_carbon_emissions_.2014. Household_Waste_Recycling_Rate._2014.15
## 1 1036 34.4
## 2 644 23.4
## 3 1415 38
## 4 975 54
## 5 1175 35.2
## 6 1180 48
## Number_of_cars._.2011_Census. Number_of_cars_per_household._.2011_Census.
## 1 1692 0.4
## 2 56966 0.8
## 3 144717 1.1
## 4 108507 1.2
## 5 87802 0.8
## 6 153908 1.2
## X._of_adults_who_cycle_at_least_once_per_month._2014.15
## 1 16.9
## 2 8.8
## 3 7.4
## 4 10.6
## 5 7.9
## 6 13
## Average_Public_Transport_Accessibility_score._2014
## 1 7.9
## 2 3
## 3 3
## 4 2.6
## 5 3.7
## 6 2.8
## Achievement_of_5_or_more_A.._C_grades_at_GCSE_or_equivalent_including_English_and_Maths._2013.14
## 1 78.6
## 2 58
## 3 67.3
## 4 60.3
## 5 60.1
## 6 68
## Rates_of_Children_Looked_After_.2016.
## 1 101
## 2 69
## 3 35
## 4 46
## 5 45
## 6 40
## X._of_pupils_whose_first_language_is_not_English_.2015.
## 1 .
## 2 41.7
## 3 46
## 4 32.6
## 5 37.6
## 6 38.9
## X._children_living_in_out.of.work_households_.2015.
## 1 7.9
## 2 18.7
## 3 9.3
## 4 12.6
## 5 13.7
## 6 10.2
## Male_life_expectancy._.2012.14. Female_life_expectancy._.2012.14.
## 1 . .
## 2 77.6 82.1
## 3 82.1 85.1
## 4 80.4 84.4
## 5 80.1 85.1
## 6 81.4 84.9
## Teenage_conception_rate_.2014. Life_satisfaction_score_2011.14_.out_of_10.
## 1 . 6.6
## 2 32.4 7.1
## 3 12.8 7.5
## 4 19.5 7.4
## 5 18.5 7.3
## 6 16.7 7.5
## Worthwhileness_score_2011.14_.out_of_10. Happiness_score_2011.14_.out_of_10.
## 1 7.1 6.0
## 2 7.6 7.1
## 3 7.8 7.4
## 4 7.7 7.2
## 5 7.4 7.2
## 6 7.9 7.4
## Anxiety_score_2011.14_.out_of_10. Childhood_Obesity_Prevalance_..._2015.16
## 1 5.6 n/a
## 2 3.1 28.5
## 3 2.8 20.7
## 4 3.3 22.7
## 5 2.9 24.3
## 6 3.3 16
## People_aged_17._with_diabetes_...
## 1 2.6
## 2 7.3
## 3 6
## 4 6.9
## 5 7.9
## 6 5.2
## Mortality_rate_from_causes_considered_preventable_2012.14
## 1 129
## 2 228
## 3 134
## 4 164
## 5 169
## 6 148
## Political_control_in_council
## 1 .
## 2 Lab
## 3 Cons
## 4 Cons
## 5 Lab
## 6 Cons
## Proportion_of_seats_won_by_Conservatives_in_2014_election
## 1 .
## 2 0
## 3 50.8
## 4 71.4
## 5 9.5
## 6 85
## Proportion_of_seats_won_by_Labour_in_2014_election
## 1 .
## 2 100
## 3 .
## 4 23.8
## 5 88.9
## 6 11.7
## Proportion_of_seats_won_by_Lib_Dems_in_2014_election
## 1 .
## 2 0
## 3 1.6
## 4 0
## 5 1.6
## 6 0
## Turnout_at_2014_local_elections
## 1 .
## 2 36.5
## 3 40.5
## 4 39.6
## 5 36.3
## 6 40.8reconfiguraremos el dataset eliminando los registros que no corresponden a distritos (hay filas de resumen) y restringiremos las filas a las que tienen datos sobre población y áreas.
london_profiles2 <- london_profiles %>%
filter(!(Area_name %in% c("Inner London", "Outer London", "London", "England", "United Kingdom"))) %>%
select(Area_name:Proportion_of_population_aged_65_and_over._2015) %>%
rename(name=Area_name)el rename servirá para asociar este dataset con el de las áreas
head(london_profiles2)## name Inner._Outer_London GLA_Population_Estimate_2017
## 1 City of London Inner London 8800
## 2 Barking and Dagenham Outer London 209000
## 3 Barnet Outer London 389600
## 4 Bexley Outer London 244300
## 5 Brent Outer London 332100
## 6 Bromley Outer London 327900
## GLA_Household_Estimate_2017 Inland_Area_.Hectares.
## 1 5326 290
## 2 78188 3,611
## 3 151423 8,675
## 4 97736 6,058
## 5 121048 4,323
## 6 140602 15,013
## Population_density_.per_hectare._2017 Average_Age._2017
## 1 30.3 43.2
## 2 57.9 32.9
## 3 44.9 37.3
## 4 40.3 39.0
## 5 76.8 35.6
## 6 21.8 40.2
## Proportion_of_population_aged_0.15._2015
## 1 11.4
## 2 27.2
## 3 21.1
## 4 20.6
## 5 20.9
## 6 19.9
## Proportion_of_population_of_working.age._2015
## 1 73.1
## 2 63.1
## 3 64.9
## 4 62.9
## 5 67.8
## 6 62.6
## Proportion_of_population_aged_65_and_over._2015
## 1 15.5
## 2 9.7
## 3 14.0
## 4 16.6
## 5 11.3
## 6 17.5Está listo para asociarlo con london_area, aunque una columna que debia ser numérica aparece como factor
london_profiles2 <- london_profiles2 %>%
mutate(GLA_Household_Estimate_2017=as.integer(GLA_Household_Estimate_2017),
Inland_Area_.Hectares.=as.numeric(Inland_Area_.Hectares.),
Population_density_.per_hectare._2017=as.numeric(Population_density_.per_hectare._2017))head(london_profiles2)## name Inner._Outer_London GLA_Population_Estimate_2017
## 1 City of London Inner London 8800
## 2 Barking and Dagenham Outer London 209000
## 3 Barnet Outer London 389600
## 4 Bexley Outer London 244300
## 5 Brent Outer London 332100
## 6 Bromley Outer London 327900
## GLA_Household_Estimate_2017 Inland_Area_.Hectares.
## 1 28 18
## 2 30 21
## 3 23 38
## 4 37 35
## 5 14 27
## 6 21 11
## Population_density_.per_hectare._2017 Average_Age._2017
## 1 15 43.2
## 2 29 32.9
## 3 21 37.3
## 4 17 39.0
## 5 33 35.6
## 6 12 40.2
## Proportion_of_population_aged_0.15._2015
## 1 11.4
## 2 27.2
## 3 21.1
## 4 20.6
## 5 20.9
## 6 19.9
## Proportion_of_population_of_working.age._2015
## 1 73.1
## 2 63.1
## 3 64.9
## 4 62.9
## 5 67.8
## 6 62.6
## Proportion_of_population_aged_65_and_over._2015
## 1 15.5
## 2 9.7
## 3 14.0
## 4 16.6
## 5 11.3
## 6 17.5No funcionón, las que están como factor se convierten a numéricas, pero con un número que no es el original posiblemente es el id del factor
Union de los dos datasets para ampliar la información de los distritos
london_area_full <- left_join(london_area, london_profiles2, by="name")head(london_area_full)## Simple feature collection with 6 features and 13 fields
## Geometry type: MULTIPOLYGON
## Dimension: XY
## Bounding box: xmin: -0.333999 ymin: 51.2895 xmax: 0.225322 ymax: 51.67036
## Geodetic CRS: WGS 84
## name cartodb_id created_at updated_at
## 1 Barking and Dagenham 1 2015-07-01 06:57:45 2015-07-01 06:57:45
## 2 Barnet 2 2015-07-01 06:57:45 2015-07-01 06:57:45
## 3 Bexley 3 2015-07-01 06:57:45 2015-07-01 06:57:45
## 4 Brent 4 2015-07-01 06:57:45 2015-07-01 06:57:45
## 5 Bromley 5 2015-07-01 06:57:45 2015-07-01 06:57:45
## 6 Camden 6 2015-07-01 06:57:45 2015-07-01 06:57:45
## Inner._Outer_London GLA_Population_Estimate_2017 GLA_Household_Estimate_2017
## 1 Outer London 209000 30
## 2 Outer London 389600 23
## 3 Outer London 244300 37
## 4 Outer London 332100 14
## 5 Outer London 327900 21
## 6 Inner London 242500 6
## Inland_Area_.Hectares. Population_density_.per_hectare._2017
## 1 21 29
## 2 38 21
## 3 35 17
## 4 27 33
## 5 11 12
## 6 14 4
## Average_Age._2017 Proportion_of_population_aged_0.15._2015
## 1 32.9 27.2
## 2 37.3 21.1
## 3 39.0 20.6
## 4 35.6 20.9
## 5 40.2 19.9
## 6 36.4 17.3
## Proportion_of_population_of_working.age._2015
## 1 63.1
## 2 64.9
## 3 62.9
## 4 67.8
## 5 62.6
## 6 71.0
## Proportion_of_population_aged_65_and_over._2015
## 1 9.7
## 2 14.0
## 3 16.6
## 4 11.3
## 5 17.5
## 6 11.7
## geometry
## 1 MULTIPOLYGON (((0.148209 51...
## 2 MULTIPOLYGON (((-0.183361 5...
## 3 MULTIPOLYGON (((0.158044 51...
## 4 MULTIPOLYGON (((-0.212138 5...
## 5 MULTIPOLYGON (((0.076463 51...
## 6 MULTIPOLYGON (((-0.140804 5...Intergración de dataset sobre escuelas en Londres con información geográfica de ubicación
all_schools_base <- read.csv("all_schools_xy_2016_lgp2.csv", sep=",", stringsAsFactors = TRUE)head(all_schools_base)## X OBJECTID URN SCHOOL_NAM
## 1 1 1 135155 Ayesha Siddiqa Girls School
## 2 2 2 140492 Beis Medrash Elyon
## 3 3 3 141411 Big Creative Independent School
## 4 4 4 142336 Wetherby Senior School
## 5 5 5 100042 St Mary's Kilburn Church of England Primary School
## 6 6 6 100224 De Beauvoir Primary School
## TYPE PHASE ADDRESS TOWN
## 1 Other Independent School Not applicable 165-169 The Broadway Southall
## 2 Other Independent School Not applicable 233 West Hendon Broadway London
## 3 Other Independent School Not applicable Silver Birch House Walthamstow
## 4 Other Independent School Not applicable 100 Marylebone Lane London
## 5 Voluntary Aided School Primary Quex Road London
## 6 Community School Primary 80 Tottenham Road London
## POSTCODE STATUS GENDER EASTING NORTHING WARD_NAME
## 1 UB1 1LR Open Girls 521263 180470 Southall Broadway
## 2 NW9 7DG Open Boys 521939 188148 West Hendon
## 3 E17 5SD Open Mixed 535764 190188 Higham Hill
## 4 W1U 2QB Open Boys 528432 181474 Marylebone High Street
## 5 NW6 4PG Open Mixed 525453 183984 Kilburn
## 6 N1 4BS Open Mixed 533252 184710 De Beauvoir
## LSOA_NAME LA_NAME WEBLINK
## 1 Ealing 026C Ealing
## 2 Barnet 036F Barnet
## 3 Waltham Forest 014C Waltham Forest
## 4 Westminster 011B Westminster
## 5 Camden 020C Camden http://www.stmarykilburn.camden.sch.uk/
## 6 Hackney 021E Hackney www.debeauvoir.hackney.sch.uk/
## AGE map_icon NEW_URN OLD_URN map_icon_l Primary x y
## 1 19-nov NA NA 2 0 -0.3784960 51.5075
## 2 16-nov NA NA 2 0 -0.2416280 51.5790
## 3 15 - 16 NA NA 2 0 -0.0425897 51.5940
## 4 16-nov NA NA 2 0 -0.1504090 51.5176
## 5 11-mar VOLUNTARY NA NA 2 1 -0.1933670 51.5404
## 6 11-mar STATE-FUNDED NA NA 2 1 -0.0769998 51.5453
## y.Type
## 1 515074997
## 2 515,789,986
## 3 515,940,018
## 4 51,517,601
## 5 515,404,015
## 6 515,452,995varias de las columnas del dataset no son significativas para el proceso que llevaremos adelante, por lo cual simplificaremos el data set seleccionando solo algunas
all_schools <- all_schools_base %>%
select(OBJECTID, SCHOOL_NAM, TYPE, PHASE, ADDRESS, TOWN, STATUS, GENDER, x, y) %>%
filter(!is.na(y))head(all_schools)## OBJECTID SCHOOL_NAM
## 1 1 Ayesha Siddiqa Girls School
## 2 2 Beis Medrash Elyon
## 3 3 Big Creative Independent School
## 4 4 Wetherby Senior School
## 5 5 St Mary's Kilburn Church of England Primary School
## 6 6 De Beauvoir Primary School
## TYPE PHASE ADDRESS TOWN
## 1 Other Independent School Not applicable 165-169 The Broadway Southall
## 2 Other Independent School Not applicable 233 West Hendon Broadway London
## 3 Other Independent School Not applicable Silver Birch House Walthamstow
## 4 Other Independent School Not applicable 100 Marylebone Lane London
## 5 Voluntary Aided School Primary Quex Road London
## 6 Community School Primary 80 Tottenham Road London
## STATUS GENDER x y
## 1 Open Girls -0.3784960 51.5075
## 2 Open Boys -0.2416280 51.5790
## 3 Open Mixed -0.0425897 51.5940
## 4 Open Boys -0.1504090 51.5176
## 5 Open Mixed -0.1933670 51.5404
## 6 Open Mixed -0.0769998 51.5453Primer esquema
ggplot(all_schools)+
geom_point(aes(x=x, y=y))
superponemos el mapa con los puntos
ggplot()+
geom_sf(data=london_area_full, fill="deepskyblue", color="white")+
geom_point(data=all_schools, color="blue", aes(x=x, y=y))
Vemos mayor densidad de escuelas en el Inner London, la parte central, y algunas fuera de los límites de Londres. para poder identificarlas y procesar en función del distrito al que pertenecen necesitamos asociar ambos datasets
all_schools_geo <- st_as_sf(all_schools, coords = c("x","y"), crs = 4326)
#transforma a formato geométrico, head(all_schools_geo)## Simple feature collection with 6 features and 8 fields
## Geometry type: POINT
## Dimension: XY
## Bounding box: xmin: -0.378496 ymin: 51.5075 xmax: -0.0425897 ymax: 51.594
## Geodetic CRS: WGS 84
## OBJECTID SCHOOL_NAM
## 1 1 Ayesha Siddiqa Girls School
## 2 2 Beis Medrash Elyon
## 3 3 Big Creative Independent School
## 4 4 Wetherby Senior School
## 5 5 St Mary's Kilburn Church of England Primary School
## 6 6 De Beauvoir Primary School
## TYPE PHASE ADDRESS TOWN
## 1 Other Independent School Not applicable 165-169 The Broadway Southall
## 2 Other Independent School Not applicable 233 West Hendon Broadway London
## 3 Other Independent School Not applicable Silver Birch House Walthamstow
## 4 Other Independent School Not applicable 100 Marylebone Lane London
## 5 Voluntary Aided School Primary Quex Road London
## 6 Community School Primary 80 Tottenham Road London
## STATUS GENDER geometry
## 1 Open Girls POINT (-0.378496 51.5075)
## 2 Open Boys POINT (-0.241628 51.579)
## 3 Open Mixed POINT (-0.0425897 51.594)
## 4 Open Boys POINT (-0.150409 51.5176)
## 5 Open Mixed POINT (-0.193367 51.5404)
## 6 Open Mixed POINT (-0.0769998 51.5453)combinamos ambos datasets
all_schools_geo <- st_join(all_schools_geo, london_area_full)## although coordinates are longitude/latitude, st_intersects assumes that they are planar
## although coordinates are longitude/latitude, st_intersects assumes that they are planarhead(all_schools_geo)## Simple feature collection with 6 features and 21 fields
## Geometry type: POINT
## Dimension: XY
## Bounding box: xmin: -0.378496 ymin: 51.5075 xmax: -0.0425897 ymax: 51.594
## Geodetic CRS: WGS 84
## OBJECTID SCHOOL_NAM
## 1 1 Ayesha Siddiqa Girls School
## 2 2 Beis Medrash Elyon
## 3 3 Big Creative Independent School
## 4 4 Wetherby Senior School
## 5 5 St Mary's Kilburn Church of England Primary School
## 6 6 De Beauvoir Primary School
## TYPE PHASE ADDRESS TOWN
## 1 Other Independent School Not applicable 165-169 The Broadway Southall
## 2 Other Independent School Not applicable 233 West Hendon Broadway London
## 3 Other Independent School Not applicable Silver Birch House Walthamstow
## 4 Other Independent School Not applicable 100 Marylebone Lane London
## 5 Voluntary Aided School Primary Quex Road London
## 6 Community School Primary 80 Tottenham Road London
## STATUS GENDER name cartodb_id created_at
## 1 Open Girls Ealing 9 2015-07-01 06:57:45
## 2 Open Boys Barnet 2 2015-07-01 06:57:45
## 3 Open Mixed Waltham Forest 31 2015-07-01 06:57:45
## 4 Open Boys Westminster 33 2015-07-01 06:57:45
## 5 Open Mixed Camden 6 2015-07-01 06:57:45
## 6 Open Mixed Hackney 12 2015-07-01 06:57:45
## updated_at Inner._Outer_London GLA_Population_Estimate_2017
## 1 2015-07-01 06:57:45 Outer London 351600
## 2 2015-07-01 06:57:45 Outer London 389600
## 3 2015-07-01 06:57:45 Outer London 276200
## 4 2015-07-01 06:57:45 Inner London 242100
## 5 2015-07-01 06:57:45 Inner London 242500
## 6 2015-07-01 06:57:45 Inner London 274300
## GLA_Household_Estimate_2017 Inland_Area_.Hectares.
## 1 18 31
## 2 23 38
## 3 5 25
## 4 12 13
## 5 6 14
## 6 10 5
## Population_density_.per_hectare._2017 Average_Age._2017
## 1 31 36.2
## 2 21 37.3
## 3 32 35.1
## 4 5 37.7
## 5 4 36.4
## 6 9 33.1
## Proportion_of_population_aged_0.15._2015
## 1 21.4
## 2 21.1
## 3 21.8
## 4 15.9
## 5 17.3
## 6 20.7
## Proportion_of_population_of_working.age._2015
## 1 66.8
## 2 64.9
## 3 67.9
## 4 72.3
## 5 71.0
## 6 72.1
## Proportion_of_population_aged_65_and_over._2015 geometry
## 1 11.8 POINT (-0.378496 51.5075)
## 2 14.0 POINT (-0.241628 51.579)
## 3 10.3 POINT (-0.0425897 51.594)
## 4 11.7 POINT (-0.150409 51.5176)
## 5 11.7 POINT (-0.193367 51.5404)
## 6 7.2 POINT (-0.0769998 51.5453)Debemos eliminar las escuelas que quedan fuera de el área de Londres
all_schools_geo <- filter(all_schools_geo, !is.na(name))head(all_schools_geo)## Simple feature collection with 6 features and 21 fields
## Geometry type: POINT
## Dimension: XY
## Bounding box: xmin: -0.378496 ymin: 51.5075 xmax: -0.0425897 ymax: 51.594
## Geodetic CRS: WGS 84
## OBJECTID SCHOOL_NAM
## 1 1 Ayesha Siddiqa Girls School
## 2 2 Beis Medrash Elyon
## 3 3 Big Creative Independent School
## 4 4 Wetherby Senior School
## 5 5 St Mary's Kilburn Church of England Primary School
## 6 6 De Beauvoir Primary School
## TYPE PHASE ADDRESS TOWN
## 1 Other Independent School Not applicable 165-169 The Broadway Southall
## 2 Other Independent School Not applicable 233 West Hendon Broadway London
## 3 Other Independent School Not applicable Silver Birch House Walthamstow
## 4 Other Independent School Not applicable 100 Marylebone Lane London
## 5 Voluntary Aided School Primary Quex Road London
## 6 Community School Primary 80 Tottenham Road London
## STATUS GENDER name cartodb_id created_at
## 1 Open Girls Ealing 9 2015-07-01 06:57:45
## 2 Open Boys Barnet 2 2015-07-01 06:57:45
## 3 Open Mixed Waltham Forest 31 2015-07-01 06:57:45
## 4 Open Boys Westminster 33 2015-07-01 06:57:45
## 5 Open Mixed Camden 6 2015-07-01 06:57:45
## 6 Open Mixed Hackney 12 2015-07-01 06:57:45
## updated_at Inner._Outer_London GLA_Population_Estimate_2017
## 1 2015-07-01 06:57:45 Outer London 351600
## 2 2015-07-01 06:57:45 Outer London 389600
## 3 2015-07-01 06:57:45 Outer London 276200
## 4 2015-07-01 06:57:45 Inner London 242100
## 5 2015-07-01 06:57:45 Inner London 242500
## 6 2015-07-01 06:57:45 Inner London 274300
## GLA_Household_Estimate_2017 Inland_Area_.Hectares.
## 1 18 31
## 2 23 38
## 3 5 25
## 4 12 13
## 5 6 14
## 6 10 5
## Population_density_.per_hectare._2017 Average_Age._2017
## 1 31 36.2
## 2 21 37.3
## 3 32 35.1
## 4 5 37.7
## 5 4 36.4
## 6 9 33.1
## Proportion_of_population_aged_0.15._2015
## 1 21.4
## 2 21.1
## 3 21.8
## 4 15.9
## 5 17.3
## 6 20.7
## Proportion_of_population_of_working.age._2015
## 1 66.8
## 2 64.9
## 3 67.9
## 4 72.3
## 5 71.0
## 6 72.1
## Proportion_of_population_aged_65_and_over._2015 geometry
## 1 11.8 POINT (-0.378496 51.5075)
## 2 14.0 POINT (-0.241628 51.579)
## 3 10.3 POINT (-0.0425897 51.594)
## 4 11.7 POINT (-0.150409 51.5176)
## 5 11.7 POINT (-0.193367 51.5404)
## 6 7.2 POINT (-0.0769998 51.5453)Podemos agrupar y sumarizar para obtener información de la cantidad de escuelas por distrito
all_schools_geo_res <- all_schools_geo %>%
group_by(name) %>%
summarize(cantidad=n())summary(all_schools_geo_res)## name cantidad geometry
## Barking and Dagenham: 1 Min. : 5.00 MULTIPOINT :33
## Barnet : 1 1st Qu.: 73.00 epsg:4326 : 0
## Bexley : 1 Median : 87.00 +proj=long...: 0
## Brent : 1 Mean : 85.12
## Bromley : 1 3rd Qu.:100.00
## Camden : 1 Max. :148.00
## (Other) :27Ahora reconvertimos a all_schools_geo_res en un dataset no geométrico
all_schools_geo_res <- all_schools_geo_res %>%
st_set_geometry(NULL)head(all_schools_geo_res)## # A tibble: 6 x 2
## name cantidad
## <fct> <int>
## 1 Barking and Dagenham 53
## 2 Barnet 148
## 3 Bexley 75
## 4 Brent 92
## 5 Bromley 114
## 6 Camden 92podemos ahora rearmar london_area_full con la información de cantidad de escuelas que nos da all_schools_geo_res
london_area_full <- left_join(london_area_full, all_schools_geo_res, by="name")head(london_area_full)## Simple feature collection with 6 features and 14 fields
## Geometry type: MULTIPOLYGON
## Dimension: XY
## Bounding box: xmin: -0.333999 ymin: 51.2895 xmax: 0.225322 ymax: 51.67036
## Geodetic CRS: WGS 84
## name cartodb_id created_at updated_at
## 1 Barking and Dagenham 1 2015-07-01 06:57:45 2015-07-01 06:57:45
## 2 Barnet 2 2015-07-01 06:57:45 2015-07-01 06:57:45
## 3 Bexley 3 2015-07-01 06:57:45 2015-07-01 06:57:45
## 4 Brent 4 2015-07-01 06:57:45 2015-07-01 06:57:45
## 5 Bromley 5 2015-07-01 06:57:45 2015-07-01 06:57:45
## 6 Camden 6 2015-07-01 06:57:45 2015-07-01 06:57:45
## Inner._Outer_London GLA_Population_Estimate_2017 GLA_Household_Estimate_2017
## 1 Outer London 209000 30
## 2 Outer London 389600 23
## 3 Outer London 244300 37
## 4 Outer London 332100 14
## 5 Outer London 327900 21
## 6 Inner London 242500 6
## Inland_Area_.Hectares. Population_density_.per_hectare._2017
## 1 21 29
## 2 38 21
## 3 35 17
## 4 27 33
## 5 11 12
## 6 14 4
## Average_Age._2017 Proportion_of_population_aged_0.15._2015
## 1 32.9 27.2
## 2 37.3 21.1
## 3 39.0 20.6
## 4 35.6 20.9
## 5 40.2 19.9
## 6 36.4 17.3
## Proportion_of_population_of_working.age._2015
## 1 63.1
## 2 64.9
## 3 62.9
## 4 67.8
## 5 62.6
## 6 71.0
## Proportion_of_population_aged_65_and_over._2015 cantidad
## 1 9.7 53
## 2 14.0 148
## 3 16.6 75
## 4 11.3 92
## 5 17.5 114
## 6 11.7 92
## geometry
## 1 MULTIPOLYGON (((0.148209 51...
## 2 MULTIPOLYGON (((-0.183361 5...
## 3 MULTIPOLYGON (((0.158044 51...
## 4 MULTIPOLYGON (((-0.212138 5...
## 5 MULTIPOLYGON (((0.076463 51...
## 6 MULTIPOLYGON (((-0.140804 5...representamos
ggplot(london_area_full)+
geom_sf(aes(fill=cantidad))
A pedido de Paula, …y porque tenía que aprender a hacerlo..
ggplot(london_area_full)+
geom_sf(aes(fill=GLA_Population_Estimate_2017/cantidad))
Teniendo en cuenta la población en edad escolar
veamos si cambia considerando la población con edades bajo los 15 años
london_area_full <- london_area_full %>%
mutate(pop_u15=GLA_Population_Estimate_2017*(Proportion_of_population_aged_0.15._2015/100))head(london_area_full)## Simple feature collection with 6 features and 15 fields
## Geometry type: MULTIPOLYGON
## Dimension: XY
## Bounding box: xmin: -0.333999 ymin: 51.2895 xmax: 0.225322 ymax: 51.67036
## Geodetic CRS: WGS 84
## name cartodb_id created_at updated_at
## 1 Barking and Dagenham 1 2015-07-01 06:57:45 2015-07-01 06:57:45
## 2 Barnet 2 2015-07-01 06:57:45 2015-07-01 06:57:45
## 3 Bexley 3 2015-07-01 06:57:45 2015-07-01 06:57:45
## 4 Brent 4 2015-07-01 06:57:45 2015-07-01 06:57:45
## 5 Bromley 5 2015-07-01 06:57:45 2015-07-01 06:57:45
## 6 Camden 6 2015-07-01 06:57:45 2015-07-01 06:57:45
## Inner._Outer_London GLA_Population_Estimate_2017 GLA_Household_Estimate_2017
## 1 Outer London 209000 30
## 2 Outer London 389600 23
## 3 Outer London 244300 37
## 4 Outer London 332100 14
## 5 Outer London 327900 21
## 6 Inner London 242500 6
## Inland_Area_.Hectares. Population_density_.per_hectare._2017
## 1 21 29
## 2 38 21
## 3 35 17
## 4 27 33
## 5 11 12
## 6 14 4
## Average_Age._2017 Proportion_of_population_aged_0.15._2015
## 1 32.9 27.2
## 2 37.3 21.1
## 3 39.0 20.6
## 4 35.6 20.9
## 5 40.2 19.9
## 6 36.4 17.3
## Proportion_of_population_of_working.age._2015
## 1 63.1
## 2 64.9
## 3 62.9
## 4 67.8
## 5 62.6
## 6 71.0
## Proportion_of_population_aged_65_and_over._2015 cantidad
## 1 9.7 53
## 2 14.0 148
## 3 16.6 75
## 4 11.3 92
## 5 17.5 114
## 6 11.7 92
## geometry pop_u15
## 1 MULTIPOLYGON (((0.148209 51... 56848.0
## 2 MULTIPOLYGON (((-0.183361 5... 82205.6
## 3 MULTIPOLYGON (((0.158044 51... 50325.8
## 4 MULTIPOLYGON (((-0.212138 5... 69408.9
## 5 MULTIPOLYGON (((0.076463 51... 65252.1
## 6 MULTIPOLYGON (((-0.140804 5... 41952.5ggplot(london_area_full)+
geom_sf(aes(fill=pop_u15/cantidad))+
labs(title="Mapa de escuelas de Londres",subtitle="población menor de 15 años x escuela",x="longitud",y="latitud")
Ahora se revelan ciertos patrones, grandes zonas con resultados similares. Si ponemos el foco en distritos en particular; la city de Londres aparece como el más favorecido, mientras que Kensington & Chelsea, y Hammersmith & Fulham (barrios residenciales y de altos ingresos) tambien tienen alto numero de escuelas por habitante menor de 15 años, el más desfavorecido es claramente Barking & Dagenham, un distrito que parece haber tenido una alta tasa de inmigración en las últimas décadas.
El dataset london-borough-profiles.csv contiene información de porcentajes de inmigrantes, pero esta información la eliminamos no seleccionando esas columnas, quizás valga la pena recuperarlas…
Incluir un mapa de fondo
#install.packages(ggmap)
library(ggmap)## Google's Terms of Service: https://cloud.google.com/maps-platform/terms/.## Please cite ggmap if you use it! See citation("ggmap") for details.bbox_london <- as.numeric(st_bbox(london_area_full))mapa_london <- get_stamenmap(bbox = bbox_london,
maptype = "terrain",
zoom=10)## Source : http://tile.stamen.com/terrain/10/510/339.png## Source : http://tile.stamen.com/terrain/10/511/339.png## Source : http://tile.stamen.com/terrain/10/512/339.png## Source : http://tile.stamen.com/terrain/10/510/340.png## Source : http://tile.stamen.com/terrain/10/511/340.png## Source : http://tile.stamen.com/terrain/10/512/340.png## Source : http://tile.stamen.com/terrain/10/510/341.png## Source : http://tile.stamen.com/terrain/10/511/341.png## Source : http://tile.stamen.com/terrain/10/512/341.pngggmap(mapa_london)
ggmap(mapa_london)+
geom_sf(data=london_area_full, aes(fill=cantidad), alpha=0.75, inherit.aes = FALSE)+
labs(title="Mapa Escuelas de Londres",
subtitle="cantidad por distrito",
x="longitud",
y="latitud")## Coordinate system already present. Adding new coordinate system, which will replace the existing one.