How to get this outcome in R-CodePudding

I have multiple data frames. Here I have demonstrated 3 data frames with different rows.

dat1<-read.table (text=" D  Size1
A1  12
A2  18
A3  16
A4  14
A5  11
A6  0
Value1  25
Score1  30
", header=TRUE)

dat2<-read.table (text=" D  Size2
S12 5
S13 9
S14 11
S15 12
S16 12
Value2  40
Score2  45
", header=TRUE)

dat3<-read.table (text=" D  Size2
S17 0
S19 1
S22 2
S33 1
Value3  22
Score3  60

", header=TRUE)

I want to get the following outcome:

D   Value   Score
1   25  30
2   40  45
3   22  60

I need to get a data frame only for value and score

CodePudding user response：

We may have to filter the rows after binding the datasets into a single data and then use pivot_wider to reshape back to wide

library(dplyr)
library(tidyr)
library(stringr)
bind_rows(dat1, dat2, dat3) %>% 
  filter(str_detect(D, '(Value|Score)\\d ')) %>%
  separate(D, into = c("colnm", "D"), sep = "(?<=[a-z](?=\\d))") %>% 
  group_by(colnm, D) %>%
  transmute(Score = coalesce(Size1, Size2)) %>%
  ungroup %>%
  pivot_wider(names_from = colnm, values_from = Score)

-output

# A tibble: 3 × 3
  D     Value Score
  <chr> <int> <int>
1 1        25    30
2 2        40    45
3 3        22    60

Or an option in base R

do.call(rbind, Map(function(dat, y) data.frame(D = y,
   Value = dat[[2]][grepl('Value', dat$D)], 
  Score = dat[[2]][grepl('Score', dat$D)]), list(dat1, dat2, dat3), 1:3))
  D Value Score
1 1    25    30
2 2    40    45
3 3    22    60