0

I have this data which is in wide format, with the width of s02 occupying a maximum of three that is 0,1,2

id_1<-c(1,2,2,2)
s02_0<-c(1,1,4,7)
s02_1<-c(2,2,5,8)
s02_2<-c(3,3,6,9)
id_2<-c(1,1,2,3)

I would wish to reshape my data, and create an extra column say n which shows the position occupied by 's02_' in every given row. My expected output is as below

id_1<-c(1,1,1,2,2,2,2,2,2,2,2,2)
s02<-c(1,2,3,1,2,3,4,5,6,7,8,9)
n<-c(1,2,3,1,2,3,1,2,3,1,2,3)
df2<-data.frame(id_1,s02,n)
r2evans
  • 77,184
  • 4
  • 55
  • 96
Sam Mwenda
  • 136
  • 7
  • 1
    This is your *fourth* question ([1](https://stackoverflow.com/q/63030902/), [2](https://stackoverflow.com/q/63209019/), [3](https://stackoverflow.com/q/63217608/), and [4](https://stackoverflow.com/q/63219279/)) tagged with both [tag:long-integer] and [tag:widechar], but your data provided has nothing to do with "large"-ish integers, and no strings at all (much less other than utf-8). Please don't include irrelevant tags, it seems like you're just including them for increased visibility. There are certainly more-relevant tags to find (perhaps [tag:reshape]? [tag:data.frame]?). – r2evans Aug 02 '20 at 19:38
  • 1
    The Stack tag-recommendation system is decent but can find relevance in illogical tags, *Please* take a moment to review them, even as a post-submit revision to your question. You can mouse hover over each tag to see a brief description; while some tags have no description, the ones you have here *do*, and there appears to be no applicability to your question/issue. – r2evans Aug 02 '20 at 19:40
  • 1
    Thanks for that, just that am seeking to transform my data from wide to long with an extra column for the count. I will be careful next time.to include logical tags – Sam Mwenda Aug 02 '20 at 19:53
  • Thank you for the edit! For clarity, [tag:reshape2] refers very specifically to the [`reshape2`](https://cran.r-project.org/web/packages/reshape2/index.html) package in R, whereas the [tag:reshape] tag is more generic about the *process* of transforming data from one layout to another (e.g., wide-to-long). I think the two tags are often viewed by the same people, but by including `reshape2`, you are *implying* that you want to use that package, when I believe that viable answers might otherwise recommend `tidyr::pivot_longer` or `data.table::melt`. – r2evans Aug 02 '20 at 19:58

1 Answers1

1

Here's a tidyverse version that is nearly a one-liner:

library(tidyr)
pivot_longer(select(df1, -id_2), starts_with("s02_"),
             names_to = "n", names_prefix = "s02_", values_to = "s02")
# # A tibble: 12 x 3
#     id_1 n       s02
#    <dbl> <chr> <dbl>
#  1     1 0         1
#  2     1 1         2
#  3     1 2         3
#  4     2 0         1
#  5     2 1         2
#  6     2 2         3
#  7     2 0         4
#  8     2 1         5
#  9     2 2         6
# 10     2 0         7
# 11     2 1         8
# 12     2 2         9

Note: this n is 0-based, because its value is derived from the column names s02_0, s02_1, and s02_2; this can be fixed with a little post-processing (e.g., convert to integer, add one, optionally convert back to character), such as

library(dplyr)
pivot_longer(select(df1, -id_2), starts_with("s02_"),
             names_to = "n", names_prefix = "s02_", values_to = "s02") %>%
  mutate(n = as.integer(n) + 1L)
# # A tibble: 12 x 3
#     id_1     n   s02
#    <dbl> <int> <dbl>
#  1     1     1     1
#  2     1     2     2
#  3     1     3     3
#  4     2     1     1
#  5     2     2     2
#  6     2     3     3
#  7     2     1     4
#  8     2     2     5
#  9     2     3     6
# 10     2     1     7
# 11     2     2     8
# 12     2     3     9
r2evans
  • 77,184
  • 4
  • 55
  • 96
  • Dear @r2evans, kindly could you assist in my updated question here? https://stackoverflow.com/questions/63209019/is-there-an-r-code-for-the-following-data-wrangling-and-transformation thanks – Sam Mwenda Aug 02 '20 at 20:55
  • Sry, can't get to it now. (If it doesn't work for you, why is it accepted?) – r2evans Aug 02 '20 at 23:58