Merging tables in R

Question

I have a question how to join two tables into one. I have the following two tables (fictive data ofc)

picture of tables

And I want to make a table like this:

Ideal combined table

I tried the join function and reshape function but keep getting wrong results or bigger tables. How can I best approach this. The data I want to eventually combine has 8-12 different results per ID (this case geld)

Does this answer your question? [How to join (merge) data frames (inner, outer, left, right)](https://stackoverflow.com/questions/1299871/how-to-join-merge-data-frames-inner-outer-left-right) — Mattia, Feb 05 '21 at 17:21
try not to upload images.. it's hard to reproduce your problem — StupidWolf, Feb 05 '21 at 17:29

score 1 · Answer 1 · answered Feb 05 '21 at 19:05

You need to left join and pivot wide:

library(dplyr)
library(tidyr)

d1 <- data.frame(ID = c( "geld1", "geld2", "geld3"), 
                 Dinero = c("dinero1", "dinero2", "dincero3"))

d2 <- data.frame(ID = c( "geld1", "geld2", "geld3", "geld1"),
                 money = c("money1", "money2", "money3", "money4"))

d1 %>% left_join(d2) %>% 
group_by(ID) %>% 
mutate(n=1:n()) %>% 
pivot_wider(id_cols=ID,names_from=n,values_from=money,names_prefix="result") 


Joining, by = "ID"
# A tibble: 3 x 3
# Groups:   ID [3]
  ID    result1 result2
  <fct> <fct>   <fct>  
1 geld1 money1  money4 
2 geld2 money2  NA     
3 geld3 money3  NA

Merging tables in R

1 Answers1