I have a dataset like df with dozens of thousands of observations and +100 categories (in the categorical variables).
This data contains info about different individuals (id) bringing different type of patients to different places for a given price and year.
year <- c(2010, 2010, 2010, 2010, 2011, 2011, 2011, 2010, 2011)
id <- c("A", "A" , "A" , "A" , "A" , "A" , "A", "B", "B")
type <- c("kid", "kid", "adult", "kid", "kid", "dog", "cat", "kid", "kid")
place <- c("hosp", "hosp", "house", "hosp", "hosp", "hosp", "house", "hosp", "hosp")
price <- c(2, 3, 6, 5, 1, 2, 3, 4, 5)
df <- data.frame(year, id, type, place, price)
I want to do some basic calculations on df (basically summary statistics) by groups (id-year) in the following manner:
So I can get something like df_new:
year <- c("2010", "2011", "2010", "2011")
id <- c("A", "A", "B", "B")
exp_type_kid <- c(1, 2, 1, 2)
exp_type_adult <- c(1, 1, 0, 0)
exp_type_dog <- c(0, 1, 0, 0)
exp_type_cat <- c(0, 1, 0, 0)
exp_place_hosp <- c(1, 2, 1, 2)
exp_place_house <- c(1, 2, 0, 0)
avg_price <- c(4, 2, 4, 5)
id_repeat_next_year <- c(1, 0, 1, 0)
df_new <- data.frame(year, id, exp_type_kid, exp_type_adult, exp_type_dog, exp_type_cat,
exp_place_hosp, exp_place_house, avg_price, id_repeat_next_year)
EDIT
My dataset can contain much more years, as in the following example:
year <- c(2010, 2010, 2010, 2010, 2011, 2011, 2011, 2009, 2010, 2015, 2017)
id <- c("A", "A" , "A" , "A" , "A" , "A" , "A", "B", "B", "B", "B")
type <- c("kid", "kid", "adult", "kid", "kid", "dog", "cat", "kid", "kid", "kid", "kid")
place <- c("hosp", "hosp", "house", "hosp", "hosp", "hosp", "house", "hosp", "hosp", "hosp", "hosp")
price <- c(2, 3, 6, 5, 1, 2, 3, 4, 4, 4, 4)
df <- data.frame(year, id, type, place, price)
I modified the year to make it numeric.
year <- c(2010, 2010, 2010, 2010, 2011, 2011, 2011)
id <- c("A", "A" , "A" , "A" , "A" , "A" , "A")
type <- c("kid", "kid", "adult", "kid", "kid", "dog", "cat")
place <- c("hosp", "hosp", "house", "hosp", "hosp", "hosp", "house")
price <- c(2, 3, 6, 5, 1, 2, 3)
df <- data.frame(year, id, type, place, price)
df
## year id type place price
## 1 2010 A kid hosp 2
## 2 2010 A kid hosp 3
## 3 2010 A adult house 6
## 4 2010 A kid hosp 5
## 5 2011 A kid hosp 1
## 6 2011 A dog hosp 2
## 7 2011 A cat house 3
I see three different tasks. Points 1 and 2 are about counting, point 3
about aggregating and point 4 satisfying a condition. I think each task must
be addressed individually and hopefully we can merge the results into the
desired data.frame.
For a single factor we can use table() to get the counts by year:
with(df, table(year, type))
## type
## year adult cat dog kid
## 2010 1 0 0 3
## 2011 0 1 1 1
Now we only care about wether there is an ocurrence of each type for any given year:
with(df, table(year, type) > 0)
## type
## year adult cat dog kid
## 2010 TRUE FALSE FALSE TRUE
## 2011 FALSE TRUE TRUE TRUE
From here we can cumsum() by columns to get the years of experience, and
easily turn that into a data.frame:
# type
with(df, table(year, type) > 0) |> apply(2, cumsum) |> as.data.frame()
## adult cat dog kid
## 2010 1 0 0 1
## 2011 1 1 1 2
# place
with(df, table(year, place) > 0) |> apply(2, cumsum) |> as.data.frame()
## hosp house
## 2010 1 1
## 2011 2 2
Let's turn this into a function to make things easier to follow:
experience_by = function (df, what) {
out = with(df, table(year, get(what)) > 0) |> apply(2, cumsum) |> as.data.frame()
names(out) = paste('exp', what, names(out), sep = '_')
return(out)
}
experience_by(df, 'type')
## exp_type_adult exp_type_cat exp_type_dog exp_type_kid
## 2010 1 0 0 1
## 2011 1 1 1 2
experience_by(df, 'place')
## exp_place_hosp exp_place_house
## 2010 1 1
## 2011 2 2
We are missing the year variable here (they are rownames now) but that will
get solved in the next step.
This is the easy part:
aggregate(price ~ year, FUN = mean, data = df)
## year price
## 1 2010 4
## 2 2011 2
id be present next year?with(df, unique(year + 1) %in% unique(year)) |> as.numeric()
## [1] 1 0
experience_by = function (df, what) {
out = with(df, table(year, get(what)) > 0) |> apply(2, cumsum) |> as.data.frame()
names(out) = paste('exp', what, names(out), sep = '_')
return(out)
}
by_type = experience_by(df, 'type')
by_place = experience_by(df, 'place')
avg_price = aggregate(price ~ year, FUN = mean, data = df)
id_repeat_next_year = with(df, as.numeric(unique(year + 1) %in% unique(year)))
cbind(avg_price, by_type, by_place, id_repeat_next_year)
## year price exp_type_adult exp_type_cat exp_type_dog exp_type_kid
## 2010 2010 4 1 0 0 1
## 2011 2011 2 1 1 1 2
## exp_place_hosp exp_place_house id_repeat_next_year
## 2010 1 1 1
## 2011 2 2 0
For the whole dataset this should be lapplyed over the splited data.frame.
Something of this sort:
split(df, ~ id) |>
lapply(function (x) {
by_type = experience_by(df, 'type')
by_place = experience_by(df, 'place')
avg_price = aggregate(price ~ year, FUN = mean, data = df)
id_repeat_next_year = with(df, as.numeric(unique(year + 1) %in% unique(year)))
cbind(avg_price, by_type, by_place, id_repeat_next_year)
})
## $A
## year price exp_type_adult exp_type_cat exp_type_dog exp_type_kid
## 2010 2010 4 1 0 0 1
## 2011 2011 2 1 1 1 2
## exp_place_hosp exp_place_house id_repeat_next_year
## 2010 1 1 1
## 2011 2 2 0
From here it should be easy to recover ids from the list and rbind all
data.frames. I am not sure how to fill in values when there are more ids
that do not share the same types or places, since this will originate
differing number of columns on each data.frame...
With the new data frame and a new id:
year <- c(2010, 2010, 2010, 2010, 2011, 2011, 2011, 2010, 2011)
id <- c("A", "A" , "A" , "A" , "A" , "A" , "A", "B", "B")
type <- c("kid", "kid", "adult", "kid", "kid", "dog", "cat", "kid", "kid")
place <- c("hosp", "hosp", "house", "hosp", "hosp", "hosp", "house", "hosp", "hosp")
price <- c(2, 3, 6, 5, 1, 2, 3, 4, 5)
df <- data.frame(year, id, type, place, price)
# use `local()` instead of pipes; `x` instead of `df`
df_new = local({
dfs = split(df, ~ id)
dfl = lapply(dfs, function (x) {
by_type = experience_by(x, 'type')
by_place = experience_by(x, 'place')
avg_price = aggregate(price ~ year, FUN = mean, data = x)
id_repeat_next_year = with(x, as.numeric(unique(year + 1) %in% unique(year)))
cbind(avg_price, by_type, by_place, id_repeat_next_year)
})
# recover `id`s
new_id = rep(names(dfl), times = sapply(dfl, FUN = nrow))
# create missing cols and combine `dfl`
all_cols = sapply(dfl, FUN = names) |> unlist() |> unique()
out = data.frame()
for (i in dfl) {
col_present = all_cols %in% names(i)
if (!all(col_present)) {
for (cl in all_cols[!col_present]) {
i[[cl]] = numeric(nrow(i))
}
}
out = rbind(out, i)
}
out = data.frame(id = new_id, out, row.names = NULL)
out
})
df_new
## id year price exp_type_adult exp_type_cat exp_type_dog exp_type_kid
## 1 A 2010 4 1 0 0 1
## 2 A 2011 2 1 1 1 2
## 3 B 2010 4 0 0 0 1
## 4 B 2011 5 0 0 0 2
## exp_place_hosp exp_place_house id_repeat_next_year
## 1 1 1 1
## 2 2 2 0
## 3 1 0 1
## 4 2 0 0