Populate table with values from another table based on both rows and columns

user_7186557 0
Viewed 190

I have an empty data frame that looks like that:

df <- data.frame(Hugo_Symbol=c("CDKN2A", "JUN", "IRS2","MTOR",
                          "NRAS"),
                 A183=c(NA, NA, NA, NA, NA),
                 A240=c(NA, NA, NA, NA, NA),
                 A330=c(NA, NA, NA, NA, NA))

I would like to use a larger data frame to populate the previous one. The structure of the larger data frame is the following:

df2 <- data.frame(Hugo_Symbol=c("CDKN2A", "JUN", "IRS2","MTOR",
                          "NRAS", "TP53", "EGFR"),
                 A183=c(2.3, 3.3, 2.6, 4.7, 1.2, 5.7, 3.4),
                 A240=c(1.3, 2.3, 4.6, 5.7, 2.2, 7.7, 1.4),
                 A330=c(0.3, 2.3, 1.6, 1.7, 4.2, 1.7, 4.4),
                 A335=c(1.3, 0.3, 0.6, 0.7, 0.2, 0.7, 0.4),
                 A345=c(0.3, 4.3, 4.6, 4.7, 4.2, 4.7, 0.4))

My desired output should look like that:

Hugo_Symbol A183 A240 A330
1      CDKN2A  2.3  1.3  0.3
2         JUN  3.3  2.3  2.3
3        IRS2  2.6  4.6  1.6
4        MTOR  4.7  5.7  1.7
5        NRAS  1.2  2.2  4.2

I tried to use dplyr package, specifically semi_join() function, but it returns empty table to me.

populate dataframe r
4 Answers

You can also use the following solution:

library(dplyr)

df %>%
  left_join(df2, by = "Hugo_Symbol") %>%
  mutate(across(ends_with(".x"), ~ coalesce(.x, get(gsub(".x", ".y", cur_column()))))) %>%
  select(Hugo_Symbol, ends_with(".x")) %>%
  rename_with(~ gsub(".x", "", .), ends_with(".x"))

  Hugo_Symbol A183 A240 A330
1      CDKN2A  2.3  1.3  0.3
2         JUN  3.3  2.3  2.3
3        IRS2  2.6  4.6  1.6
4        MTOR  4.7  5.7  1.7
5        NRAS  1.2  2.2  4.2
user_143145200

We could use a join

library(data.table)
nm1 <- names(df)[-1]
df[nm1] <- lapply(df[nm1], as.numeric)
setDT(df)[df2, (nm1) := mget(paste0('i.', nm1)), on = .(Hugo_Symbol)]

-ouptut

df
   Hugo_Symbol A183 A240 A330
1:      CDKN2A  2.3  1.3  0.3
2:         JUN  3.3  2.3  2.3
3:        IRS2  2.6  4.6  1.6
4:        MTOR  4.7  5.7  1.7
5:        NRAS  1.2  2.2  4.2
user_37322710

Is it possible to just drop the NA columns from the first data frame? If so, a left join will produce the desired output.

df <- data.frame(
  Hugo_Symbol = c("CDKN2A", "JUN", "IRS2", "MTOR",
                  "NRAS"),
  A183 = c(NA, NA, NA, NA, NA),
  A240 = c(NA, NA, NA, NA, NA),
  A330 = c(NA, NA, NA, NA, NA)
)

df2 <- data.frame(
  Hugo_Symbol = c("CDKN2A", "JUN", "IRS2", "MTOR",
                  "NRAS", "TP53", "EGFR"),
  A183 = c(2.3, 3.3, 2.6, 4.7, 1.2, 5.7, 3.4),
  A240 = c(1.3, 2.3, 4.6, 5.7, 2.2, 7.7, 1.4),
  A330 = c(0.3, 2.3, 1.6, 1.7, 4.2, 1.7, 4.4),
  A335 = c(1.3, 0.3, 0.6, 0.7, 0.2, 0.7, 0.4),
  A345 = c(0.3, 4.3, 4.6, 4.7, 4.2, 4.7, 0.4)
)

library(dplyr)

left_join(df["Hugo_Symbol"], df2, by = "Hugo_Symbol")
user_93620180

one more way to do it-

  • left_join on hugo_symbol
  • then use transmute across on those columns only which either end in suffix .y and hugo_symbol
  • retain values as such. hence ~.
  • remove .y from names using .names argument. use regex [.]y so that is not interpreted as wildcard and y.
library(dplyr)

df %>% left_join(df2, by = 'Hugo_Symbol') %>%
  transmute(across(Hugo_Symbol | ends_with('.y'), ~., .names = '{gsub("[.]y", "", .col )}'))

#>   Hugo_Symbol A183 A240 A330
#> 1      CDKN2A  2.3  1.3  0.3
#> 2         JUN  3.3  2.3  2.3
#> 3        IRS2  2.6  4.6  1.6
#> 4        MTOR  4.7  5.7  1.7
#> 5        NRAS  1.2  2.2  4.2

Created on 2021-07-24 by the reprex package (v2.0.0)

user_28848590
Related