I would like to copy the value from a dataframe to another dataframe. The difficulty for me is that I have four groups in both dataframes and in the dataframe where I would like to copy the values, I have several columns where I want to insert the value. More specifically, the dataframe (df1) from which I would like to copy the values looks like this:
structure(list(Name = c("A", "B", "C", "D"), Value = c(2L, 5L,
3L, 2L)), class = "data.frame", row.names = c(NA, -4L))
The dataframe (df2) where I want to insert the values looks like this:
structure(list(Name = c("A", "B", "C", "D"), `Rating 2017-06` = c(NA,
NA, NA, NA), `Rating 2017-07` = c(NA, NA, NA, NA), `Ratin g 2017-08` = c(NA,
NA, NA, NA), `Rating 2017-09` = c(NA, NA, NA, NA), `Rating 2017-10` = c(NA,
NA, NA, NA), `Rating 2017-11` = c(NA, NA, NA, NA), `Rating 2017-12` = c(NA,
NA, NA, NA), `Rating 2018-01` = c(4L, 4L, 3L, 3L), `Rating 2018-02` = c(3L,
4L, 3L, 2L)), class = "data.frame", row.names = c(NA, -4L))
I would like to copy the data in df1 in column "Value" to df2 to columns 2 to 8 ("Rating 2017-06" to "Rating 2017-12") for each Name.
What I have tried so far:
Merged_Data <- df1 %>%
left_join(df1, df2, by="Name")
The problem is with this code that I cannot specifically copy the values form df1 to selected columns as mentioned above in df2. I don't know what I have to right in the code that I would be able to do that.
Could someone help me here? Thank you already.
CodePudding user response:
Try this approach:
library(tidyverse)
df1 <- structure(list(
Name = c("A", "B", "C", "D"),
Value = c(2L, 5L,
3L, 2L)
),
class = "data.frame",
row.names = c(NA,-4L))
df2 <- structure(
list(
Name = c("A", "B", "C", "D"),
`Rating 2016-06` = c(NA,
NA, NA, NA),
`Rating 2017-07` = c(NA, NA, NA, NA),
`Ratin g 2017-08` = c(NA,
NA, NA, NA),
`Rating 2017-09` = c(NA, NA, NA, NA),
`Rating 2017-10` = c(NA,
NA, NA, NA),
`Rating 2017-11` = c(NA, NA, NA, NA),
`Rating 2017-12` = c(NA,
NA, NA, NA),
`Rating 2018-01` = c(4L, 4L, 3L, 3L),
`Rating 2018-02` = c(3L,
4L, 3L, 2L)), class = "data.frame", row.names = c(NA, -4L))
df2 |>
left_join(df1) |>
mutate(across(contains("2016") | contains("2017"), ~ Value)) |>
select(- Value)
#> Joining, by = "Name"
#> Name Rating 2016-06 Rating 2017-07 Ratin g 2017-08 Rating 2017-09
#> 1 A 2 2 2 2
#> 2 B 5 5 5 5
#> 3 C 3 3 3 3
#> 4 D 2 2 2 2
#> Rating 2017-10 Rating 2017-11 Rating 2017-12 Rating 2018-01 Rating 2018-02
#> 1 2 2 2 4 3
#> 2 5 5 5 4 4
#> 3 3 3 3 3 3
#> 4 2 2 2 3 2
df2 |>
left_join(df1) |>
mutate(across(`Rating 2016-06`:`Rating 2017-12`, ~ Value)) |>
select(- Value)
#> Joining, by = "Name"
#> Name Rating 2016-06 Rating 2017-07 Ratin g 2017-08 Rating 2017-09
#> 1 A 2 2 2 2
#> 2 B 5 5 5 5
#> 3 C 3 3 3 3
#> 4 D 2 2 2 2
#> Rating 2017-10 Rating 2017-11 Rating 2017-12 Rating 2018-01 Rating 2018-02
#> 1 2 2 2 4 3
#> 2 5 5 5 4 4
#> 3 3 3 3 3 3
#> 4 2 2 2 3 2
Created on 2022-04-30 by the reprex package (v2.0.1)