How to transform wide to long data

[Fenny]

Hi Everyone,

I need help in transforming multiple sets of wide data to long format.
My Data looks like this: The top table is the original data and I need to transform to the below table.

Any help is appreciated, since I am a beginner in R Studio and have looked into few examples using pivot_longer, reshape but still has not got what I wanted the data to look like.

Any help is appreciated. Thank you.

[gueyenono]

Hi @Fenny and welcome to the RStudio Community

Transforming wide to long data is actually fairly simple to do in R. In order to better serve you, we would need you to make your question reproducible. That is to say that rather than a picture of your dataset, it would help us get the actual data which you can share with us this way:

• import the data to your current R session (in a variable called my_data for example)
• execute: dput(my_data)
• copy and paste the output here.

You may want to look at this great article to learn how to ask a reproducible question:FAQ: How to do a minimal reproducible example ( reprex ) for beginners

Having said that, if you want to figure it out yourself, you can look into the pivot_longer() function in the tidyr package. But if you share your data, we will be able to provide you with actual code.

[Fenny]

Thanks @gueyenono

This is mydata

''''''''
data.frame(
stringsAsFactors = FALSE,
id = c(1, 2, 3, 4, 5, 6),
domicile = c("Jakarta","Depok",
"Jakarta","Jakarta","Jakarta","Jakarta"),
age = c(26, 19, 23, 25, 35, 36),
gender = c("P", "P", "P", "L", "P", "L"),
ecommerce_industry = c("Tidak ada satupun",
"Tidak ada satupun","Tidak ada satupun","Tidak ada satupun",
"Tidak ada satupun","Tidak ada satupun"),
Awareness_BrandA = c(1, 1, 1, 1, 1, 1),
Awareness_BrandB = c(1, 1, 1, 1, 1, 1),
Awareness_BrandC = c(1, 1, 1, 1, 1, 1),
used_BrandA = c(0, 1, 1, 0, 0, 0),
used_BrandB = c(1, 1, 1, 1, 1, 1),
used_BrandC = c(0, 0, 1, 1, 0, 0),
usedP1M_BrandA = c(0, 1, 1, 0, 0, 0),
usedP1M_BrandB = c(1, 1, 1, 1, 1, 1),
usedP1M_BrandC = c(0, 0, 1, 0, 0, 0)
)

''''''''''''
I want the data to be arranged as follows:

id, domicile, age, gender, ecommerce_industry, Brand, Awarenss, Used, UsedP1M

So all will be grouped under Brand.. The Brand will be BrandA, BrandB, BrandC..

If you could give me answers in code, it will be much appreciated..
I have been trying to figure this out since yesterday but without much success.. so any help is welcome.

Thank you.

[gueyenono]

Your example is a bit advanced, but you can learn more above using the pivot_longer() function using these resources:

• video: https://www.youtube.com/watch?v=6Y-fxqLsNDI
• article: https://thatdatatho.com/2020/03/28/tidyrs-pivot_longer-and-pivot_wider-examples-tidytuesday-challenge/

This is how to get your desired result:

library(tidyr)

my_data_wide <- data.frame(
  stringsAsFactors = FALSE,
  id = c(1, 2, 3, 4, 5, 6),
  domicile = c("Jakarta","Depok",
               "Jakarta","Jakarta","Jakarta","Jakarta"),
  age = c(26, 19, 23, 25, 35, 36),
  gender = c("P", "P", "P", "L", "P", "L"),
  ecommerce_industry = c("Tidak ada satupun",
                         "Tidak ada satupun","Tidak ada satupun","Tidak ada satupun",
                         "Tidak ada satupun","Tidak ada satupun"),
  Awareness_BrandA = c(1, 1, 1, 1, 1, 1),
  Awareness_BrandB = c(1, 1, 1, 1, 1, 1),
  Awareness_BrandC = c(1, 1, 1, 1, 1, 1),
  used_BrandA = c(0, 1, 1, 0, 0, 0),
  used_BrandB = c(1, 1, 1, 1, 1, 1),
  used_BrandC = c(0, 0, 1, 1, 0, 0),
  usedP1M_BrandA = c(0, 1, 1, 0, 0, 0),
  usedP1M_BrandB = c(1, 1, 1, 1, 1, 1),
  usedP1M_BrandC = c(0, 0, 1, 0, 0, 0)
)

pivot_longer(my_data_wide, 
             cols = matches("Brand"),
             names_to = c(".value", "Brand"),
             names_pattern = "(.*)_(.*)")

# A tibble: 18 x 9
      id domicile   age gender ecommerce_industry Brand  Awareness  used usedP1M
   <dbl> <chr>    <dbl> <chr>  <chr>              <chr>      <dbl> <dbl>   <dbl>
 1     1 Jakarta     26 P      Tidak ada satupun  BrandA         1     0       0
 2     1 Jakarta     26 P      Tidak ada satupun  BrandB         1     1       1
 3     1 Jakarta     26 P      Tidak ada satupun  BrandC         1     0       0
 4     2 Depok       19 P      Tidak ada satupun  BrandA         1     1       1
 5     2 Depok       19 P      Tidak ada satupun  BrandB         1     1       1
 6     2 Depok       19 P      Tidak ada satupun  BrandC         1     0       0
 7     3 Jakarta     23 P      Tidak ada satupun  BrandA         1     1       1
 8     3 Jakarta     23 P      Tidak ada satupun  BrandB         1     1       1
 9     3 Jakarta     23 P      Tidak ada satupun  BrandC         1     1       1
10     4 Jakarta     25 L      Tidak ada satupun  BrandA         1     0       0
11     4 Jakarta     25 L      Tidak ada satupun  BrandB         1     1       1
12     4 Jakarta     25 L      Tidak ada satupun  BrandC         1     1       0
13     5 Jakarta     35 P      Tidak ada satupun  BrandA         1     0       0
14     5 Jakarta     35 P      Tidak ada satupun  BrandB         1     1       1
15     5 Jakarta     35 P      Tidak ada satupun  BrandC         1     0       0
16     6 Jakarta     36 L      Tidak ada satupun  BrandA         1     0       0
17     6 Jakarta     36 L      Tidak ada satupun  BrandB         1     1       1
18     6 Jakarta     36 L      Tidak ada satupun  BrandC         1     0       0

[Fenny]

Thank you.. This works.

and thanks for recommending resources. I'll make sure I learn from them.

[gueyenono]

@Fenny Concerning marking my answer as the solution if it helped you. This will make sure that the answer will help people who will visit this thread in the future.