CODEWITHSUNDEEP
rdplyrtidyverse
Mibi
Mibi

Reputation: 298

R Multiply Column of DataFrame base on condition of another Column from two dataframes

I am trying to multiply one column (here 'employes') with an index (here relative 'profit'). So I want to multiply a certain number with every row which has the same year.

Lets say I have a profit increase of 1.05 in year 2019 than I want to multiply every value from year 2019 with 1.05 in a data frame.

The Code Example below does this but I don't want to write down every single column.

df1 = data.frame(year=c(2020, 2021, 2022), profit=c(1, 1.05, 1.12))
df2 = data.frame(year=c(2020, 2021, 2022, 2020, 2020, 2021, 2022), employes=c(200, 220, 240, 300, 140, 100, 200))

df2 %>% mutate('scaled' = case_when(year == df1[1,1] ~ employes * df1[nrow(df1), 2]/df1[1,2],
                                    year == df1[2,1] ~ employes * df1[nrow(df1), 2]/df1[2,2],
                                    year == df1[3,1] ~ employes * df1[nrow(df1), 2]/df1[3,2]))

Edit:

Thanks to MonJeanJean I got the answer. I modified the code a little bit and got this:

df_final = inner_join(df1, df2) %>%
  mutate('scaled'=employes*(df1[nrow(df1), 2]/profit))

Upvotes: 0

Views: 216

Answers (1)

MonJeanJean
MonJeanJean

Reputation: 2906

You can first join your two dataframes to get the value of profit in df2 :

df_final <- dplyr::inner_join(df, df2, by = "year")

You will get a dataframe with three columns :

  1. Year
  2. Profit
  3. employes

You can then create your new column :

df_final <- df_final %>%
mutate(new_column = employes*profits)

Upvotes: 1

Related Questions