R: Create new data frame or Matrix from two data frames-CodePudding

I have two data frames in R

df1 <- data.frame(Name = c("RIS_001", "RIS_002", "RIS_003", "RIS_004", "RIS_005")) %>% 
mutate(Value = c(5, 3, 8, 6, 9))

df2 <- data.frame(Prod = c("RIS_010", "RIS_011", "RIS_012", "RIS_013", "RIS_014", "RIS_015", "RIS_016", "RIS_017")) %>%
  mutate(Value = c(54, 87, 92, 48, 66, 35, 12, 18))

I want to create two new data frames from them as in below images. How to accomplish this in R?

new_df1, concatenation of Row & Column or Column & Row separated by a special character "|"

new_df2, mean of each intersecting row & column Ex: Mean for RIS_001 & RIS_010 = mean(5 54) = 29.5

Appreciate any help. Thank you!

CodePudding user response：

You can outer for the first df:

o <- outer(df1$Name, df2$Prod, paste, sep = " | ")
rownames(o) <- df1$Name
colnames(o) <- df2$Prod

And for the second df:

m <- outer(df1$Value, df2$Value, \(x, y) (x   y) / 2)
rownames(m) <- df1$Name
colnames(m) <- df2$Prod

        RIS_010 RIS_011 RIS_012 RIS_013 RIS_014 RIS_015 RIS_016 RIS_017
RIS_001    29.5    46.0    48.5    26.5    35.5    20.0     8.5    11.5
RIS_002    28.5    45.0    47.5    25.5    34.5    19.0     7.5    10.5
RIS_003    31.0    47.5    50.0    28.0    37.0    21.5    10.0    13.0
RIS_004    30.0    46.5    49.0    27.0    36.0    20.5     9.0    12.0
RIS_005    31.5    48.0    50.5    28.5    37.5    22.0    10.5    13.5

CodePudding user response：

I might be mis-interpreting your question, but this seems to me to be what you are asking?

library(tidyverse)

#vals sep by bar
outer(
pivot_wider(df1, names_from = Name, values_from = Value),
pivot_wider(df2, names_from = Prod, values_from = Value),
\(x, y) paste(x, y, sep = "|")
)
#>         RIS_010 RIS_011 RIS_012 RIS_013 RIS_014 RIS_015 RIS_016 RIS_017
#> RIS_001 "5|54"  "5|87"  "5|92"  "5|48"  "5|66"  "5|35"  "5|12"  "5|18" 
#> RIS_002 "3|54"  "3|87"  "3|92"  "3|48"  "3|66"  "3|35"  "3|12"  "3|18" 
#> RIS_003 "8|54"  "8|87"  "8|92"  "8|48"  "8|66"  "8|35"  "8|12"  "8|18" 
#> RIS_004 "6|54"  "6|87"  "6|92"  "6|48"  "6|66"  "6|35"  "6|12"  "6|18" 
#> RIS_005 "9|54"  "9|87"  "9|92"  "9|48"  "9|66"  "9|35"  "9|12"  "9|18"

#vals mean
outer(
pivot_wider(df1, names_from = Name, values_from = Value),
pivot_wider(df2, names_from = Prod, values_from = Value),
\(x, y) (as.numeric(x) as.numeric(y))/2
)
#>         RIS_010 RIS_011 RIS_012 RIS_013 RIS_014 RIS_015 RIS_016 RIS_017
#> RIS_001    29.5    46.0    48.5    26.5    35.5    20.0     8.5    11.5
#> RIS_002    28.5    45.0    47.5    25.5    34.5    19.0     7.5    10.5
#> RIS_003    31.0    47.5    50.0    28.0    37.0    21.5    10.0    13.0
#> RIS_004    30.0    46.5    49.0    27.0    36.0    20.5     9.0    12.0
#> RIS_005    31.5    48.0    50.5    28.5    37.5    22.0    10.5    13.5