Transforming a dataframe by "multiplying" a column's elements by the names of the other columns

Question

An example is below. How can I transform a dataframe df with column names to the form of df.transformed below?

> df <- data.frame("names" = c("y1", "y2"), "x1" = 1:2, "x2" = 4:5)
> df
  names x1 x2
1    y1  1  4
2    y2  2  5


> df.transformed <- data.frame("y1x1" = 1, "y1x2" =4, "y2x1" = 2, "y2x2" = 5)
> df.transformed
  y1x1 y1x2 y2x1 y2x2
1    1    4    2    5

Does `unlist(df[-1])` suffice? – Sotos Nov 22 '19 at 15:00 — Sotos, Nov 22 '19 at 15:00
No the names in totality are needed. – its.me.adam Nov 22 '19 at 16:55 — its.me.adam, Nov 22 '19 at 16:55

JDG · Answer 1 · 2019-11-22T14:58:28.570

4

Code

require(data.table); setDT(df)

dt = melt(df, id.vars = 'names')[, col := paste0(variable, names)]
out = dt$value; names(out) = dt$col

Result

> data.frame(t(out))

x1y1 x1y2 x2y1 x2y2 
   1    2    4    5

edited Nov 22 '19 at 14:58

answered Nov 22 '19 at 14:47

JDG

1,342
8
18

The output is not what the OP expects. – slava-kohut Nov 22 '19 at 14:58
Eh, technically you are right, however I doubt that the column ordering (xnyn or ynxn) matters strongly in this particular context. – JDG Nov 22 '19 at 15:04

slava-kohut · Answer 2 · 2019-11-22T14:48:05.347

You can achieve this in base R. This should work for any data frame size. The idea is combine Reduce with outer to build the data frame column names.

df <- data.frame("names" = c("y1", "y2"), "x1" = 1:2, "x2" = 4:5)

df_names <- outer(df[,1], names(df[,-1]), paste0)
df.transformed <- as.data.frame(matrix(,ncol = nrow(df)*ncol(df[,-1]), nrow = 0))
names(df.transformed) <- Reduce(`c`,t(df_names))
df.transformed[1,] <- Reduce(`c`,t(df[-1]))

Output

#  y1x1 y1x2 y2x1 y2x2
#    1    4    2    5

score 1 · Accepted Answer · answered Nov 22 '19 at 15:26

You can do this in one line with the new tidyr::pivot_wider. Setting multiple columns for values means names will get pasted together for assignment.

library(tidyr)

pivot_wider(df, names_from = names, values_from = c(x1, x2), names_sep = "")
#> # A tibble: 1 x 4
#>    x1y1  x1y2  x2y1  x2y2
#>   <int> <int> <int> <int>
#> 1     1     2     4     5

However, the column names ("x1", "x2") come first. If you need to swap the "x" and "y" components of the names, you can do regex replacement with dplyr::rename_all.

df %>%
  pivot_wider(names_from = names, values_from = c(x1, x2), names_sep = "") %>%
  dplyr::rename_all(gsub, pattern = "(x\\d+)(y\\d+)", replacement = "\\2\\1")
#> # A tibble: 1 x 4
#>    y1x1  y2x1  y1x2  y2x2
#>   <int> <int> <int> <int>
#> 1     1     2     4     5

Transforming a dataframe by "multiplying" a column's elements by the names of the other columns

3 Answers3