Populate columns by matching name of column in data-frame with row values

Question

df1

1 2 3 4 5 ...53 
0 0 0 0 0     
...

Columns in the above df sits within the range 8:60 (Column Position)

df2

Group.1   x 
   1      10
   2      15
   3      20
   4      5
   5      0
...53

There are 53 columns in df1 from the range 8:60 and there of 53 rows in df2

The idea is to match the column names in df1 so 1-53 with the 'Group.1' column in df2 and if there is a match then populate columns row in df1 with the corresponding value in the 'x' column in df2

Ouptut df1

    1  2  3  4 5 ...53 
    10 15 20 5 0

How would I go about doing this?

Thanks,

Dharman · Accepted Answer · 2021-02-02 16:10:20Z

1

that would be a solution with the tidyverse-packages:

library(tibble)
library(dplyr)
#> 
#> Attaching package: 'dplyr'
#> The following objects are masked from 'package:stats':
#> 
#>     filter, lag
#> The following objects are masked from 'package:base':
#> 
#>     intersect, setdiff, setequal, union
library(tidyr)


df1 <- tibble::tribble(
  ~"1", ~"2", ~"3", ~"4", ~"5",
  0, 0, 0, 0, 0
)


df2 <- tibble::tribble(
  ~"Group.1", ~"x",
  1, 10,
  2, 15,
  3, 20,
  4, 5,
  5, 0
)


df1_long <- df1 %>% tidyr::pivot_longer(
  cols = dplyr::everything(),
  names_to = "name",
  values_to = "value"
)


df2_char <- df2 %>% dplyr::mutate(name = as.character(Group.1))

df <- df1_long %>% dplyr::left_join(df2_char, by = c("name"))

df_wide <- df %>%
  dplyr::select(name, x) %>%
  tidyr::pivot_wider(names_from = name, values_from = x)

^{Created on 2021-02-02 by the reprex package (v1.0.0)}

edited Feb 2, 2021 at 16:10

Dharman♦

33.9k27 gold badges106 silver badges157 bronze badges

answered Feb 2, 2021 at 16:05

Andi

1528 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Populate columns by matching name of column in data-frame with row values

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related