Dataframe Column is an Offset of Multiple Column Values - Elegant solution desired

user_14469716 0
Viewed 44

I am in search of an elegant solution that produces a column of values that are column offsets of a 'column offset' column = 'relative_column_position.' The desired answer is provided (radio).

My actual data consists of thousands of rows with ~300 different column positions denoted in 'relative_column_position,' so a hand-solution such as this is not in the cards.

enter image description here

gaga <- tibble(relative_column_position = c(rep(1,3), rep(2,6), rep(3,3) ),
              col_1 = 1:12,
              col_2 = 13:24,
              col_3 = 25:36
        )
gaga

radio <- tibble( c(gaga$col_1[1:3], 
                   gaga$col_2[4:9],
                   gaga$col_3[10:12])
                 )

radio
tibble r
3 Answers

Here is a base R solution in two steps.

library(tibble)

gaga <- tibble(relative_column_position = c(rep(1,3), rep(2,6), rep(3,3) ),
               col_1 = 1:12,
               col_2 = 13:24,
               col_3 = 25:36
)

radio <- tibble(c(gaga$col_1[1:3], 
                   gaga$col_2[4:9],
                   gaga$col_3[10:12])
)

rcp <- split(seq_along(gaga$relative_column_position), gaga$relative_column_position)
unlist(mapply(\(x, i) x[i], gaga[-1], rcp))
#> col_11 col_12 col_13 col_21 col_22 col_23 col_24 col_25 col_26 col_31 col_32 
#>      1      2      3     16     17     18     19     20     21     34     35 
#> col_33 
#>     36

Created on 2022-05-21 by the reprex package (v2.0.1)

As a tibble:

rcp <- split(seq_along(gaga$relative_column_position), gaga$relative_column_position)
radio <- tibble(rcp = unlist(mapply(\(x, i) x[i], gaga[-1], rcp)))
rm(rcp)
radio
#> # A tibble: 12 × 1
#>      rcp
#>    <int>
#>  1     1
#>  2     2
#>  3     3
#>  4    16
#>  5    17
#>  6    18
#>  7    19
#>  8    20
#>  9    21
#> 10    34
#> 11    35
#> 12    36

Created on 2022-05-21 by the reprex package (v2.0.1)

user_82454060 
df |> 
  mutate(rel = apply(df, 1, \(x) x[colnames(df)[x["relative_col"]]]  ))

to apply to your df example:

gaga |> 
  mutate(rel = apply(gaga, 1, \(x) x[colnames(gaga)[x["relative_column_position"] + 1]]  ))

Assuming you have a relative column to map over, you can use apply and

mutate

Admin1

Base R answer using matrix subsetting -

gaga <- data.frame(gaga)
result <- data.frame(value = gaga[cbind(seq_len(nrow(gaga)), 
                                        gaga$relative_column_position + 1)])

result

#   value
#1      1
#2      2
#3      3
#4     16
#5     17
#6     18
#7     19
#8     20
#9     21
#10    34
#11    35
#12    36

gaga$relative_column_position + 1 because the subsetting starts from the 2nd column in the dataset. So when gaga$relative_column_position is 1, we actually want to subset data from 2nd column in gaga dataset.

user_39629140
Related