'Multiply Columns of two different dataframes
How to efficiently multiply columns of two different data frames with same number of rows but different number of columns.
I have two data sets Volumes and prices and I want to multiply each volume column by each price column such that the resulting data frame would have nXm columns (n is ncols
in first data frame and m is ncols
in second data frame).
set.seed(159) # for reproducibility
volumes <- as.data.frame(cbind(Year = 2000:2004,
matrix(round(runif(25, 50, 100), 0),
nrow = 5, ncol = 5)))
names(volumes) <- c("Year", paste(rep("V", 5), seq(1:5), sep = ""))
volumes
Year V1 V2 V3 V4 V5
1 2000 56 52 88 81 52
2 2001 81 56 90 76 69
3 2002 81 92 69 93 69
4 2003 56 68 77 80 72
5 2004 51 58 62 53 62
set.seed(159)
prices <- as.data.frame(cbind(Year = 2000:2004,
matrix(round(runif(20, 5, 15), 0),
nrow = 5, ncol = 2)))
names(prices) <- c("Year", paste(rep("P", 2), seq(1:2), sep = ""))
prices
Year P1 P2
1 2000 6 5
2 2001 11 6
3 2002 11 13
4 2003 6 9
5 2004 5 7
Solution 1:[1]
Here is one possible approach. It's not the most efficient one but gets the job done:
result <- c()
for(i in names(volumes)) {
for(j in names(prices)) {
result <- c(result, volumes[i] * prices[j])
}
}
# outcome of every combination as you want (m * n columns)
result_df <- as.data.frame(result)
# resulting column names are a bit messy but you can rename easily
# names(result_df) <- # your list of m * n names
Solution 2:[2]
prices <- structure(list(Year = c(2001, 2003, 2002, 2000, 2004), P1 = c(15,
8, 13, 12, 7), P2 = c(7, 10, 8, 14, 10)), row.names = c(2L, 4L,
3L, 1L, 5L), class = "data.frame")
volumes <- structure(list(Year = c(2000, 2001, 2002, 2003, 2004), V1 = c(76,
78, 55, 74, 80), V2 = c(61, 80, 77, 68, 65), V3 = c(56, 52, 91,
69, 90), V4 = c(50, 59, 51, 66, 58), V5 = c(75, 57, 57, 80, 59
)), class = "data.frame", row.names = c(NA, -5L))
We can do this in two steps using lapply
and purrr::reduce
.
First, we use lapply
to loop through each column of prices
and multiply volumes
by that. lapply
returns a list, with the output of each operation as a list item.
volumes_mult <- lapply(prices[,-1], function(p) {
cbind(Year = volumes$Year, volumes[,-1] * p)
})
We then use reduce
to apply a *_join
operation to each item in the list. I recommend using purrr::reduce
rather than base R Reduce
because it makes it easier to supply additional arguments to *_join
(we need the by=
argument to properly join the tables). You can also customize the suffix=
argument to choose how identical rows from different tables will be renamed:
purrr::reduce(volumes_mult, dplyr::full_join, by='Year', suffix = paste0('_', names(x)))
Year V1_P1 V2_P1 V3_P1 V4_P1 V5_P1 V1_P2 V2_P2 V3_P2 V4_P2 V5_P2
1 2000 1140 915 840 750 1125 532 427 392 350 525
2 2001 624 640 416 472 456 780 800 520 590 570
3 2002 715 1001 1183 663 741 440 616 728 408 456
4 2003 888 816 828 792 960 1036 952 966 924 1120
5 2004 560 455 630 406 413 800 650 900 580 590
Solution 3:[3]
Sorry for digging up this old thread. I just had the same problem and created this solution, which (in my real-life use case) is faster by a factor of 300 comapred to the lapply + reduce option:
library(tidyverse)
names_loop <- expand_grid(volumes_names = names(volumes)[-1],
prices_names = names(prices)[-1])
left_join(volumes, prices, by = "Year") |>
add_column(map2_dfc(.x = names_loop[1],
.y = names_loop[2],
.f = ~volumes[.x] * prices[.y]) |>
rename_with(.cols = everything(),
.fn = ~paste0(names_loop$volumes_names, "_", names_loop$prices_names)))
Year V1 V2 V3 V4 V5 P1 P2 V1_P1 V1_P2 V2_P1 V2_P2 V3_P1 V3_P2 V4_P1 V4_P2 V5_P1 V5_P2
1 2000 56 52 88 81 52 6 5 336 280 312 260 528 440 486 405 312 260
2 2001 81 56 90 76 69 11 6 891 486 616 336 990 540 836 456 759 414
3 2002 81 92 69 93 69 11 13 891 1053 1012 1196 759 897 1023 1209 759 897
4 2003 56 68 77 80 72 6 9 336 504 408 612 462 693 480 720 432 648
5 2004 51 58 62 53 62 5 7 255 357 290 406 310 434 265 371 310 434
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
Solution | Source |
---|---|
Solution 1 | Ozan |
Solution 2 | |
Solution 3 | deschen |