r - 有没有办法将 .csv 中的第一行需要是列，第二行标题，但在 R 中从宽格式转换为长格式？

Question

在任何地方都找不到它，但我有下面的 data.frame，并且需要看起来像第二个 data.frame，但在第一行中挣扎。有任何想法吗？ (在原始 .csv 中，我有 18 个变量和 28 个观察值)。

这是我所拥有的 data.frame 示例:

#Have this  cnames_have <- data.frame(names = c(NA, "Name", "BMC", "MFH", "MCHHS", "CIH"), Official.use.only = c( NA, "Last Updated", "2020-11-10", "2020-10-10", "2020-11-10", "2020-11-09"), X = c("Adult Unit", "Staffed", 8, NA, 0, 62), X1 = c(NA, "Current Available", 3, NA, 0,13), X2 = c("Pediatric Unit", "Staffed", 8, NA, 0, 62), X3 = c(NA, "Current Available", 3, NA, 0,13)) 

这是我需要的示例:

#need this  cnames <- data.frame(names = c("BMC", "MFH", "MCHHS", "CIH", "BMC", "MFH", "MCHHS", "CIH"), Last_Updated = c("2020-11-10", "2020-10-10", "2020-11-10", "2020-11-09"), beds = c("Adult Unit", "Adult Unit", "Adult Unit", "Adult Unit", "Pediatric Unit", "Pediatric Unit", "Pediatric Unit", "Pediatric Unit"), Staffed = c(8, NA, 0, 62, 8, NA, 0, 62), Current_Available = c(3, NA, 0,13, 3, NA, 0,13))

我尝试过转置、融化、dcast、聚集等。这是我所能得到的，但后来我想不出从那里去哪里，或者我是否只是对自己进行代码屏蔽。

    df <- as.data.frame(t(cnames)) #from Have this cnames data frame

df <- df %>% rename(col_1 = "2") %>% fill(col_1)

任何帮助都会很棒，因为我需要弄清楚这一点，以便我可以在我的情况报告中包含视觉效果。预先感谢您!

Answer 1

通过先长后回宽的 dplyr 和 tidyr 解决方案

library(dplyr)
cnames_have <- data.frame(names = c(NA, "Name", "BMC", "MFH", "MCHHS", "CIH"), Official.use.only = c( NA, "Last Updated", "2020-11-10", "2020-10-10", "2020-11-10", "2020-11-09"), X = c("Adult Unit", "Staffed", 8, NA, 0, 62), X1 = c(NA, "Current Available", 3, NA, 0,13), X2 = c("Pediatric Unit", "Staffed", 8, NA, 0, 62), X3 = c(NA, "Current Available", 3, NA, 0,13)) 

cnames_have %>% rename(Last_Updated = Official.use.only,
                       `Adult Staffed` = X,
                       `Adult Available` = X1,
                       `Pediatric Staffed` = X2,
                       `Pediatric Available` = X3) %>%
   slice(-1:-2) %>%
   tidyr::pivot_longer(`Adult Staffed`:`Pediatric Available`) %>%
   tidyr::separate(., name, into = c("beds", "type")) %>%
   tidyr::pivot_wider(names_from = type) %>%
   arrange(beds)
#> # A tibble: 8 x 5
#>   names Last_Updated beds      Staffed Available
#>   <chr> <chr>        <chr>     <chr>   <chr>    
#> 1 BMC   2020-11-10   Adult     8       3        
#> 2 MFH   2020-10-10   Adult     <NA>    <NA>     
#> 3 MCHHS 2020-11-10   Adult     0       0        
#> 4 CIH   2020-11-09   Adult     62      13       
#> 5 BMC   2020-11-10   Pediatric 8       3        
#> 6 MFH   2020-10-10   Pediatric <NA>    <NA>     
#> 7 MCHHS 2020-11-10   Pediatric 0       0        
#> 8 CIH   2020-11-09   Pediatric 62      13