How do you add additional rows from multiple columns in R?

Question

My DF looks like this:

ID  V1    V2  V3
A   100  200    
B   100     
C   300     
D   400     
E   223  233   4

I want to transform it in R, such that the multiple 'V' columns come under one another with repeating IDs:

What is the best way of doing this in R or Excel?

Possible duplicate of [Reshaping time series data from wide to tall format (for plotting)](http://stackoverflow.com/questions/1181060/reshaping-time-series-data-from-wide-to-tall-format-for-plotting) — zx8754, Apr 18 '16 at 10:20

score 2 · Accepted Answer · answered Apr 18 '16 at 08:44

2

We can use melt

library(data.table)
setnames(melt(setDT(df), id.var="ID", na.rm=TRUE)[order(ID), -2, with = FALSE], 2, "V1")[]
#   ID  V1
#1:  A 100
#2:  A 200
#3:  B 100
#4:  C 300
#5:  D 400
#6:  E 223
#7:  E 233
#8:  E   4

answered Apr 18 '16 at 08:44

akrun

874,273
37
540
662

1

Exactly what I was looking for! Thanks! – Krupa Kapadia Apr 18 '16 at 08:47

score 2 · Answer 2 · answered Apr 18 '16 at 10:12

2

You can also try tidyr:

library(tidyr)
df <- df %>% gather(new, V1, 2:4) %>% filter(!is.na(V1))

If you don't want the "new" column add this cody (library "dplyr"):

%>% select(id, V1)

answered Apr 18 '16 at 10:12

Tomasz Mikolajczyk

115
2
10

1

gather also has `na.rm=TRUE` – akrun Apr 18 '16 at 10:16
I tried doing this, got an error: Error in filter(., !is.na(V1)) : object 'V1' not found – Krupa Kapadia Apr 18 '16 at 10:28
It should work. Here you have analogical solution with chracter data: [link](http://stackoverflow.com/questions/25045301/removing-incomplete-cases-from-output-of-tidyr-gather-r) – Tomasz Mikolajczyk Apr 18 '16 at 10:43

How do you add additional rows from multiple columns in R?

2 Answers2