'Dplyr function to match column value to row values and replace it
I have 2 data frames. Data Frame A is 
and Data Frame B is
I want to take the values of column active ident in Data Frame B and input them as a row on the top of Data Frame A in a way where they match up according to column name from Data Frame B
I have tried using dplyr but I cant seem to figure out how to do this in R. Would appreciate any help
Attaching dput(head) for both my files
Cell Labels
structure(list(`[email protected]` = structure(c(3L,
2L, 3L, 3L, 3L, 3L), .Label = c("Cluster_0", "Cluster_4", "Cluster_3",
"Cluster_2", "Cluster_1"), class = "factor")), row.names = c("pat01.pre_AAACCTGAGGAGCGAG",
"pat01.pre_AAACCTGCACTACAGT", "pat01.pre_AAACCTGTCACCGTAA", "pat01.pre_AAATGCCCACTATCTT",
"pat01.pre_AACCATGAGCATCATC", "pat01.pre_AACCGCGCAGATGGCA"), class = "data.frame")
Gene Count Per Cell :
dput(head(Gene_Counts_per_Cell[, c(1:5)]))
structure(list(pat01.pre_AAACCTGAGGAGCGAG = c(1.99399322071276,
1.5433201979508, 2.4725719042226, -2.59159111384049, 1.56977481481343,
0.192853860719877), pat01.pre_AAACCTGCACTACAGT = c(2.90248911455912,
2.27707326162242, 2.12992680712843, -1.44512552229319, 0.541062218328074,
1.8626908687607), pat01.pre_AAACCTGTCACCGTAA = c(3.99090573935858,
3.00560247848693, 2.9656947677965, -3.23693215603618, 4.72557633990864,
0.0247894431208639), pat01.pre_AAATGCCCACTATCTT = c(1.08405270702075,
-0.884466121620786, 0.500175551980942, -2.28142505510742, 3.97105313918843,
-1.01130712883293), pat01.pre_AACCATGAGCATCATC = c(4.55944063063621,
2.43937477176712, 3.93016796802459, -1.92695887361317, 3.16070890309665,
1.65917938530014)), row.names = c("ACTB", "ACTG1", "ACTN1", "ADAP2",
"ADM", "ADRB2"), class = "data.frame")
Solution 1:[1]
This maybe what you are looking for. Note have had to convert the clusters into numeric to ensure type consistency for columns. Use rownames to distinguish between clusters and other numeric data in the columns.
library(dplyr)
library(tidyr)
library(tibble)
library(stringr)
dfb %>%
rownames_to_column("rowname") %>%
pivot_wider(names_from = rowname, values_from = `[email protected]`) %>%
mutate(across(everything(), ~as.numeric(str_extract(.x, "\\d$")))) %>%
mutate(cluster = "cluster") %>%
column_to_rownames(var = "cluster") %>%
bind_rows(dfa)
#> pat01.pre_AAACCTGAGGAGCGAG pat01.pre_AAACCTGCACTACAGT
#> cluster 3.0000000 4.0000000
#> ACTB 1.9939932 2.9024891
#> ACTG1 1.5433202 2.2770733
#> ACTN1 2.4725719 2.1299268
#> ADAP2 -2.5915911 -1.4451255
#> ADM 1.5697748 0.5410622
#> ADRB2 0.1928539 1.8626909
#> pat01.pre_AAACCTGTCACCGTAA pat01.pre_AAATGCCCACTATCTT
#> cluster 3.00000000 3.0000000
#> ACTB 3.99090574 1.0840527
#> ACTG1 3.00560248 -0.8844661
#> ACTN1 2.96569477 0.5001756
#> ADAP2 -3.23693216 -2.2814251
#> ADM 4.72557634 3.9710531
#> ADRB2 0.02478944 -1.0113071
#> pat01.pre_AACCATGAGCATCATC pat01.pre_AACCGCGCAGATGGCA
#> cluster 3.000000 3
#> ACTB 4.559441 NA
#> ACTG1 2.439375 NA
#> ACTN1 3.930168 NA
#> ADAP2 -1.926959 NA
#> ADM 3.160709 NA
#> ADRB2 1.659179 NA
- data
dfb <- structure(list(`[email protected]` = structure(c(3L,
2L, 3L, 3L, 3L, 3L), .Label = c("Cluster_0", "Cluster_4", "Cluster_3",
"Cluster_2", "Cluster_1"), class = "factor")), row.names = c("pat01.pre_AAACCTGAGGAGCGAG",
"pat01.pre_AAACCTGCACTACAGT", "pat01.pre_AAACCTGTCACCGTAA", "pat01.pre_AAATGCCCACTATCTT",
"pat01.pre_AACCATGAGCATCATC", "pat01.pre_AACCGCGCAGATGGCA"), class = "data.frame")
dfa <- structure(list(pat01.pre_AAACCTGAGGAGCGAG = c(1.99399322071276,
1.5433201979508, 2.4725719042226, -2.59159111384049, 1.56977481481343,
0.192853860719877), pat01.pre_AAACCTGCACTACAGT = c(2.90248911455912,
2.27707326162242, 2.12992680712843, -1.44512552229319, 0.541062218328074,
1.8626908687607), pat01.pre_AAACCTGTCACCGTAA = c(3.99090573935858,
3.00560247848693, 2.9656947677965, -3.23693215603618, 4.72557633990864,
0.0247894431208639), pat01.pre_AAATGCCCACTATCTT = c(1.08405270702075,
-0.884466121620786, 0.500175551980942, -2.28142505510742, 3.97105313918843,
-1.01130712883293), pat01.pre_AACCATGAGCATCATC = c(4.55944063063621,
2.43937477176712, 3.93016796802459, -1.92695887361317, 3.16070890309665,
1.65917938530014)), row.names = c("ACTB", "ACTG1", "ACTN1", "ADAP2",
"ADM", "ADRB2"), class = "data.frame")
Created on 2022-03-21 by the reprex package (v2.0.1)
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | Peter |