I have a data.frame
of Quantities and other of Weights and want to multiply Quantities with their corresponding Weights. Can the task be accomplished by selecting only numeric columns? However, I want to keep the other columns in the final output too. Struggling how to get it more efficiently.
library(tidyverse)
df1 <-
tibble(
"A" = paste0("A", 1:10)
, "Q" = 1:10
)
df1
#> # A tibble: 10 x 2
#> A Q
#> <chr> <int>
#> 1 A1 1
#> 2 A2 2
#> 3 A3 3
#> 4 A4 4
#> 5 A5 5
#> 6 A6 6
#> 7 A7 7
#> 8 A8 8
#> 9 A9 9
#> 10 A10 10
df2 <-
tibble(
"A" = paste0("A", 1:10)
, "W" = 11:20
)
df2
#> # A tibble: 10 x 2
#> A W
#> <chr> <int>
#> 1 A1 11
#> 2 A2 12
#> 3 A3 13
#> 4 A4 14
#> 5 A5 15
#> 6 A6 16
#> 7 A7 17
#> 8 A8 18
#> 9 A9 19
#> 10 A10 20
select(df1, -A) * select(df2, -A)
#> Q
#> 1 11
#> 2 24
#> 3 39
#> 4 56
#> 5 75
#> 6 96
#> 7 119
#> 8 144
#> 9 171
#> 10 200
df1*df2
#> Error in FUN(left, right): non-numeric argument to binary operator
Edited
My real data sets has many columns, so looking for a more generic solution.
CodePudding user response:
library(tidyverse)
df1 <-
tibble(
"A" = paste0("A", 1:10)
, "Q" = 1:10
)
df2 <-
tibble(
"A" = paste0("A", 1:10)
, "W" = 11:20
)
df1 |>
mutate(Q*df2$W)
#> # A tibble: 10 x 3
#> A Q `Q * df2$W`
#> <chr> <int> <int>
#> 1 A1 1 11
#> 2 A2 2 24
#> 3 A3 3 39
#> 4 A4 4 56
#> 5 A5 5 75
#> 6 A6 6 96
#> 7 A7 7 119
#> 8 A8 8 144
#> 9 A9 9 171
#> 10 A10 10 200
Created on 2022-02-25 by the reprex package (v2.0.1)
If df1$A == df2$A
then you can use left_join
and mutate as well.
CodePudding user response:
Another possible solution, using inner_join
:
library(tidyverse)
df1 <-
tibble(
"A" = paste0("A", 1:10)
, "Q" = 1:10
)
df2 <-
tibble(
"A" = paste0("A", 1:10)
, "W" = 11:20
)
df1 %>%
inner_join(df2) %>%
mutate(T = Q*W)
#> Joining, by = "A"
#> # A tibble: 10 × 4
#> A Q W T
#> <chr> <int> <int> <int>
#> 1 A1 1 11 11
#> 2 A2 2 12 24
#> 3 A3 3 13 39
#> 4 A4 4 14 56
#> 5 A5 5 15 75
#> 6 A6 6 16 96
#> 7 A7 7 17 119
#> 8 A8 8 18 144
#> 9 A9 9 19 171
#> 10 A10 10 20 200
CodePudding user response:
Using base R
:
data.frame(A = df1$A, variable = df1$Q * df2$W)
A variable
1 A1 11
2 A2 24
3 A3 39
4 A4 56
5 A5 75
6 A6 96
7 A7 119
8 A8 144
9 A9 171
10 A10 200
Using dplyr
:
library(dplyr)
mutate(df1, variable = Q * df2$W) %>% select(-Q) # select(-Q) removes the old Q variable
# A tibble: 10 x 2
A variable
<chr> <int>
1 A1 11
2 A2 24
3 A3 39
4 A4 56
5 A5 75
6 A6 96
7 A7 119
8 A8 144
9 A9 171
10 A10 200
In case you want to keep all the columns in the final product, you can do:
library(dplyr)
left_join(df1, df2, by = "A") %>%
mutate(variable = Q * W)
# A tibble: 10 x 4
A Q W variable
<chr> <int> <int> <int>
1 A1 1 11 11
2 A2 2 12 24
3 A3 3 13 39
4 A4 4 14 56
5 A5 5 15 75
6 A6 6 16 96
7 A7 7 17 119
8 A8 8 18 144
9 A9 9 19 171
10 A10 10 20 200