Maximum based on custom order-CodePudding

I want to calculate highest price based on custom orders as follows (largest being first).

Hundred Million
Million
THO
Hundred

Data is as below -

df <- read.table(text = "Price  Unit
1445    Million
620 THO
830 Million
661 Million
783 Hundred
349 'Hundred Million'
", header= T)

CodePudding user response：

If you wish to also calculate the "actual price", we can:

first create a dataframe of "Unit" and "Value" (for example price_unit in my answer).
Then left_join this price_unit with your original dataframe, which will match on the "Unit" column.
Then do the calculation using mutate.
Finally sort the column.

library(tidyverse)

df <- read.table(text = "Price  Unit
1445    Million
620 THO
830 Million
661 Million
783 Hundred
349 'Hundred Million'
", header= T)

price_unit <- tibble(Unit = c("THO", "Hundred", "Million", "Hundred Million"), 
                     Value = c(10^3, 10^2, 10^6, 10^8))

left_join(df, price_unit, by = "Unit") %>% 
  mutate(actual_price = Price * Value) %>% 
  arrange(desc(actual_price))

  Price            Unit Value actual_price
1   349 Hundred Million 1e 08    3.490e 10
2  1445         Million 1e 06    1.445e 09
3   830         Million 1e 06    8.300e 08
4   661         Million 1e 06    6.610e 08
5   620             THO 1e 03    6.200e 05
6   783         Hundred 1e 02    7.830e 04

CodePudding user response：

First you can create a factor for your Unit variable by ordering them in the levels command:

df$Unit <- factor(df$Unit,
       levels = c("THO",
                  "Hundred",
                  "Million",
                  "Hundred Million"))

Then just arrange by unit, which should arrange them by smallest unit to largest:

df %>% 
  arrange(Unit,
          Price)

Which gives you this output:

  Price            Unit
1   620             THO
2   783         Hundred
3   661         Million
4   830         Million
5  1445         Million
6   349 Hundred Million