CODEWITHSUNDEEP
rconditional-statementsdivision
kallen
kallen

Reputation: 9

Dividing multiple columns conditionally

I want to divide my supply dataframe by my population data frame to create a supply per capita data frame. Each data frame has an item column, a country column, and multiple year columns ( a column for each year's data). I need to match the Country and then divide each entry in the supply dataframe by the corresponding value in the population dataframe.

Any suggestions? I haven't been able to come up with anything.

Upvotes: 0

Views: 400

Answers (2)

akrun
akrun

Reputation: 887128

We can use data.table

library(data.table)
i1 <- grep("year", names(df1))
i2 <- paste0("i.", i1)
setDT(df1)[df2, (i1) := Map(`/`, mget(i1), mget(i2)), on = .(country)]
df1

Upvotes: 1

Florian
Florian

Reputation: 25385

Maybe this can help you n the right direction:

# sample data
population = data.frame(country = c("A","B"),population = c(100,200))
data = data.frame(country=c("A","B"), y2016 = c(1000,2000), y2017=c(2000,3000))

library(dplyr)
# join the population and the data dataframe based on the 'country' column.
data =data %>% left_join(population)

# divide all columns except the columns 'country' and 'population' by the population column.
data[, colnames(data)[!colnames(data) %in% c("country","population")]] = data[, colnames(data)[!colnames(data) %in% c("country","population")]]/data$population

Input:

> population
  country population
1       A        100
2       B        200
> data
  country y2016 y2017
1       A  1000  2000
2       B  2000  3000

Output:

  country y2016 y2017 population
1       A    10    20        100
2       B    10    15        200

Hope this helps!

Upvotes: 1

Related Questions