I have a target variable ranging from -33 to 17 and the variable merchant_category_id that has int type.
summary(total_trans$target)
Min. 1st Qu. Median Mean 3rd Qu. Max.
-33.21928 -0.80808 -0.09018 -0.45554 0.54223 17.96507
str(total_trans$merchant_category_id)
merchant_category_id: int 278 307 705 307 705 307 705 307 278 332
I want to find the quantity, from lowest to highest, of the variable merchant_category_id, only when the target variable is less than or equal to the first quartile.
I tried to do this:
total_trans %>% group_by(merchant_category_id) %>% summarise(counting = count(merchant_category_id))
Response is an error:
Error in summarise_impl(.data, dots) :
Evaluation error
After:
total_trans %>% summarise(Range = list(range(merchant_category_id[target <= summary(target)[2]])))
Response:
Range
1 -1, 891
Also try:
total_trans %>% group_by(merchant_category_id) %>% summarise(Range = list(range(target[target < -0.80808])))
Response:
# A tibble: 325 x 2
merchant_category_id Range
<int> <list>
1 -1 <dbl [2]>
2 2 <dbl [2]>
3 9 <dbl [2]>
4 11 <dbl [2]>
5 14 <dbl [2]>
6 16 <dbl [2]>
7 18 <dbl [2]>
8 19 <dbl [2]>
9 21 <dbl [2]>
10 27 <dbl [2]>
# ... with 315 more rows
There were 26 warnings (use warnings() to see them)
If I do this
total_trans %>% count(merchant_category_id, wt = target < -0.80808)
or
total_trans %>%
mutate(q1 = target <= quantile(target, 1/4)) %>%
filter(q1) %>%
group_by(merchant_category_id) %>%
summarise(count = n())
I get this in response:
merchant_category_id n
<int> <int>
1 -1 432
2 2 8364
3 9 2580
4 11 9
5 14 1800
6 16 177
7 18 4
8 19 24371
9 21 466
10 27 4
This is almost what I need. It is only necessary to order column n, from the largest quantity to the smallest quantity
How to use dplyr to do this?
I don't know it this is best answer:
top_n(total_trans %>%
mutate(q1 = target <= quantile(target, 1/4)) %>%
filter(q1) %>%
group_by(merchant_category_id) %>%
summarise(count = n())%>% arrange(desc(count)), 20)
But works using top_n.
Thank you very much everybody!!!!