How do I split the value of a column based on a lookup table?

user_5054897 0
Viewed 75

I have a dataset with ids and associated values:

df <- data.frame(id = c("1", "2", "3"), value = c("12", "20", "16"))

I have a lookup table that matches the id to another reference label ref:

lookup <- data.frame(id = c("1", "1", "1", "2", "2", "3", "3", "3", "3"), ref = c("a", "b", "c", "a", "d", "d", "e", "f", "a"))

Note that id to ref is a many-to-many match: the same id can be associated with multiple ref, and the same ref can be associated with multiple id.

I'm trying to split the value associated with the df$id column equally into the associated ref columns. The output dataset would look like:

output <- data.frame(ref = "a", "b", "c", "d", "e", f", value = "18", "4", "4", "14", "4", "4")
ref value
a 18
b 4
c 4
d 14
e 4
f 4

I tried splitting this into four steps:

  1. calling pivot_wider on lookup, turning rows with the same id value into columns (e.g., a, b, c.)
  2. merging the two datasets based on id
  3. dividing each df$value equally into a, b, c, etc. columns that are not empty
  4. transposing the dataset and summing across the id columns.

I can't figure out how to make step (3) work, though, and I suspect there's a much easier approach.

reshape dataframe pivot r
3 Answers

A variation of @thelatemail's answer with base pipes.

merge(df, lookup) |> type.convert(as.is=TRUE) |>
  transform(value=ave(value, id, FUN=\(x) x/length(x))) |>
  with(aggregate(list(value=value), list(ref=ref), sum))
#   ref value
# 1   a    18
# 2   b     4
# 3   c     4
# 4   d    14
# 5   e     4
# 6   f     4
user_65740380

Here's a potential logic. Merge value from df into lookup by id, divide value by number of matching rows, then group by ref and sum. Then take your pick of how you want to do it.

Base R

tmp <- merge(lookup, df, by="id", all.x=TRUE)
tmp$value <- ave(as.numeric(tmp$value), tmp$id, FUN=\(x) x/length(x) )
aggregate(value ~ ref, tmp, sum)

dplyr

library(dplyr)
lookup %>%
  left_join(df, by="id") %>%
  group_by(id) %>% 
  mutate(value = as.numeric(value) / n() ) %>%
  group_by(ref) %>%
  summarise(value = sum(value))

data.table

library(data.table)
setDT(df)
setDT(lookup)
lookup[df, on="id", value := as.numeric(value)/.N, by=.EACHI][
   , .(value = sum(value)), by=ref]

#   ref value
#1:   a    18
#2:   b     4
#3:   c     4
#4:   d    14
#5:   e     4
#6:   f     4
user_4968030

This may work

lookup %>%
  left_join(lookup %>%
              group_by(id) %>%
              summarise(n = n()) %>%
              left_join(dummy, by = "id") %>%
              mutate(value = as.numeric(value)) %>%
              mutate(repl = value/n) %>%
              select(id, repl) ,
            by = "id"
  ) %>% select(ref, repl) %>%
  group_by(ref) %>% summarise(value = sum(repl))

  ref   value
  <chr> <dbl>
1 a        18
2 b         4
3 c         4
4 d        14
5 e         4
6 f         4
user_167291750
Related