Problem merging csv onto shape file

[olivert5]

Hi everyone - firstly, please excuse any amateurish language. I am an amateur.

I am trying to merge a .csv file onto a shapefile in R. Essentially I want to create a map of London showing crime rates at the ward level for a research project. To do this, I have a MSOA map downloaded from London Datastore (I have used this file before and it has always worked fine) and a manipulated crime rate csv file, with two columns, one with the ward code (matches to the shapefile) and one with the number of crimes. I'm only able to put one image in so I have only got a screenshot of the merged data below!

When I merge together using the code below, the files merge fine, but I lose all the data in the crime column, and it just comes up as a list of 'NA' rather than the numbers. I have tried changing the column type from double to integer to numeric etc. to see if this would help and it didn't. I also tried opening up the file in a new directory to check it wasn't just a bug, and this didn't help either. As I say, I have done very similar operations before with this shapefile, and have never had this issue come up.

Here's the code I used to open the files and merge together:

library(rgdal)
library(rgeos)
library(tmap)
library(sp)
library(dplyr)
##Unecessary packages are for use later in the project

London_OAs <- readOGR(".", "MSOA_2011_London_gen_MHW")
London_OAs <- spTransform(London_OAs, CRS("+init=EPSG:27700"))
crime_data <- read.csv(crime_data)

crime_merged <- merge(London_OAs, crime_data, by.x="MSOA11CD", by.y="WardCode")

Here's the screenshot of the shapefile after the merge, with the row of NAs next to the crimes column.

If anyone knows why this is going wrong please send help! Thank you so much in advance

[olivert5]

EDIT: I just managed to merge the shapefile with some health data - and it worked fine. Now even more confused why it is rejecting the crime data.

[technocrat]

Let shp be your MSOA shapefile, coerced into a dataframe with an area identifier and the boundries. Let dat be your csv file imported into a dataframe with an area identifier corresponding. Then

library(dplyr)
combined <- left.join(dat,shp,by="area")

Consider the {sf} library, which makes working with geo data much easier overall.

library(sf)
#> Linking to GEOS 3.9.0, GDAL 3.2.2, PROJ 7.2.1; sf_use_s2() is TRUE
# substitute the name of your file
fname <- system.file("shape/nc.shp", package="sf")
fname
#> [1] "/home/roc/R/x86_64-pc-linux-gnu-library/4.1/sf/shape/nc.shp"
nc <- st_read(fname)
#> Reading layer `nc' from data source 
#>   `/home/roc/R/x86_64-pc-linux-gnu-library/4.1/sf/shape/nc.shp' 
#>   using driver `ESRI Shapefile'
#> Simple feature collection with 100 features and 14 fields
#> Geometry type: MULTIPOLYGON
#> Dimension:     XY
#> Bounding box:  xmin: -84.32385 ymin: 33.88199 xmax: -75.45698 ymax: 36.58965
#> Geodetic CRS:  NAD27
nc
#> Simple feature collection with 100 features and 14 fields
#> Geometry type: MULTIPOLYGON
#> Dimension:     XY
#> Bounding box:  xmin: -84.32385 ymin: 33.88199 xmax: -75.45698 ymax: 36.58965
#> Geodetic CRS:  NAD27
#> First 10 features:
#>     AREA PERIMETER CNTY_ CNTY_ID        NAME  FIPS FIPSNO CRESS_ID BIR74 SID74
#> 1  0.114     1.442  1825    1825        Ashe 37009  37009        5  1091     1
#> 2  0.061     1.231  1827    1827   Alleghany 37005  37005        3   487     0
#> 3  0.143     1.630  1828    1828       Surry 37171  37171       86  3188     5
#> 4  0.070     2.968  1831    1831   Currituck 37053  37053       27   508     1
#> 5  0.153     2.206  1832    1832 Northampton 37131  37131       66  1421     9
#> 6  0.097     1.670  1833    1833    Hertford 37091  37091       46  1452     7
#> 7  0.062     1.547  1834    1834      Camden 37029  37029       15   286     0
#> 8  0.091     1.284  1835    1835       Gates 37073  37073       37   420     0
#> 9  0.118     1.421  1836    1836      Warren 37185  37185       93   968     4
#> 10 0.124     1.428  1837    1837      Stokes 37169  37169       85  1612     1
#>    NWBIR74 BIR79 SID79 NWBIR79                       geometry
#> 1       10  1364     0      19 MULTIPOLYGON (((-81.47276 3...
#> 2       10   542     3      12 MULTIPOLYGON (((-81.23989 3...
#> 3      208  3616     6     260 MULTIPOLYGON (((-80.45634 3...
#> 4      123   830     2     145 MULTIPOLYGON (((-76.00897 3...
#> 5     1066  1606     3    1197 MULTIPOLYGON (((-77.21767 3...
#> 6      954  1838     5    1237 MULTIPOLYGON (((-76.74506 3...
#> 7      115   350     2     139 MULTIPOLYGON (((-76.00897 3...
#> 8      254   594     2     371 MULTIPOLYGON (((-76.56251 3...
#> 9      748  1190     2     844 MULTIPOLYGON (((-78.30876 3...
#> 10     160  2038     5     176 MULTIPOLYGON (((-80.02567 3...