Splitting row into two rows-CodePudding

I'm recently working with data as

year <- c('1990', '1990', '1994', '2000', '2012')
n1 <- c(30, 70, 20, 31, 10)
n2 <- c(40, 60, 20, 21, 8)
r1 <- c(0, 9, 3, 4, 4)
r2 <- c(3, 9, 1, 2, 0)
data <- data.frame(year, n1, n2, r1, r2)

I want to split each row into two rows, where row1 includes n1 and r1 and row2 includes n2 and r2.

The desired result would be like this;

> data
   year type  n r
1  1990    0 30 0
2  1990    1 40 3
3  1990    0 70 9
4  1990    1 60 9
5  1994    0 20 3
6  1994    1 20 1
7  2000    0 31 4
8  2000    1 21 2
9  2012    0 10 4
10 2012    1  8 0

CodePudding user response：

Updated for a repeating year.

Try this:

year <- c('1990', '1990', '1994', '2000', '2012')
n1 <- c(30, 70, 20, 31, 10)
n2 <- c(40, 60, 20, 21, 8)
r1 <- c(0, 9, 3, 4, 4)
r2 <- c(3, 9, 1, 2, 0)
data <- data.frame(year, n1, n2, r1, r2)

library(tidyverse)

data |> 
  group_by(year) |> 
  mutate(row = row_number()) |> 
  pivot_longer(-c(row, year), names_to = c("prefix", "type"), names_pattern = "(\\w)(\\d)") |> 
  pivot_wider(names_from = prefix, values_from = value)
#> # A tibble: 10 × 5
#> # Groups:   year [4]
#>    year    row type      n     r
#>    <chr> <int> <chr> <dbl> <dbl>
#>  1 1990      1 1        30     0
#>  2 1990      1 2        40     3
#>  3 1990      2 1        70     9
#>  4 1990      2 2        60     9
#>  5 1994      1 1        20     3
#>  6 1994      1 2        20     1
#>  7 2000      1 1        31     4
#>  8 2000      1 2        21     2
#>  9 2012      1 1        10     4
#> 10 2012      1 2         8     0

^{Created on 2022-05-09 by the reprex package (v2.0.1)}

CodePudding user response：

When there is no need for a specific order rep and c could be used.

with(data, data.frame(year, type = rep(0:1, each=length(year)),
                      n = c(n1, n2), r = c(r1, r2)))
#   year type  n r
#1  1990    0 30 0
#2  1990    0 70 9
#3  1994    0 20 3
#4  2000    0 31 4
#5  2012    0 10 4
#6  1990    1 40 3
#7  1990    1 60 9
#8  1994    1 20 1
#9  2000    1 21 2
#10 2012    1  8 0

Or keeping the order:

with(data, data.frame(year, type = rep(0:1, each=length(year)),
                      n = c(n1, n2), r = c(r1, r2))[
             c(matrix(seq(1:(2*length(year))), 2, byrow=TRUE)),])
#   year type  n r
#1  1990    0 30 0
#6  1990    1 40 3
#2  1990    0 70 9
#7  1990    1 60 9
#3  1994    0 20 3
#8  1994    1 20 1
#4  2000    0 31 4
#9  2000    1 21 2
#5  2012    0 10 4
#10 2012    1  8 0

Or another variant repeating the original datasets and updating the content.

. <- data[rep(seq_len(nrow(data)), each=2),]
.$n1[c(FALSE, TRUE)] <- .$n2[c(TRUE, FALSE)]
.$r1[c(FALSE, TRUE)] <- .$r2[c(TRUE, FALSE)]
with(., data.frame(year, type = 0:1, n=n1, r=r1))
#   year type  n r
#1  1990    0 30 0
#2  1990    1 40 3
#3  1990    0 70 9
#4  1990    1 60 9
#5  1994    0 20 3
#6  1994    1 20 1
#7  2000    0 31 4
#8  2000    1 21 2
#9  2012    0 10 4
#10 2012    1  8 0

CodePudding user response：

Try this:

data %>% 
  pivot_longer(matches("\\d"), 
               names_to = c("prefix", "type"), 
               names_pattern = "(.)(.)") %>% 
  pivot_wider(names_from = "prefix", 
              values_from = "value") %>%
  unnest(c(n,r))
# A tibble: 10 × 4
   year  type      n     r
   <chr> <chr> <dbl> <dbl>
 1 1990  1        30     0
 2 1990  1        70     9
 3 1990  2        40     3
 4 1990  2        60     9
 5 1994  1        20     3
 6 1994  2        20     1
 7 2000  1        31     4
 8 2000  2        21     2
 9 2012  1        10     4
10 2012  2         8     0

CodePudding user response：

Another way to get the result is the following combination of functions from base R and tidyverse:

data %>% 
  reshape(direction = "long", varying = list(c("n1", "n2"), c("r1", "r2"))) %>%  
  select(-id) %>% 
  rename(type = time, n = n1, r= r1) %>%  
  mutate(type = if_else(type == 1, 0, 1)) %>% 
  `rownames<-`(1:nrow(.)) %>%  
  arrange(year)

   year type  n r
1  1990    0 30 0
2  1990    0 70 9
3  1990    1 40 3
4  1990    1 60 9
5  1994    0 20 3
6  1994    1 20 1
7  2000    0 31 4
8  2000    1 21 2
9  2012    0 10 4
10 2012    1  8 0