Join on two separate columns in a data.frame with a simple code

Question

I need a simple code to join on two separate columns from the same key data frame.

My first data.frame looks like this:

> head(data)
   bikeid end.station.id start.station.id diff.time            stoptime           starttime
4   15941            259              315       564 2014-09-02 17:59:30 2014-09-02 18:08:54
8   15941            229              450      2616 2014-09-09 09:28:39 2014-09-09 10:12:15
9   15941            477              465      3223 2014-09-09 15:59:23 2014-09-09 16:53:06
10  15941            319              147       570 2014-09-09 18:55:44 2014-09-09 19:05:14
14  15941           3002              304      3208 2014-09-12 14:54:10 2014-09-12 15:47:38
19  15941            469              267      2514 2014-09-25 16:13:24 2014-09-25 16:55:18
               midtime
4  2014-09-02 18:04:12
8  2014-09-09 09:50:27
9  2014-09-09 16:26:14
10 2014-09-09 19:00:29
14 2014-09-12 15:20:54
19 2014-09-25 16:34:21

I need to join end.station.id and start.station.id on citibike_station_id in the data.frame stations


> head(stations)
  id citibike_station_id latitude longitude                         label
1  1                  72 40.76727 -73.99393              W 52 St & 11 Ave
2  2                  79 40.71912 -74.00667      Franklin St & W Broadway
3  3                  82 40.71117 -74.00017        St James Pl & Pearl St
4  4                  83 40.68383 -73.97632 Atlantic Ave & Fort Greene Pl
5  5                 116 40.74178 -74.00150               W 17 St & 8 Ave
6  6                 119 40.69609 -73.97803      Park Ave & St Edwards St

So that the end result is a data frame that looks like this, obviously with no zeros.

> head(data)
   bikeid end.station.id start.station.id diff.time            stoptime           starttime
4   15941            259              315       564 2014-09-02 17:59:30 2014-09-02 18:08:54
8   15941            229              450      2616 2014-09-09 09:28:39 2014-09-09 10:12:15
9   15941            477              465      3223 2014-09-09 15:59:23 2014-09-09 16:53:06
10  15941            319              147       570 2014-09-09 18:55:44 2014-09-09 19:05:14
14  15941           3002              304      3208 2014-09-12 14:54:10 2014-09-12 15:47:38
19  15941            469              267      2514 2014-09-25 16:13:24 2014-09-25 16:55:18
               midtime end.station.lat end.station.lon end.station.name start.station.lat
4  2014-09-02 18:04:12               0               0                0                 0
8  2014-09-09 09:50:27               0               0                0                 0
9  2014-09-09 16:26:14               0               0                0                 0
10 2014-09-09 19:00:29               0               0                0                 0
14 2014-09-12 15:20:54               0               0                0                 0
19 2014-09-25 16:34:21               0               0                0                 0
   start.station.lon start.station.name
4                  0                  0
8                  0                  0
9                  0                  0
10                 0                  0
14                 0                  0
19                 0                  0

example is not reproducible, read how to ask R question on SO — jangorecki

Mist Mist · Accepted Answer · 2016-01-12T03:14:28

Try this:

data <- data.frame(end.station.id = c(1,2,3), start.station.id = c(3,1,2))
stations <- data.frame(citibike_station_id = c(1,2,3), label = c("One", "Two", "Three"))
data <- merge(data, stations, by.x = "start.station.id", by.y = "citibike_station_id", all.x = TRUE)
data <- merge(data, stations, by.x = "end.station.id",   by.y = "citibike_station_id", all.x = TRUE)
names(data)[3:4] <- c("start", "end")

Join on two separate columns in a data.frame with a simple code

1 Answers