Is there a way to turn a .csv where row one needs to be a column, 2nd row headers but from wide to long format in R?

user_14621207 0
Viewed 31

Can't find it anywhere but I have the below data.frame and need to to look like the second data.frame but struggling with the first row. Any ideas? (in the original .csv I have 18 variables with 28 observations).

Here is a data.frame example of what I have:

#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))

Here is an example of what I need:

#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))

I have tried transpose, melt, dcast, gather, etc. Here is as far as I was able to get but then I couldn't think of where to go from there or if I just code-blocked myself.

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

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

Any help would be awesome as I need to figure this out so I can include visuals in my situational reports. Thank you in advance!

r
3 Answers

A dplyr and tidyr solution by pivotting long then back wide

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
user_91900340

This seems to work but is not as elegant as I would like, I'm sure there is a way to do this using pivot_longer. The data wrangling block of code could be put into a function.


library(dplyr)
library(tidyr)
library(purrr)

# Extract unique names from data
vec_names <- cnames_have[2, 1:4]


au <- 
  cnames_have %>% 
  select(names:X1) %>% 
  set_names(vec_names) %>% 
  mutate(beds = "Adult Unit") %>% 
  filter(row_number() > 2)
 

cnames <- 
  cnames_have %>% 
  select(names, Official.use.only, X2, X3) %>% 
  set_names(vec_names) %>% 
  mutate(beds = "Pediatric Unit") %>% 
  filter(row_number() > 2) %>% 
  bind_rows(au)

cnames
#>    Name Last Updated Staffed Current Available           beds
#> 1   BMC   2020-11-10       8                 3 Pediatric Unit
#> 2   MFH   2020-10-10    <NA>              <NA> Pediatric Unit
#> 3 MCHHS   2020-11-10       0                 0 Pediatric Unit
#> 4   CIH   2020-11-09      62                13 Pediatric Unit
#> 5   BMC   2020-11-10       8                 3     Adult Unit
#> 6   MFH   2020-10-10    <NA>              <NA>     Adult Unit
#> 7 MCHHS   2020-11-10       0                 0     Adult Unit
#> 8   CIH   2020-11-09      62                13     Adult Unit

data

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))

Created on 2020-11-11 by the reprex package (v0.3.0)

user_69365450 
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)) 

colnames(cnames_have) <- c("Name", "Last Updated", "Adult Unit", "Adult Unit Current Available", "Pediatric Unit", "Pediatric Unit Current Available")

cnames_have <- cnames_have[-1, ]
cnames_have <- cnames_have[-1, ]

cnames_have <- cnames_have[, c(1, 2, 3, 5, 4, 6)]

library(tidyr)
cnames_have <- gather(cnames_have, 'Unit', 'Staffed', 3:4)

cnames_have <- cnames_have[, -3]

colnames(cnames_have) <- c("names", "Last_Updated", "Current_Available", "beds", "Staffed")

cnames_have <- cnames_have[, c(1, 2, 4, 5, 3)]
user_71477170
Related