R, Merge Data Sets Changing Key Values

Question

I am attempting to merge the following value data set, which is kind of dimensionalized. The variable here is

activity_labels <- 

activity_id      activity_name
1                WALKING
2                WALKING_UPSTAIRS
3                WALKING_DOWNSTAIRS
4                SITTING
5                STANDING
6                LAYING

with another data set that looks like the following.:

activities_raw <- 

activity_id
5
5
3
3
4
2

What I would expect to see is:

activity_id      activity_name
    5                STANDING
    5                STANDING
    3                WALKING_DOWNSTAIRS
    3                WALKING_DOWNSTAIRS
    4                SITTING
    2                WALKING_UPSTAIRS

However, what I see is something completely different. In fact, it looks like the merge that I'm doing is changing the values of activities_raw, which is my record level data. I see this:

activity_id      activity_name
    1                WALKING
    1                WALKING
    1                WALKING
    1                WALKING
    1                WALKING
    1                WALKING

I've attempt inner join, left join, right join, etc. Here is an example of my inner join:

merge(activities_raw , activity_labels , by="activity_id")

Any help would be greatly appreciated.

have you tried `merge(activities_raw, activity_labels, by = "activity_id", all.y = TRUE)`? — KenHBS, Aug 16 '16 at 18:03
On the surface, that should work. It'd be helpful to have samples of the actual data sets to probe for a deeper problem. Could you do `dput(head(activity_labels))` and `dput(head(activities_raw))` and post the resulting outputs? This would make it possible to work with some of your data directly. — jdobres, Aug 16 '16 at 18:04

score 0 · Answer 1 · answered Aug 16 '16 at 18:07

merge works fine here :

merge(xx2,xx1,sort=FALSE)

#   activity_id      activity_name
# 1           5           STANDING
# 2           5           STANDING
# 3           3 WALKING_DOWNSTAIRS
# 4           3 WALKING_DOWNSTAIRS
# 5           4            SITTING
# 6           2   WALKING_UPSTAIRS

Where data are :

xx1 <- read.table(text="activity_id      activity_name
1                WALKING
2                WALKING_UPSTAIRS
3                WALKING_DOWNSTAIRS
4                SITTING
5                STANDING
6                LAYING",header=TRUE)


xx2 <- read.table(text="activity_id
5
5
3
3
4
2",header=TRUE)

R, Merge Data Sets Changing Key Values

1 Answers1