0

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.

kallen
  • 9
  • 1
  • 1
    Please show a small reproducible example and expected output based on that. Try with a `merge` or `left_join` from `dplyr` and replace the values by dividing – akrun Jul 17 '17 at 06:45
  • 1
    Welcome to StackOverflow! Please read the info about [how to ask a good question](http://stackoverflow.com/help/how-to-ask) and how to give a [reproducible example](http://stackoverflow.com/questions/5963269). This will make it much easier for others to help you. – Sotos Jul 17 '17 at 06:45

2 Answers2

1

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!

Florian
  • 24,425
  • 4
  • 49
  • 80
1

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
akrun
  • 874,273
  • 37
  • 540
  • 662