r语言 - 将两个数据帧折叠成一个数组结构

Question

data_1 <- data.frame(V1 = c("123","345","546","890"), V2 = c("J10","K12","R34","J17"),V3=c("N12","M34","W57","Q90"))

data_1| v1 | v2 | v3|:---- |:------:| -----:|| 123 | j10 | n12 |【345】k12 | m34 || 546 | n12 | r34 || 890 | J17 | J10 |

data_2 <-  data.frame(V1 = c("123","345","546","890"), V2 = c("01/02/90","10/04/21","09/03/95","29/03/90"),V3=c("28/07/86","16/02/87","17/10/56","14/01/60"))

data_2| v1 | v2 | v3|:---- |:------:| -----:|| 123 | 01/02/90 | 28/07/86 || 345 | 10/04/21 | 16/02/87 || 546 | 09/03/95 | 17/10/56 || 890 | 29/03/90 | 14/01/60 |

我想有一个共同的第一列，并将数据折叠成一个数组结构

结果:| v1 | j10 | n12 | k12 | m34 | r34 | j17 ||:---- |:----:| :----:| :----: | :----: | :----: | ----:|| 123 | 01/02/90 | 28/07/86 || || || 345 | |10/04/21|16/02/87 | || 546 || 09/03/95 || 17/10/56 || 890 |14/01/60 | || || 29/03/90 |

Answer 1

我们可以重塑为'长'格式，绑定数据集，然后重塑为'宽'

library(dplyr)
library(tidyr)
bind_cols(data_1 %>% 
pivot_longer(cols = -V1), 
data_2 %>% 
pivot_longer(cols = -V1) %>% 
select(-V1)) %>%
select(-starts_with('name')) %>% 
pivot_wider(names_from = value...3, values_from = value...5)

与产出

# A tibble: 4 × 9
V1    J10      N12      K12      M34      R34      W57      J17      Q90     
<chr> <chr>    <chr>    <chr>    <chr>    <chr>    <chr>    <chr>    <chr>   
1 123   01/02/90 28/07/86 <NA>     <NA>     <NA>     <NA>     <NA>     <NA>    
2 345   <NA>     <NA>     10/04/21 16/02/87 <NA>     <NA>     <NA>     <NA>    
3 546   <NA>     <NA>     <NA>     <NA>     09/03/95 17/10/56 <NA>     <NA>    
4 890   <NA>     <NA>     <NA>     <NA>     <NA>     <NA>     29/03/90 14/01/60

Answer 2

data_1 <- data.frame(V1 = c("123","345","546","890"), V2 = c("J10","K12","R34","J17"),V3=c("N12","M34","W57","Q90"))
data_2 <- data.frame(V1 = c("123","345","546","890"), V2 = c("01/02/90","10/04/21","09/03/95","29/03/90"),V3=c("28/07/86","16/02/87","17/10/56","14/01/60"))
var_1 <- data.frame( V1= data_1$V1, VAR = data_1$V2, stringsAsFactors = F)
var_2 <- data.frame( V1= data_1$V1, VAR = data_1$V3, stringsAsFactors = F)
var <- bind_rows(var_1,var_2)
date_1 <- data.frame( V1= data_2$V1, DATE = data_2$V2, stringsAsFactors = F)
date_2 <- data.frame( V1= data_2$V1, DATE = data_2$V2, stringsAsFactors = F)
date <- bind_rows(date_1,date_2)
result <- left_join(var, date) %>% mutate_all(as.character) %>% distinct()
result <- result %>% pivot_wider(names_from = VAR, values_from = DATE)
result
V1    J10      K12      R34      J17      N12      M34      W57      Q90     
<chr> <chr>    <chr>    <chr>    <chr>    <chr>    <chr>    <chr>    <chr>   
1 123   01/02/90 NA       NA       NA       01/02/90 NA       NA       NA      
2 345   NA       10/04/21 NA       NA       NA       10/04/21 NA       NA      
3 546   NA       NA       09/03/95 NA       NA       NA       09/03/95 NA      
4 890   NA       NA       NA       29/03/90 NA       NA       NA       29/03/90