format all numeric columns with comma to seprate the v

Question

I have a data table below.

how do I format all columns with variables with comma values?

I know of the scales package but if I use the scales package I won't be able to use the table for some calculating operations any longer

i want something that will still retain the table format type as numeric.

Customers	telex	manay	players
babs	3434323424	937387573	96222221
bobs	53545322	758464938	122134
pint	43	7453537384	223444
red	35435	624353	345654
yello	4567	44	334

I want the output to look like this table below

Customers	telex	manay	players
babs	3,434,323,424	937,387,573	96,222,221
bobs	53,545,322	758,464,938	122,134
pint	43	7,453,537,384	223,444
red	35,435	624,353	345,654
yello	4,567	44	334

Does this answer your question? [Formatting numeric values in tibble with thousand separators gives error](https://stackoverflow.com/questions/63050218/formatting-numeric-values-in-tibble-with-thousand-separators-gives-error) — harre, Jun 07 '22 at 15:05
This may solve it. [link](https://stackoverflow.com/questions/29465941/format-number-in-r-with-both-comma-thousands-separator-and-specified-decimals) — maRvin, Jun 07 '22 at 15:47

score 2 · Answer 1 · answered Jun 08 '22 at 03:19

You can use the scales::label_comma() function. Note that the format for using this is scales::label_comma()(x) since a label function is generated and then you call it on your numerical vector.

As noted in similar answers above, this converts the column type to character. To convert back to number, you can use readr::parse_number.

library(dplyr)
#> 
#> Attaching package: 'dplyr'
#> The following objects are masked from 'package:stats':
#> 
#>     filter, lag
#> The following objects are masked from 'package:base':
#> 
#>     intersect, setdiff, setequal, union

df <- tibble(
  Customers = c("babs", "bobs", "pint", "red", "yellow"),
  telex = c(3434323424, 53545322, 43, 35435, 4567),
  manay = c(937387573, 758464938, 7453537384, 624353, 44),
  players = c(96222221, 122134, 223444, 345654, 334)
)
df
#> # A tibble: 5 x 4
#>   Customers      telex      manay  players
#>   <chr>          <dbl>      <dbl>    <dbl>
#> 1 babs      3434323424  937387573 96222221
#> 2 bobs        53545322  758464938   122134
#> 3 pint              43 7453537384   223444
#> 4 red            35435     624353   345654
#> 5 yellow          4567         44      334

df_with_comma <- df %>% 
  mutate(across(telex:players, scales::label_comma()))
df_with_comma
#> # A tibble: 5 x 4
#>   Customers telex         manay         players   
#>   <chr>     <chr>         <chr>         <chr>     
#> 1 babs      3,434,323,424 937,387,573   96,222,221
#> 2 bobs      53,545,322    758,464,938   122,134   
#> 3 pint      43            7,453,537,384 223,444   
#> 4 red       35,435        624,353       345,654   
#> 5 yellow    4,567         44            334

df_reverted <- df_with_comma %>% 
  mutate(across(telex:players, readr::parse_number))
df_reverted
#> # A tibble: 5 x 4
#>   Customers      telex      manay  players
#>   <chr>          <dbl>      <dbl>    <dbl>
#> 1 babs      3434323424  937387573 96222221
#> 2 bobs        53545322  758464938   122134
#> 3 pint              43 7453537384   223444
#> 4 red            35435     624353   345654
#> 5 yellow          4567         44      334

^{Created on 2022-06-07 by the reprex package (v2.0.1)}

maRvin · Accepted Answer · 2022-06-07T16:41:43.513

1

format() does this.

library(tidyverse)

df <- tibble(
  Customers = c("babs", "bobs", "pint", "red", "yellow"),
  telex = c(3434323424, 53545322, 43, 35435, 4567),
  manay = c(937387573, 758464938, 7453537384, 624353, 44),
  players = c(96222221, 122134, 223444, 345654, 334)
)

df <- df %>%
  mutate(across(telex:players, ~ format(., big.mark = ",", scientific = F)))

But note that your data changes to type "character".

edited Jun 07 '22 at 16:41

answered Jun 07 '22 at 16:14

maRvin

249
1
11

Is there a way I can convert it back to numeric without losing the comma? – Yomi.blaze93 Jun 07 '22 at 16:18
scientific = F already indicates that this formatting is unusual. I am not aware of any method that leads to numeric values with thousands separator. Sorry. – maRvin Jun 07 '22 at 16:40
Thank you I have re-ordered my script since the script will convert the data type tp character which is what I need, so the output will be my last script ...i can continue with other operations while the data type is in numeric Thanks alot – Yomi.blaze93 Jun 07 '22 at 16:46

score 0 · Answer 3 · answered Jun 07 '22 at 15:06

You can use formatC:

library(dplyr)

df <- data.table::fread('Customers  telex   manay   players
babs    3434323424  937387573   96222221
bobs    53545322    758464938   122134
pint    43  7453537384  223444
red     35435   624353  345654
yello   4567    44  334')

df %>% 
  mutate(across(telex:players, ~as.numeric(.))) %>% 
  mutate(across(telex:players, ~formatC(., format="d", big.mark=",")))

Which gives:

  Customers      telex       manay    players
1:      babs         NA 937,387,573 96,222,221
2:      bobs 53,545,322 758,464,938    122,134
3:      pint         43          NA    223,444
4:       red     35,435     624,353    345,654
5:     yello      4,567          44        334

I noticed that it change the data type from numeric to character. Is there a way I can change it back to numeric without tampering with the comma for further operations Thanks — Yomi.blaze93, Jun 07 '22 at 15:53

format all numeric columns with comma to seprate the v

3 Answers3