[SOLVED] How to use Spotfire Dense Rank function in R and How to Implement it R?

How to use Spotfire Dense Rank function in R and How to Implement it R?

I need to calculate Dense Rank in R, like we use in Spotfire. I tried rank() function in R But thats not worked. For This you can consider below Data Frame which have 5 columns such as City, Region, Market, Year, Number_measure

You can consider below table for calculation

city <- c("Nagpur", "Mumbai", "Pune", "Aurangabad", 
"Auric City", "Solapur", "Nashik", "Aurangabad", "Auric City", "Latur", 
"Mumbai", "Pune", "Aurangabad", "Auric City", "Auric City", 
"Auric City", "Mumbai", "Pune", "Nagpur", "Mumbai", "Pune", 
"Nagpur", "Mumbai", "Pune", "Aurangabad", "Auric City", 
"Aurangabad", "Auric City","Solapur", "Nashik")

Region <- c("Vidharbh", "Mumbai Region", "West Maharashtra", 
             "Marathwada", "Marathwada", "West Maharashtra", 
             "North Maharashtra", "Marathwada", "Marathwada", 
             "Marathwada", "Mumbai Region", "West Maharashtra",
             "Marathwada", "Marathwada", "Marathwada", "Marathwada", 
             "Mumbai Region", "West Maharashtra", "Vidharbh", 
             "Mumbai Region", "West Maharashtra", "Vidharbh", 
             "Mumbai Region", "West Maharashtra", "Marathwada",
             "Marathwada", "Marathwada", "Marathwada", 
             "West Maharashtra", "North Maharashtra")

Market <- c("Public", "Non Public", "Public", "Non Public", 
"Public",  "Non Public", "Public", "Non Public", "Public", 
"Non Public", "Public",  "Non Public", "Public", "Non Public", 
"Public", "Non Public", "Public",  "Non Public","Public", 
"Non Public", "Public", "Non Public", "Public",  
"Non Public", "Public", "Non Public", "Public", "Non Public", 
"Public",  "Non Public")

Year <- c(2021, 2022, 2022, 2023, 2020, 2021, 2021, 
2022, 2022, 2022, 2022, 2024, 2023, 2025, 2026, 2021, 
2022, 2022, 2023, 2020, 2021, 2021, 2022, 2022, 2022, 
2022, 2024, 2023, 2025, 2026)

Number_Measure <- c(48877434, 48028137,  3912303, 13654456, 
30299576, 49090894, 53157520, 49631953, 43065620, 39234446, 
38298719,  4143774, 14631379, 31659688, 50734152, 55101038, 
51630622, 45200506, 41854513, 41215679,  4566837, 14384281, 
27952862, 44175298, 50610826, 50342076, 46282737, 44193037, 
44064169,  7849937)

MainData <- data.frame(city, Region, Market, Year, Number_Measure)

city            Region            Market       Year    Number_Measure
Nagpur            Vidharbh          Public    2021       48877434
Mumbai         Mumbai Region    Non Public    2022       48028137
Pune  West        Maharashtra       Public    2022        3912303
Aurangabad        Marathwada    Non Public    2023       13654456
Auric City        Marathwada        Public    2020       30299576
Solapur      West Maharashtra   Non Public    2021       49090894
Nashik       North Maharashtra      Public    2021       53157520
Aurangabad        Marathwada    Non Public    2022       49631953
Auric City        Marathwada        Public    2022       43065620
Latur             Marathwada    Non Public    2022       39234446
Mumbai         Mumbai Region        Public    2022       38298719
Pune  West        Maharashtra   Non Public    2024        4143774
Aurangabad        Marathwada        Public    2023       14631379
Auric City        Marathwada    Non Public    2025       31659688
Auric City        Marathwada        Public    2026       50734152
Auric City        Marathwada    Non Public    2021       55101038
Mumbai         Mumbai Region        Public    2022       51630622
Pune         West Maharashtra   Non Public    2022       45200506
Nagpur            Vidharbh          Public    2023       41854513
Mumbai         Mumbai Region    Non Public    2020       41215679
Pune         West Maharashtra       Public    2021        4566837
Nagpur            Vidharbh      Non Public    2021       14384281
Mumbai         Mumbai Region        Public    2022       27952862
Pune  West        Maharashtra   Non Public    2022       44175298
Aurangabad        Marathwada        Public    2022       50610826
Auric City        Marathwada    Non Public    2022       50342076
Aurangabad        Marathwada        Public    2024       46282737
Auric City        Marathwada    Non Public    2023       44193037
Solapur      West Maharashtra       Public    2025       44064169
Nashik       North Maharashtra  Non Public    2026       7849937

Now I Need to Find Rank on basis of Number_Measure and Key should be City, Regions, Market, Year

In Spotfire for this Dense Rank Function was used For Reference- https://docs.tibco.com/pub/spotfire/7.0.1/doc/html/ncfe/ncfe_ranking_functions.htm

Solution

Please Try dense_rank function from dplyr package

MainData$MeasureRank <- dense_rank(MainData$Number_Measure)