crossprod of values with the same value in one column-CodePudding

I have a dataframe that looks something like this (very simplified):

Fund     Weight        Value1       Value2
A         0.3           83            100
A         0.2           62            150   
A         0.4           70            70  
A         0.1           80            180  
B         0.15          87            120 
B         0.25          91            80   
B         0.5           76            170  
B         0.2           12            110

What I am trying to do is to calculate the cross-product between the columns Weight and other columns for each Fund.

Eventually, I want to have a data frame that looks something like this:

Fund     Value1       Value2
A          73.3         106
B          76.2         145

So far, I have only managed to calculate the cross-product with a separate data frame for each Fund which is not a viable option as the data frame is too big.

Any help would be very appreciated.

CodePudding user response：

df %>%
  group_by(Fund) %>%
  summarise(across(Value1:Value2, weighted.mean, Weight), .groups = 'drop')

# A tibble: 2 x 3
  Fund  Value1 Value2
  <chr>  <dbl>  <dbl>
1 A       73.3   106 
2 B       69.3   132.

df %>%
  group_by(Fund) %>%
  summarise(vals = crossprod(Weight, as.matrix(across(Value1:Value2))))

or even:

df %>%
   group_by(Fund) %>%
   summarise(across(Value1:Value2, ~c(.x %*% Weight)), .groups = 'drop')

# A tibble: 2 x 3
  Fund  Value1 Value2
  <chr>  <dbl>  <dbl>
1 A       73.3    106
2 B       76.2    145

CodePudding user response：

We could multiply by the 'Value' columns with Weight and get the sum after grouping by 'Fund'

library(dplyr)
df1 %>%
  group_by(Fund) %>% 
  summarise(across(starts_with("Value"), ~ sum(.x * Weight)), .groups = "drop")

-output

# A tibble: 2 × 3
  Fund  Value1 Value2
  <chr>  <dbl>  <dbl>
1 A       73.3    106
2 B       76.2    145

Or may use fsum which is more easier

library(collapse)
fsum(gvr(df1, "^Value"), g = df1$Fund, w = df1$Weight)
  Value1 Value2
A   73.3    106
B   76.2    145

CodePudding user response：

A base R code

> aggregate(df[-(1:2)]*df$Weight, df[1],sum)
  Fund Value1 Value2
1    A   73.3    106
2    B   76.2    145