My data set has columns where the variable categories are mentioned, for eg: column name is "Shrawan 2071 -Dog Bite". Here "Shrawan" is the month "2071" is a year and "-Dog Bite" is the type of bite. Similarly, I have another column "Bhadra 2071 -Other Rabies Susceptible Animal Bite" where "Bhadra" is the month, "2071" is the year and "Other Rabies Susceptible Animal Bite" is the type of bite, and so on. In this way, I have 12 different month categories, 2 types of bite and 10-year data. How do I separate these column names as categories in separate columns in tidyverse?
My data using "dput(head(bites_hmis, 10)[, 1:30])":
structure(list(Province = c("1 Koshi Province", "1 Koshi Province",
"1 Koshi Province", "1 Koshi Province", "1 Koshi Province", "1 Koshi Province",
"1 Koshi Province", "1 Koshi Province", "1 Koshi Province", "1 Koshi Province"
), District = c("101 TAPLEJUNG", "101 TAPLEJUNG", "101 TAPLEJUNG",
"101 TAPLEJUNG", "101 TAPLEJUNG", "101 TAPLEJUNG", "101 TAPLEJUNG",
"101 TAPLEJUNG", "101 TAPLEJUNG", "101 TAPLEJUNG"), Municipaltiy = c("10108 Sirijanga Rural Municipality",
"10109 Sidingba Rural Municipality", "10107 Yangwarak Rural Municipality",
"10105 Aatharai Tribeni Rural Municipality", "10105 Aatharai Tribeni Rural Municipality",
"10105 Aatharai Tribeni Rural Municipality", "10106 Phungling Municipality",
"10106 Phungling Municipality", "10104 Maiwakhola Rural Municipality",
"10106 Phungling Municipality"), Ward = c("10108 Sirijanga 02",
"10109 Sidingba 03", "10107 Yangwarak 05", "10105 Aatharai Tribeni 05",
"10105 Aatharai Tribeni 04", "10105 Aatharai Tribeni 02", "10106 Phungling 01",
"10106 Phungling 02", "10104 Maiwakhola 02", "10106 Phungling 08"
), `Health Facility` = c("AMBEGUDIN HP TAPLEJUNG", "ANGKHOP HP TAPLEJUNG",
"CHAKSIBOTE HP TAPLEJUNG", "CHANGE BHSC TAPLEJUNG", "CHANGE HP TAPLEJUNG",
"CHOKPUR HP TAPLEJUNG", "DANDAGAUN BHSC PHUNGLING 01 TAPLEJUNG",
"DEULINGE BHSC TAPLEJUNG", "DHUNGESAGHU PHC TAPLEJUNG", "DOKHU HP TAPLEJUNG"
), `Shrawan 2071 -Dog Bite` = c(NA, NA, NA, NA, 2, NA, NA, NA,
NA, 2), `Shrawan 2071 -Other Rabies Susceptible Animal Bite` = c(NA_real_,
NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_,
NA_real_, NA_real_), `Bhadra 2071 -Dog Bite` = c(NA, NA, NA,
NA, 2, NA, NA, NA, 1, NA), `Bhadra 2071 -Other Rabies Susceptible Animal Bite` = c(NA_real_,
NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_,
NA_real_, NA_real_), `Ashwin 2071 -Dog Bite` = c(NA_real_, NA_real_,
NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_,
NA_real_), `Ashwin 2071 -Other Rabies Susceptible Animal Bite` = c(NA_real_,
NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_,
NA_real_, NA_real_), `Kartik 2071 -Dog Bite` = c(NA_real_, NA_real_,
NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_,
NA_real_), `Kartik 2071 -Other Rabies Susceptible Animal Bite` = c(NA_real_,
NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_,
NA_real_, NA_real_), `Mangsir 2071 -Dog Bite` = c(NA, NA, NA,
NA, 1, NA, NA, NA, NA, NA), `Mangsir 2071 -Other Rabies Susceptible Animal Bite` = c(NA_real_,
NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_,
NA_real_, NA_real_), `Poush 2071 -Dog Bite` = c(NA, NA, NA, NA,
2, NA, NA, NA, NA, 1), `Poush 2071 -Other Rabies Susceptible Animal Bite` = c(NA_real_,
NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_,
NA_real_, NA_real_), `Magh 2071 -Dog Bite` = c(NA, NA, NA, NA,
1, NA, NA, NA, NA, NA), `Magh 2071 -Other Rabies Susceptible Animal Bite` = c(NA_real_,
NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_,
NA_real_, NA_real_), `Falgun 2071 -Dog Bite` = c(NA_real_, NA_real_,
NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_,
NA_real_), `Falgun 2071 -Other Rabies Susceptible Animal Bite` = c(NA_real_,
NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_,
NA_real_, NA_real_), `Chaitra 2071 -Dog Bite` = c(NA_real_, NA_real_,
NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_,
NA_real_), `Chaitra 2071 -Other Rabies Susceptible Animal Bite` = c(NA_real_,
NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_,
NA_real_, NA_real_), `Baishak 2072 -Dog Bite` = c(NA_real_, NA_real_,
NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_,
NA_real_), `Baishak 2072 -Other Rabies Susceptible Animal Bite` = c(NA_real_,
NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_,
NA_real_, NA_real_), `Jestha 2072 -Dog Bite` = c(NA, NA, NA,
NA, NA, NA, NA, NA, NA, 1), `Jestha 2072 -Other Rabies Susceptible Animal Bite` = c(NA_real_,
NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_,
NA_real_, NA_real_), `Asar 2072 -Dog Bite` = c(NA_real_, NA_real_,
NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_,
NA_real_), `Asar 2072 -Other Rabies Susceptible Animal Bite` = c(NA_real_,
NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_,
NA_real_, NA_real_), `Shrawan 2072 -Dog Bite` = c(NA_real_, NA_real_,
NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_,
NA_real_)), row.names = c(NA, -10L), class = c("tbl_df", "tbl",
"data.frame"))
bites_hmis |>
pivot_longer(6:30, values_drop_na = TRUE) |>
separate(name, c("Month", "Year", "Type"), sep = " ", extra = "merge")
Result
# A tibble: 9 × 9
Province District Municipaltiy Ward `Health Facility` Month Year Type value
<chr> <chr> <chr> <chr> <chr> <chr> <chr> <chr> <dbl>
1 1 Koshi Province 101 TAPLEJUNG 10105 Aatharai Tribeni Rural Municipality 10105 Aatharai Tribeni 04 CHANGE HP TAPLEJUNG Shrawan 2071 Dog Bite 2
2 1 Koshi Province 101 TAPLEJUNG 10105 Aatharai Tribeni Rural Municipality 10105 Aatharai Tribeni 04 CHANGE HP TAPLEJUNG Bhadra 2071 Dog Bite 2
3 1 Koshi Province 101 TAPLEJUNG 10105 Aatharai Tribeni Rural Municipality 10105 Aatharai Tribeni 04 CHANGE HP TAPLEJUNG Mangsir 2071 Dog Bite 1
4 1 Koshi Province 101 TAPLEJUNG 10105 Aatharai Tribeni Rural Municipality 10105 Aatharai Tribeni 04 CHANGE HP TAPLEJUNG Poush 2071 Dog Bite 2
5 1 Koshi Province 101 TAPLEJUNG 10105 Aatharai Tribeni Rural Municipality 10105 Aatharai Tribeni 04 CHANGE HP TAPLEJUNG Magh 2071 Dog Bite 1
6 1 Koshi Province 101 TAPLEJUNG 10104 Maiwakhola Rural Municipality 10104 Maiwakhola 02 DHUNGESAGHU PHC TAPLEJUNG Bhadra 2071 Dog Bite 1
7 1 Koshi Province 101 TAPLEJUNG 10106 Phungling Municipality 10106 Phungling 08 DOKHU HP TAPLEJUNG Shrawan 2071 Dog Bite 2
8 1 Koshi Province 101 TAPLEJUNG 10106 Phungling Municipality 10106 Phungling 08 DOKHU HP TAPLEJUNG Poush 2071 Dog Bite 1
9 1 Koshi Province 101 TAPLEJUNG 10106 Phungling Municipality 10106 Phungling 08 DOKHU HP TAPLEJUNG Jestha 2072 Dog Bite 1