Brtdku

I have created a dataframe which has three columns
Name, Month and Amount .
The format is such that there are mutiple names in each month and each combination has an amount .
I want to find the top 5 users based on their monthly spending.
Which means the final data in the data frame will have only top 5 earnings for each month .
The way i have calculated the data now now is **

Extract_Month<- months(Credit$Transaction.Date)

Extract_Month

TopSpend<-aggregate(Credit$Amount, 

                    by=list(Credit$User,Extract_Month)

                    , FUN=mean)

**
I am stuck beyond this point . Please help

Here is some sample data

User<-c(6,2,3,4,5,6)

Transaction.Date<-c("11-1-2019","11-2-2019","11-3-2019",

"12-1-2019","12-2-2019","11-1-2019")

Amount<-c(100,200,300,400,500,150)



Credit<-data.frame(User,Transaction.Date,Amount)

Using made up data over multiple months. May not be the best approach but it works. I would recommend working with @NelsonGon on the tidyverse approach.

Data Creation:

library(dplyr)



User<-c(6,2,3,4,5,6)

Transaction.Date<-c("11-1-2019","11-2-2019","11-3-2019",

"12-1-2019","12-2-2019","11-1-2019")

Amount<-c(100,200,300,400,500,150)



Credit<-data.frame(User,Transaction.Date,Amount)

Aggregate, Arrange and Subset:

#Aggregate user by avg amount spent and date

TopSpend<-aggregate(Credit$Amount, 

                by=list(Credit$User,Credit$Transaction.Date)

                , FUN=mean)



#Reverse so high in the start                    

TopSpend<-arrange(TopSpend, rev(rownames(TopSpend)))

                    print(TopSpend)



#Rename for clarity                

names(TopSpend)<-c("User", "Date","Amount")



#Format date for split              

TopSpend$Date<-as.POSIXct(TopSpend$Date, format="%m-%d-%Y")



#Split based on month             

TopSpend_Fin<-split(TopSpend, format(TopSpend$Date, "%Y-%m"))



#Get first 5 elements (non-existent won't throw error)

TopSpend_Fin<-lapply(TopSpend_Fin, head, n = 5L)



$`2019-11`

  User       Date Amount

3    3 2019-11-03    300

4    2 2019-11-02    200

5    6 2019-11-01    125



$`2019-12`

  User       Date Amount

1    5 2019-12-02    500

2    4 2019-12-01    400

Here is a solution:

 library(tidyverse)

 df<-data.frame(Name=c("A","B","C"),Month=as.factor(c(11,11,11)),Amount=c(123,456,789))

 df %>% 

 arrange(desc(Amount)) %>% 

 top_n(2,Amount)#change 2 to 5

Best to provide sample data:

iris %>% 

  group_by(Species) %>% 

  arrange(desc(Sepal.Length)) %>% 

  top_n(5,Sepal.Length)

OR:: Based on @Chabo 's data:

User<-c(6,2,3,4,5,6)

Transaction.Date<-c("11-1-2019","11-2-2019","11-3-2019",

                    "12-1-2019","12-2-2019","11-1-2019")

Amount<-c(100,200,300,400,500,150)

df1<-data.frame(Amount,Transaction.Date,User)

df1 %>% 

  group_by(User,Transaction.Date) %>% 

  arrange(desc(Amount)) %>% 

  top_n(5,Amount) %>% 

  ungroup() %>% 

  top_n(5,Amount)