I need to spread an amount proportionally over several rows by ID

[wissalmh]

I have two datasets with a common ID but not the same level of granularity, and i need to join both files and spread the amount i have in df2 over several rows in df1 having the same common ID but have are split into contracts ....

I know that i need to 1st count the contracts i have by ID and then find a way to divide the amount proportionnaly

[EeethB]

In general it will be easier to get help with a minimal reproducible example, or reprex. Sometimes making one will even show you where the problem is, which is always a great feeling!

FAQ: How to do a minimal reproducible example ( reprex ) for beginners

A minimal reproducible example consists of the following items: A minimal dataset, necessary to reproduce the issue The minimal runnable code necessary to reproduce the issue, which can be run on the given dataset, and including the necessary information on the used packages. Let's quickly go over each one of these with examples: Minimal Dataset (Sample Data) You need to provide a data frame that is small enough to be (reasonably) pasted on a post, but big enough to reproduce your issue. Let's say, as an example, that you are working with the iris data frame head(iris) #> Sepal.Length Sepal.Width Petal.Length Petal.Width Species #> 1 5.1 3.5 1.4 0.…

I tried to make some fake data to reproduce your problem. If this not an accurate representation, please post some more detail about your problem:

library(tidyverse)

df1 <- tibble(grp = c(rep(1, 2), rep(2, 3)), contract = c(1:2, 1:3))
df2 <- tibble(grp = 1:2, value = c(173, 2142))

df1 %>% 
  group_by(grp) %>% 
  mutate(ct = n()) %>% 
  left_join(df2, by = "grp") %>% 
  mutate(prop_value = value / ct)
#> # A tibble: 5 x 5
#> # Groups:   grp [2]
#>     grp contract    ct value prop_value
#>   <dbl>    <int> <int> <dbl>      <dbl>
#> 1     1        1     2   173       86.5
#> 2     1        2     2   173       86.5
#> 3     2        1     3  2142      714  
#> 4     2        2     3  2142      714  
#> 5     2        3     3  2142      714

^{Created on 2021-05-21 by the reprex package (v2.0.0)}