I'm analyzing multiple data.table's in R using the data.table package and am struggling with pulling information from multiple tables at a time.
I have a products data.table and a january_views data.table. The january_views table lists the customer_id, product_id, and time. In the products table, it lists the product_id, category, and price. I want to create a table that lists the most viewed products that will include the product_id, the category, and the number of views.
I've been able to create a table that shows the amount of times the products were viewed in descending order from the january_views table but I don't know how to add in the category from the products table. This is what I have so far:
tab <- january_views[, .N, by = product_id]
setorderv(tab, cols = "N", order = -1)
How can I add in the category to this table?
Here's reproducible data for both data.tables:
structure(list(category = structure(c(5L, 5L, 5L, 5L, 5L, 5L,
5L, 5L, 5L, 5L), levels = c("coat", "hat", "pants", "shirt",
"shoes"), class = "factor"), product_id = c("dCgYSwwE8gjlmm7u",
"GyoBFMLpSxMyYNXJ", "LJFl3aX0EUW84u06", "SIKFeFci7HLldbiL", "qFBRZdMfS00mk0x8",
"5S0HofP6wP7TXQbm", "t3qGXTgA4SvSq92B", "IMWN7BvRKgXkMMNy", "AinHMrbMn8ygQnjq",
"SuLEtq5M9tTAFPYP"), price = c(122.19, 128.99, 110.99, 137.99,
112.99, 114.99, 114.99, 154.1, 116.99, 129.79)), row.names = c(NA,
-10L), class = c("data.table", "data.frame"), .internal.selfref = <pointer: 0x1488180e0>)
structure(list(customer_id = c("S4PpjV8AgTBx", "p5bpA9itlILN",
"nujcp24ULuxD", "cFV46KwexXoE"), product_id = c("kq4dNGB9NzwbwmiE",
"FQjLaJ4B76h0l1dM", "pCl1B4XF0iRBUuGt", "e5DN2VOdpiH1Cqg3"),
time = structure(c(1577872800.28324, 1577872800.89588, 1577872801.36233,
1577872801.87305), class = c("POSIXct", "POSIXt"), tzone = "America/New_York")), row.names = c(NA,
-4L), class = c("data.table", "data.frame"), .internal.selfref = <pointer: 0x1488180e0>)