CODEWITHSUNDEEP
rdplyrtidyr
Antón
Antón

Reputation: 132

How to group two kinds of columns into two columns

I have downloaded data in a difficult format and I'm trying to make it tiddy. The dataframe has data from several samplings, and for each sampling it provides two columns, one for the year in which it was carried, and another for the number obtained. This is a dummy dataset with the same structure:

df<-data.frame(s1y=c(2000,2001,2002),
               s1r=c(5,23,5),
               s2y=c(2004,2004,2003),
               s2r=c(6,2,3),
               s3y=c(2009,2008,2006),
               s3r=c(4,2,12))

   s1y s1r  s2y s2r  s3y s3r
1 2000   5 2004   6 2009   4
2 2001  23 2004   2 2008   2
3 2002   5 2003   3 2006  12

In the column name, the number stands for the number of the sampling, and "y" and "r" stand for year and result. I don't really care about the sampling; I want two columns, one for the year and another one for the result, so it looks like this:

     y  r
1 2000  5
2 2001 23
3 2002  5
4 2004  6
5 2004  2
6 2003  3
7 2009  4
8 2008  2
9 2006 12

I've been trying to do this with pivot_longer(), but I don't know how to pivot into two long columns at the same time, and I've also tried to colapse it into one column like this

df%>%pivot_longer(cols=everything(),
                     names_pattern="(..)(.)",
                     names_to=c("sampling","type"),
                     values_to="result")

and then use pivot_wider(), but I haven't been able to achive the desired output.

Upvotes: 2

Views: 49

Answers (1)

dyrland
dyrland

Reputation: 648

How about this? If your data is consistent (only y and r) then you need an id_col that changes every two rows.

df %>%
  pivot_longer(cols = everything(),
               names_pattern="(..)(.)",
               names_to=c("sampling","type"),
               values_to = "result"
  ) %>%
  mutate(id_col = row_number() + row_number() %% 2) %>%
  pivot_wider(id_cols = "id_col",
              names_from = "type",
              values_from = "result"
  )

Upvotes: 1

Related Questions