R remove part of string using gsub with wildcard

Question

I have a dataframe where I want to do two things to the values:

1. Add a space as thousand separator
2. Remove de trailing zero's after the decimal

I managed to do both these things separately, but I don't seem to be able to combine the two.

In order to remove the trailing zero's I used:

cat <- c("A", "B", "C")
value <- c(1234.5, 1, 12.34)
df <- data.frame(cat, value)

df$value2 <- gsub("\\.00$","",df$value)
df
  cat   value value2
1   A 1234.50 1234.5
2   B    1.00      1
3   C   12.34  12.34

And in order to add the thousand separator I used:

df$value2 <- format(df$value, big.mark=" ")
df
  cat   value   value2
1   A 1234.50 1 234.50
2   B    1.00     1.00
3   C   12.34    12.34

Now I wish to combine the two, but if I do this using the following code:

df$value2 <- gsub("\\.00$","",format(df$value, big.mark=" "))
df
  cat   value   value2
1   A 1234.50 1 234.50
2   B    1.00        1
3   C   12.34    12.34

The trailing zero's are only removed in the cases that end with two zeroes. I have tried to add |\\..*0$ so that it also looks at zeroes with a number in front of it, but that removes the number after the decimal, which is not what I want.

Answer 1

You can use the argument drop0trailing

format(value, big.mark=" ", drop0trailing=TRUE)
# [1] "1 234.5" "   1"    "  12.34"

Works with formatC as well, with the difference that you get no leading spaces by default :

formatC(value, big.mark=" ", drop0trailing=TRUE)
# [1] "1 234" "1"     "12.34"

Or use the trim argument of format :

format(value, big.mark=" ", drop0trailing=TRUE, trim = TRUE)
# [1] "1 234.5" "1"       "12.34"