Thousand separator to numeric columns in R

Question

I am trying to format numbers as shown (adding thousand separator). The function is working fine but post formatting the numbers, the numeric columns does not sort by numbers since there are characters

df <- data.frame(x = c(12345,35666,345,5646575))
format_numbers <- function (df, column_name){
  df[[column_name]] <- ifelse(nchar(df[[column_name]]) <= 5, paste(format(round(df[[column_name]] / 1e3, 1), trim = TRUE), "K"),
                              paste(format(round(df[[column_name]] / 1e6, 1), trim = TRUE), "M"))
}

df$x <- format_numbers(df,"x")
> df
       x
1 12.3 K
2 35.7 K
3  0.3 K
4  5.6 M

Can we make sure the numbers are sorted in descending/ascending order post formatting ?

Note : This data df is to be incorporated in DT table

Try mixed sort, possible duplicate https://stackoverflow.com/q/17531403/680068 — zx8754, Feb 17 '22 at 11:36
Try DT native formatting: https://stackoverflow.com/q/46656851/680068 — zx8754, Feb 17 '22 at 11:37
Mixed sorting does not work ````df$x <- df$x[order(nchar(df$x), df$x)]```` — manu p, Feb 17 '22 at 11:39
Have you checked out these answers? https://stackoverflow.com/q/27255108/ — Roman, Feb 17 '22 at 22:07

score 3 · Answer 1 · answered Feb 18 '22 at 11:38

The problem is the formating part. If you do it correctly--ie while maintaining your data as numeric, then everything else will fall in place. Here I will demonstrate using S3 class:

my_numbers <- function(x) structure(x, class = c('my_numbers', 'numeric'))

format.my_numbers <-  function(x,..., d = 1,  L =   c('', 'K', 'M', 'B', 'T')){
      ifelse(abs(x) >= 1000, Recall(x/1000, d = d + 1),
         sprintf('%.1f%s', x, L[d]))
}

print.my_numbers <- function(x, ...) print(format(x), quote = FALSE)

'[.my_numbers' <- function(x, ..., drop = FALSE)  my_numbers(NextMethod('['))

Now you can run your code:

df <- data.frame(x = c(12345,35666,345,5646575))
df$x <- my_numbers(df$x)
df
      x
1 12.3K
2 35.7K
3 345.0
4  5.6M

You can use any mathematical operation on column x as it is numeric.

eg:

cbinding with its double and ordering from smallest to larges:

cbind(x = df, y = df*2)[order(df$x),]
      x     x
3 345.0 690.0  # smallest
1 12.3K 24.7K
2 35.7K 71.3K
4  5.6M 11.3M  # largest ie Millions

Note that under the hood, x does not change:

unclass(df$x)
[1]   12345   35666     345 5646575 # Same as given

That is great. Can you please explain in steps especially ````format.my_numbers```` and ````print.my_numbers```` . But this is really fantastic. Actually i am not aware of s3 class — manu p, Feb 18 '22 at 13:04
@manup the format function just formats the numbers for display. The numbers are not changed but rather formated in a lretty way. Its just a normal function. Eg run `format.my_numbers(c(10000,2020000,3456789))`and not what it gives you. Why do i have the `.my_numbers` in every function? Because i am using S3 class and i need to define the methods for that particular class. — Onyambu, Feb 18 '22 at 17:29

Thousand separator to numeric columns in R

1 Answers1

Linked