CODEWITHSUNDEEP
rfunctionsubsettidyr
Nneka
Nneka

Reputation: 1860

gather function in R to match patterns in character strings

I want to gather reshape wide table to long table. The columns i want to gather have a pattern. For now i only managed to gather them by their position. How can i change this to gather them by the patterns in column names? please only use the gather function.

I have included an example dataset, however in the real dataset there are many more columns. Therefore I would like to gather all columns that:

  1. start with an f or m

  2. are followed by one OR two numbers

    dput(head(test1, 1)) structure(list(startdate = "2019-11-06", id = "POL55", m0_9 = NA_real_, m10_19 = NA_real_, m20_29 = NA_real_, m30_39 = NA_real_, m40_49 = 32, m50_59 = NA_real_, m60_69 = NA_real_, m70 = NA_real_, f0_9 = 32, f10_19 = NA_real_, f20_29 = NA_real_, f30_39 = NA_real_, f40_49 = NA_real_, f50_59 = NA_real_, f60_69 = NA_real_, f70 = NA_real_), row.names = c(NA, -1L), class = c("tbl_df", "tbl", "data.frame"))

    df_age2 <- test1 %>% gather(age_cat, count, m0_9:f70 ) df_age2

expected output (there will be many more columns that are not gathered). The count should of course count...

 startdate  id    age_cat count
   <chr>      <chr> <chr>      <dbl>
 1 2019-11-06 POL55 m0_9          NA
 2 2019-11-06 POL56 m0_9          NA
 3 2019-11-06 POL57 m0_9          NA
 4 2019-11-06 POL58 m0_9          NA
 5 2019-11-06 POL59 m0_9          NA
 6 2019-11-06 POL60 m0_9          NA
 7 2019-11-06 POL61 m0_9          NA
 8 2019-11-06 POL62 m0_9          NA
 9 2019-11-06 POL63 m0_9          NA
10 2019-11-06 POL64 m0_9          NA

Upvotes: 1

Views: 643

Answers (2)

akrun
akrun

Reputation: 887108

We can use pivot_longer from tidyr

 library(dplyr)
 library(tidyr)
 test1 %>% 
    pivot_longer(cols = -c(startdate, id), names_to = c('.value', 'grp'), names_sep="_")

Or it could be

test1 %>% 
  pivot_longer(cols = -c(startdate, id),
      names_to = c( '.value', 'grp'), names_pattern = "^([a-z])(.*)")
# A tibble: 8 x 5
#  startdate  id    grp       m     f
#  <chr>      <chr> <chr> <dbl> <dbl>
#1 2019-11-06 POL55 0_9      NA    32
#2 2019-11-06 POL55 10_19    NA    NA
#3 2019-11-06 POL55 20_29    NA    NA
#4 2019-11-06 POL55 30_39    NA    NA
#5 2019-11-06 POL55 40_49    32    NA
#6 2019-11-06 POL55 50_59    NA    NA
#7 2019-11-06 POL55 60_69    NA    NA
#8 2019-11-06 POL55 70       NA    NA

Or may be

test1 %>% 
  pivot_longer(cols = -c(startdate, id), 
     names_to = c( 'grp',  '.value'), names_pattern = "^([a-z])(.*)")
# A tibble: 2 x 11
#   startdate  id    grp   `0_9` `10_19` `20_29` `30_39` `40_49` `50_59` `60_69`  `70`
#  <chr>      <chr> <chr> <dbl>   <dbl>   <dbl>   <dbl>   <dbl>   <dbl>   <dbl> <dbl>
#1 2019-11-06 POL55 m        NA      NA      NA      NA      32      NA      NA    NA
#2 2019-11-06 POL55 f        32      NA      NA      NA      NA      NA      NA    NA

Or it can be

test1 %>% 
  pivot_longer(cols = matches("^(f|m)\\d+_?\\d*$"), names_to = 'age_bucket',
        values_to = 'count')
# A tibble: 16 x 4
#   startdate  id    age_bucket count
#   <chr>      <chr> <chr>      <dbl>
# 1 2019-11-06 POL55 m0_9          NA
# 2 2019-11-06 POL55 m10_19        NA
# 3 2019-11-06 POL55 m20_29        NA
# 4 2019-11-06 POL55 m30_39        NA
# 5 2019-11-06 POL55 m40_49        32
# 6 2019-11-06 POL55 m50_59        NA
# 7 2019-11-06 POL55 m60_69        NA
# 8 2019-11-06 POL55 m70           NA
# 9 2019-11-06 POL55 f0_9          32
#10 2019-11-06 POL55 f10_19        NA
#11 2019-11-06 POL55 f20_29        NA
#12 2019-11-06 POL55 f30_39        NA
#13 2019-11-06 POL55 f40_49        NA
#14 2019-11-06 POL55 f50_59        NA
#15 2019-11-06 POL55 f60_69        NA
#16 2019-11-06 POL55 f70           NA

Upvotes: 3

eastclintw00d
eastclintw00d

Reputation: 2364

Use starts_with:

test1 %>% 
  gather(age_bucket, count, c(starts_with("m"), starts_with("f")))

Upvotes: 4

Related Questions