Tall format of data frame with multiple (x,y) pairs

user_12107084 0
Viewed 69

I have a data frame (actually I prefer data.table) with columns of multiple pairs of (x,y) coordinates and corresponding values alpha, something like follows:

    > data.frame(x_1 = 1:5, y_1 = 6:10, 
                 x_2 = 11:15, y_2 = 16:20, 
                 x_3 = 21:25, y_3=26:30, 
                 alpha = seq(0.2,1,0.2))
      x_1 y_1 x_2 y_2 x_3 y_3 alpha
    1   1   6  11  16  21  26   0.2
    2   2   7  12  17  22  27   0.4
    3   3   8  13  18  23  28   0.6
    4   4   9  14  19  24  29   0.8
    5   5  10  15  20  25  30   1.0

I need to organise it into a long format such that there is an x and a y column, where a row of coordinates from df is stacked to be three pairs on top of one another; a column for alpha which is duplicated for each pairing and; a column for the corresponding pair index, as follows:

    x  y alpha index
1   1  6   0.2     1
2  11 16   0.2     2
3  21 26   0.2     3
4   2  7   0.4     1
5  12 17   0.4     2
6  22 27   0.4     3
7   3  8   0.6     1
8  13 18   0.6     2
9  23 28   0.6     3
10  4  9   0.8     1
11 14 19   0.8     2
12 24 29   0.8     3
13  5 10   1.0     1
14 15 20   1.0     2
15 25 30   1.0     3

I have tried to use gather without much success - trying to melt by pairs of columns and then duplicating the alpha values caused me grief. I then resorted to a for loop through the rows of df, compiling a (pre-allocated) vector of values x, y and alpha with each iteration, but even with the pre-allocation this was horrendously slow compared to a similar operation in python.

In practice I have about 20,000-40,000 rows, many more "constant" columns like alpha and something like 3-5 pair indices.

Apologies if there has been a similar question - I couldn't find one and really struggle wording questions about quite specific data manipulations. Any help is greatly appreciated!

tidyr dplyr dataframe r
3 Answers

gather has been superseded by pivot_longer. I think this gives you what you want.

df %>% 
  pivot_longer(
    c(starts_with("x"), starts_with("y")),
    names_pattern="(.)_(.)",
    names_to=c(".value", "index")
  )
# A tibble: 15 x 4
   alpha index     x     y
   <dbl> <chr> <int> <int>
 1   0.2 1         1     6
 2   0.2 2        11    16
 3   0.2 3        21    26
 4   0.4 1         2     7
 5   0.4 2        12    17
 6   0.4 3        22    27
 7   0.6 1         3     8
 8   0.6 2        13    18
 9   0.6 3        23    28
10   0.8 1         4     9
11   0.8 2        14    19
12   0.8 3        24    29
13   1   1         5    10
14   1   2        15    20
15   1   3        25    30
user_134348710

Does this work as expected?

df %>%
  pivot_longer(cols = -alpha, names_to = c("col", "index"), names_sep = "_") %>%
  pivot_wider(names_from = col, values_from = value)

Output

# A tibble: 15 x 4
   alpha index     x     y
   <dbl> <chr> <int> <int>
 1   0.2 1         1     6
 2   0.2 2        11    16
 3   0.2 3        21    26
 4   0.4 1         2     7
 5   0.4 2        12    17
 6   0.4 3        22    27
 7   0.6 1         3     8
 8   0.6 2        13    18
 9   0.6 3        23    28
10   0.8 1         4     9
11   0.8 2        14    19
12   0.8 3        24    29
13   1   1         5    10
14   1   2        15    20
15   1   3        25    30
user_68518250

Here is another pivot_longer approach:

  1. pivot_longer without alpha only columns start with x
  2. use window function lead
  3. remove every second row with filter
  4. Create index
library(dplyr)
library(tidyr)
df %>% 
  pivot_longer(c(-alpha, starts_with("x")),
               names_to = "names.x",
               values_to = "x"
               ) %>% 
  mutate(y = lead(x)) %>% 
  filter(row_number() %% 2 != 0) %>%  ## Delete even-rows
  select(-names.x) %>% 
  mutate(index = rep(1:3, length.out = n()))

 alpha     x     y index
   <dbl> <int> <int> <int>
 1   0.2     1     6     1
 2   0.2    11    16     2
 3   0.2    21    26     3
 4   0.4     2     7     1
 5   0.4    12    17     2
 6   0.4    22    27     3
 7   0.6     3     8     1
 8   0.6    13    18     2
 9   0.6    23    28     3
10   0.8     4     9     1
11   0.8    14    19     2
12   0.8    24    29     3
13   1       5    10     1
14   1      15    20     2
15   1      25    30     3
user_133216470
Related