Conditional fractioning

Question

Suppose I have the dataset

ID	Country	Sales
1	Austria	6
1	Austria	6
1	Belgium	6
2	Belgium	10
2	Czech	10
3	Denmark	3
3	Germany	3

I want to another variable of sales which depends on countries and their ID ie in fractions.

ID	Country	Sales	fraction
1	Austria	6	0.666
1	Austria	6	0.666
1	Belgium	6	0.333
2	Belgium	10	0.5
2	Czech	10	0.5
3	Denmark	3	1
3	Denmark	3	1

Any help would be appreciated!

`library(dplyr); your_data |> mutate(fraction = Sales / sum(Sales), .by = ID)` — Gregor Thomas, Jun 29 '23 at 13:51
Hmm, just realized it's a little more complicated because of the repeated countries within ID groups. Posting an answer in a minute... — Gregor Thomas, Jun 29 '23 at 13:52

score 1 · Answer 1 · edited Jun 29 '23 at 15:17

library(dplyr)
your_data |>
  mutate(country_total = sum(Sales), .by = c(ID, Country)) |>
  mutate(fraction = country_total / sum(Sales), .by = ID)
#   ID Country Sales country_total  fraction
# 1  1 Austria     6            12 0.6666667
# 2  1 Austria     6            12 0.6666667
# 3  1 Belgium     6             6 0.3333333
# 4  2 Belgium    10            10 0.5000000
# 5  2   Czech    10            10 0.5000000
# 6  3 Denmark     3             3 0.5000000
# 7  3 Germany     3             3 0.5000000

Using this sample data:

your_data = read.table(text = 'ID   Country     Sales
1   Austria     6
1   Austria     6
1   Belgium     6
2   Belgium     10
2   Czech   10
3   Denmark     3
3   Germany     3', header = T)

score 1 · Accepted Answer · answered Jun 29 '23 at 14:28

Alternatively we could use add_count to get the same result

library(dplyr)

df %>% add_count(ID,name = 'n') %>% add_count(ID,Country, name = 'gn') %>% 
mutate(new=gn/n) %>% select(-c(n,gn))

# output

# A tibble: 7 × 4
     ID Country Sales   new
  <dbl> <chr>   <dbl> <dbl>
1     1 Austria     6 0.667
2     1 Austria     6 0.667
3     1 Belgium     6 0.333
4     2 Belgium    10 0.5  
5     2 Czech      10 0.5  
6     3 Denmark     3 0.5  
7     3 Germany     3 0.5  
```

score 0 · Answer 3 · answered Jun 29 '23 at 14:01

Base

> df$fraction=ave(df$Sales,list(df$ID,df$Country),FUN=sum)/ave(df$Sales,df$ID,FUN=sum)

  ID Country Sales  fraction
1  1 Austria     6 0.6666667
2  1 Austria     6 0.6666667
3  1 Belgium     6 0.3333333
4  2 Belgium    10 0.5000000
5  2   Czech    10 0.5000000
6  3 Denmark     3 0.5000000
7  3 Germany     3 0.5000000

Conditional fractioning

3 Answers3