R - Insert values from dataframe in row of another dataframe with column names as ID

Question

I have a dataframe (DF1) with columns: Country, Year, Indicator 1, Indicator 2, .. etc.

I have another dataframe (DF2) with columns: Indicators, Weight

I now want to multiply each Indicator of DF1 with the value in the Weight column of DF2.

E.g. if in DF2 Indicator 1 has Weight = 0.5, and Indicator 2 has Weight =0.2, and in DF1 I have a row where Country=Brazil, Year=2015, Indicator 1 = 0.34, Indicator 2 = 0.76,..

I'd like to end up with a dataframe like this:

Country=Brazil, Year=2015, Indicator 1 = 0.34 x 0.5, Indicator 2 = 0.76 x 0.2, etc

where x= multiply

I am very thankful for any kind of help! :)

Welcome to SO! Could you provide a [reproducible example](https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example) of your data and some example output in code? — Rory S, Jun 22 '21 at 14:37

score 1 · Accepted Answer · answered Jun 22 '21 at 15:04

You perhaps need something like this?

Let's create some example first

library(tidyverse)

df <- data.frame(
  stringsAsFactors = FALSE,
           country = c("ABC", "BCD", "ABC", "BCD", "CDE"),
              year = c(1L, 1L, 2L, 2L, 3L),
       indicator_1 = c(5L, 12L, 8L, 7L, 6L),
       indicator_2 = c(13L, 10L, 6L, 5L, 8L),
       indicator_3 = c(9L, 7L, 4L, 8L, 12L)
      )
df
#>   country year indicator_1 indicator_2 indicator_3
#> 1     ABC    1           5          13           9
#> 2     BCD    1          12          10           7
#> 3     ABC    2           8           6           4
#> 4     BCD    2           7           5           8
#> 5     CDE    3           6           8          12

df_indicators <- data.frame(
  stringsAsFactors = FALSE,
                                Ind = c("indicator_1","indicator_2",
                                        "indicator_3"),
           weights = c(2L, 3L, 4L)
                 )
df_indicators
#>           Ind weights
#> 1 indicator_1       2
#> 2 indicator_2       3
#> 3 indicator_3       4

Do this

df %>% mutate(across(starts_with('indicator'), ~ . * df_indicators$weights[match(cur_column(), df_indicators$Ind)]))

#>   country year indicator_1 indicator_2 indicator_3
#> 1     ABC    1          10          39          36
#> 2     BCD    1          24          30          28
#> 3     ABC    2          16          18          16
#> 4     BCD    2          14          15          32
#> 5     CDE    3          12          24          48

^{Created on 2021-06-22 by the reprex package (v2.0.0)}

score 1 · Answer 2 · answered Jun 22 '21 at 16:27

We may also use deframe to create a named vector

library(dplyr)
library(tibble)
df %>% 
   mutate(across(starts_with('indicator'), 
       ~ deframe(df_indicators)[cur_column()] * .))

-output

country year indicator_1 indicator_2 indicator_3
1     ABC    1          10          39          36
2     BCD    1          24          30          28
3     ABC    2          16          18          16
4     BCD    2          14          15          32
5     CDE    3          12          24          48

data

df <- structure(list(country = c("ABC", "BCD", "ABC", "BCD", "CDE"), 
    year = c(1L, 1L, 2L, 2L, 3L), indicator_1 = c(5L, 12L, 8L, 
    7L, 6L), indicator_2 = c(13L, 10L, 6L, 5L, 8L), indicator_3 = c(9L, 
    7L, 4L, 8L, 12L)), class = "data.frame", row.names = c(NA, 
-5L))

df_indicators <- structure(list(Ind = c("indicator_1", 
  "indicator_2", "indicator_3"
), weights = 2:4), class = "data.frame", row.names = c(NA, -3L
))

Do this deframe will work even when columns are not sequential as those in df_indicators? — AnilGoyal, Jun 22 '21 at 16:33
@AnilGoyal it is matching at the column names which is a string — akrun, Jun 22 '21 at 16:34
Oh, fantastic. It's learning new things everytime with you. +1 already — AnilGoyal, Jun 22 '21 at 16:35

R - Insert values from dataframe in row of another dataframe with column names as ID

2 Answers2

data