CODEWITHSUNDEEP
rdplyr
drmariod
drmariod

Reputation: 11762

Multiplication within dplyr to get multiple rows

I am trying to multiply values within dplyr with a named vector and would like to get two new columns with the vector names and the factors written in the vector.

A minimal example would look like this

accounts <- list(
  special=c(G09=.5, G10=.3, PCF=.2),
  normal=c(PCF=1)
)
df <- data.frame(account=c('normal','special'),
                 price=c(200,100))
df %>%
  mutate(price2=price * special_cost_center[[account]])

The output I expect should look like this. The new price multiplied by the values in the vector and ideal would be a new column with the new accounts.

  account price account2 price2
1  normal   200      PCF    200
2 special   100      G09     50
3 special   100      G10     30
4 special   100      PCF     20

So far I get an error that the mupliplication produces 3 instead of 1 line.

Has anyone an idea how to achieve something like this? I could think about instead of accounts in a list, to put it in a data.frame and then by joining, but I have the feeling this solution would be less readable.

Upvotes: 1

Views: 435

Answers (3)

akrun
akrun

Reputation: 886938

We can use tidyverse methods. Create a tibble with the 'accounts' and the names of the 'accounts', then unnest it to 'long' format, do a left_join with 'df' and transmute to select and modify/create new columns

library(tibble)
library(dplyr)
library(tidyr)
tibble(col1 = accounts, account = names(col1)) %>% 
     unnest_longer(c(col1)) %>% 
     left_join(df) %>% 
     transmute(account, price, account2 = col1_id, price2 = price * col1)
# A tibble: 4 x 4
#  account price account2 price2
#  <chr>   <dbl> <chr>     <dbl>
#1 special   100 G09          50
#2 special   100 G10          30
#3 special   100 PCF          20
#4 normal    200 PCF         200

Or using map

library(purrr)
map_dfr(accounts, enframe,  name = 'account2', .id = 'account') %>%
    left_join(df) %>%
    mutate(price2 = price * value, value = NULL )
# A tibble: 4 x 4
#  account account2 price price2
#  <chr>   <chr>    <dbl>  <dbl>
#1 special G09        100     50
#2 special G10        100     30
#3 special PCF        100     20
#4 normal  PCF        200    200

Upvotes: 1

Duck
Duck

Reputation: 39585

I reached something similar with next code (using a dataframe instead of list):

library(reshape2)
#Code
accounts <- data.frame(
  special=c(G09=.5, G10=.3, PCF=.2),
  normal=c(PCF=1)
)

accounts$account <- rownames(accounts)
rownames(accounts)<-NULL
#Melt
df2 <- reshape2::melt(accounts,id.vars = 'account')
#Data
df <- data.frame(account=c('normal','special'),
                 price=c(200,100))
#Merge
df3 <- merge(df2,df,by.x = 'variable',by.y='account')
df3$Prod <- df3$value*df3$price
df3$value <- NULL



  variable account price Prod
1   normal     G09   200  200
2   normal     G10   200  200
3   normal     PCF   200  200
4  special     G09   100   50
5  special     G10   100   30
6  special     PCF   100   20

Upvotes: 0

Sotos
Sotos

Reputation: 51582

An idea is to use Map but the output will not come out exactly as you desired.

do.call(rbind, Map(function(x, y) data.frame(Price1 = y, Price2 = x * y), 
                                      accounts[match(df$account, names(accounts))], 
                                      df$price) )

#            Price1 Price2
#normal         200    200
#special.G09    100     50
#special.G10    100     30
#special.PCF    100     20

Upvotes: 1

Related Questions