Copy and duplicate rows based on condition

Question

My Dataframe looks similar to this

 Date   Key  Value1  Value2
   D1   1A     2
   D1   2A     1      2
   D2   3B     1      3
   D2   1A     3
   D3   2A     5

As you can see, the Keyitself ain't primary, However if we consider Dateand Keytogether, each observation finds it as a primary key. Now I wish to achieve that for every observation where Value1and Value2both are present, duplicate the row and break them into two rows. Something like this:

Date   Key  Value1  Value2  Value
D1     1A     2               2
D1     2A     1       2       1
D1     2A     1       2       2
D2     3B     1       3       1
D2     3B     1       3       3
D3     2A     5               5

Is there anyway I can achieve this? Thanks in advance.

Is the `Value` column you are after? Maybe consider [reshaping wide-to-long](http://stackoverflow.com/questions/2185252)? — zx8754, Jun 05 '18 at 12:08
I tried melt(), the problem is, my no. of columns in the actual dataframe is close to 30 and almost 200,000 rows. Hence melting with the columns got messy. Is there any alternative? — d1r3w0lF, Jun 05 '18 at 12:12

score 1 · Answer 1 · answered Jun 05 '18 at 12:12

You probably want this:

df %>% gather(keys,Value,-Date,-Key) %>% filter(!is.na(Value)) %>% arrange(Date,Key)

  Date Key   keys Value
1   D1  1A Value1     2
2   D1  2A Value1     1
3   D1  2A Value2     2
4   D2  1A Value1     3
5   D2  3B Value1     1
6   D2  3B Value2     3
7   D3  2A Value1     5

score 0 · Answer 2 · answered Jun 05 '18 at 13:52

You could also use unnest

library(tidyverse)
df %>%
  rowwise %>%
  mutate(Value3 = ifelse(is.na(Value2),list(Value1),list(c(Value1,Value2)))) %>%
  unnest

# # A tibble: 7 x 5
#    Date   Key Value1 Value2 Value3
#   <chr> <chr>  <int>  <int>  <int>
# 1    D1    1A      2     NA      2
# 2    D1    2A      1      2      1
# 3    D1    2A      1      2      2
# 4    D2    3B      1      3      1
# 5    D2    3B      1      3      3
# 6    D2    1A      3     NA      3
# 7    D3    2A      5     NA      5

data

df <- read.table(text="
Date   Key  Value1  Value2
D1   1A     2      NA
D1   2A     1      2
D2   3B     1      3
D2   1A     3      NA
D3   2A     5      NA",h=T,strin=F
)

Copy and duplicate rows based on condition

2 Answers2