Assign new column of a dataframe to values matching other dataframe

Question

I have two data frames:

geodata<-read.csv(text='postalcode;latitude;longitude
7000;47.8415;16.5041
7000;47.8921;16.4011
7000;47.9400;16.5032
7071;47.8012;16.671
2443;47.9156;16.5179', sep=';', header=TRUE)

addresses<-read.csv(text='postalcode;address
2400;Lorem ipsum
7000;Dolor sit amet
2443;Consetetur sadipscing elitr
7000;Sed diam nonumy', sep=';', header=TRUE)

What I would need is two additional columns in the addresses dataframe with the corresponding (first match when multiple postalcodes rows exist) geodata$latitude and geodata$longitude values.

How to join (merge) data frames (inner, outer, left, right)? shows nearly what I'm looking for except that I don't want duplicated lines in the merged dataframe.

I tried something like

# code not working... #
addresses$latitude<-geodata[addresses$postalcode==geodata$postalcode]

which I know is really ugly :-(

agstudy · Answer 1 · 2015-08-27T10:02:19.440

6

NO NEED to use extra packages here . Simply merge:

merge(geodata,addresses,all.x=TRUE)

#   postalcode latitude longitude                     address
# 1       2443  47.9156   16.5179 Consetetur sadipscing elitr
# 2       7000  47.8415   16.5041              Dolor sit amet
# 3       7000  47.8415   16.5041             Sed diam nonumy
# 4       7071  47.8012   16.6710                        <NA>

EDIT:

to add geodata to address we should invert the order here :

merge(addresses,geodata,all.x=TRUE)
  postalcode                     address latitude longitude
1       2400                 Lorem ipsum       NA        NA
2       2443 Consetetur sadipscing elitr  47.9156   16.5179
3       7000              Dolor sit amet  47.8415   16.5041
4       7000             Sed diam nonumy  47.8415   16.5041

edited Aug 27 '15 at 10:02

answered Aug 27 '15 at 09:47

agstudy

119,832
17
199
261

2

...with `all.x=TRUE` I think. Poor old `merge`, overlooked by all the shiny new tools :-) – thelatemail Aug 27 '15 at 09:47
@agstudy - switch addresses and geodata around too I think, so the `x` references the longer file. – thelatemail Aug 27 '15 at 09:50
You are losing one address in this solution. Postalcode 2400, address Lorem ipsum – erasmortg Aug 27 '15 at 09:55
1

erasmortg: loosing addresses w/o geodata is what I'd expect. My goal is to plot the data as a map. – Johann Horvat Aug 27 '15 at 10:02

erasmortg · Accepted Answer · 2015-08-27T11:15:11.623

Perhaps with a left_join from dplyr:

library(dplyr)
left_join(addresses,geodata, fill=NA)
#Joining by: "postalcode"
#  postalcode                     address latitude longitude
#1       2400                 Lorem ipsum       NA        NA
#2       7000              Dolor sit amet  47.8415   16.5041
#3       2443 Consetetur sadipscing elitr  47.9156   16.5179
#4       7000             Sed diam nonumy  47.8415   16.5041

Edit, if there is more than one result per postal code, you could try subsetting geodata:

#left_join(addresses, subset(geodata, !duplicated(postalcode)), fill=NA)
#Joining by: "postalcode"
#  postalcode                     address latitude longitude
#1       2400                 Lorem ipsum       NA        NA
#2       7000              Dolor sit amet  47.8415   16.5041
#3       2443 Consetetur sadipscing elitr  47.9156   16.5179
#4       7000             Sed diam nonumy  47.8415   16.5041

Thanks, this works nearly as expected. I edited my post: the problem is, that there may be multiple lat/loc values per postal code. What I would need is the first matching line within the geodata datafame. — Johann Horvat, Aug 27 '15 at 09:54

score 2 · Answer 3 · answered Aug 27 '15 at 09:42

2

You could join them (in plyr)

library(plyr)
addresses <- join(addresses,geodata)

answered Aug 27 '15 at 09:42

Wannes Rosiers

1,680
1
12
18

Assign new column of a dataframe to values matching other dataframe

3 Answers3

EDIT: