Here is first 5 row each datatable.
dt1 <-
data.table(
places <- c("Harlem","East Village","Hell's Kitchen","Capitol Hill","Dupont Circle","Harlem"),
zipcodes <- c("10030", "11354", "10019", "98102", "20037","10043")
)
setnames(dt1,old = c("V1","V2"),new= c("Neigbourhood","Zipcode"))
for dt2 You understand it correctly I have one column for each city and places (neighbourhoods) underthem like this (first 2 columns and 5 rows there are many places neighbourhoods ofc but i didnt type them all for now)
dt2 <-
data.table(
city1 <- c("NewYork", "Harlem","East Village","Hell's Kitchen","Polo Grounds","Upper Eastside"),
city2 <- c("Washington DC","Capitol Hill","Dupont Circle","Navy Yard","Anacostia","Foggy Bottom"))
colnames(dt2) <- as.character(dt2[1, ])
dt2 <- dt2[-1,]
as you can see I have Harlem, Hell's Kitchen ... Capitol Hill in dt1 this values are repeating because they are huge areas and have different zipcodes i mean Hell's kitchen has 2 zipcodes harlem has over 10 zipcodes thats why I need to match Neighbourhood names and Cities.
I want to have something like this in the end.
dt3 <-
data.table(
places <- c("Harlem","East Village","Hell's Kitchen","Capitol Hill","Dupont Circle","Harlem"),
zipcodes <- c("10030", "11354", "10019", "98102", "20037","10043"),
city <- c("NY","NY","NY","DC","DC","NY")
)
setnames(dt3,old = c("V1","V2","V3"),new= c("Neigbourhood","Zipcode","City"))
When it sees Harlem in dt1 should look up to dt2 and if there is a match it should paste that entry to dt1$City column.
English is not my native and not that familiar with the R sorry if i couldnt express myself. Have a nice day.