Summarizing the data in a data table

Question

Hi I have the following input.

+------+----+----+
| CODE | R1 | R2 |
+------+----+----+
| A    |  0 |  1 |
| B    |  1 |  1 |
| C    |  0 |  0 |
+------+----+----+

I need the output like below.

+------+------+-------+
| CODE | CODE | VALUE |
+------+------+-------+
| A    | R1   |     0 |
| A    | R2   |     1 |
| B    | R1   |     1 |
| B    | R2   |     1 |
| C    | R1   |     0 |
| C    | R2   |     0 |
+------+------+-------+

Please note that the regions such as R1 and R2, there are many regions in the actual data like R3,R4,R5 and so on. For simplicity, I put only R1 and R2.

Thanks in advance for your help!

Hi @avinash0513, please check the formatting once my edit is accepted & you can follow that if you come back with more questions :) — massisenergy, Apr 16 '20 at 17:14
this will help you in future questions: [How to make a great R reproducible example](https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example), to modify your question, with a smaller sample taken from your data (check `?dput()`). — massisenergy, Apr 16 '20 at 17:25

massisenergy · Answer 1 · 2020-04-16T17:29:01.433

Pretty classic scenario to transform data from wide to long format. And straight forward to use pivot-longer from tidyr:

df <- read.table(stringsAsFactors = F, header = T, text = "
           CODE R1  R2
           A    0   1
           B    1   1
           C    0   0");

dfTarget <- read.table(stringsAsFactors = F, header = T, text = "
 CODE  CODE  VALUE 
 A     R1        0 
 A     R2        1 
 B     R1        1 
 B     R2        1 
 C     R1        0 
 C     R2        0");

Now, the code that you want:

df %>% pivot_longer(cols = c("R1","R2"), values_to = "VALUE")
# A tibble: 6 x 3
  CODE  name  VALUE
  <chr> <chr> <int>
1 A     R1        0
2 A     R2        1
3 B     R1        1
4 B     R2        1
5 C     R1        0
6 C     R2        0

colnames(df)[1:2] <- c("CODE1", "CODE2"); #to change the column names, dataframe 
#with duplicate column names is not a good idea.

You can find a comprehensive overview of pivoting here @ https://tidyr.tidyverse.org/articles/pivot.html

score 0 · Accepted Answer · answered Apr 16 '20 at 18:50

0

With data.table, we can use melt

library(data.table)
melt(setDT(df1), id.var = 'CODE')

answered Apr 16 '20 at 18:50

akrun

874,273
37
540
662

Summarizing the data in a data table

2 Answers2