CODEWITHSUNDEEP
r
Mr. A
Mr. A

Reputation: 63

How to format a data table or matrix in R for required currency symbol and negative numbers in parentheses "()"

I would like to get the below data (under a) in the format with currency and commas and negative numbers in parentheses and right aligned (under b and c). Also, note that I need currency symbol to only a few rows (having costs in labels/ row names)

(a) Current Output: -

                                Drug A vs P  Drug A vs Q  Drug A vs R  Drug A vs S
Combined PP Difference               0.0903       0.0383       0.9933       0.0393
Medical Cost Difference (in $)    -985.0988   -9999.5833   -5253.2836   -9603.5999
Total PP Cost Difference (in $)  -5696.8688    -335.3333    5590.0962    9880.3909
YYL based on Med Cost ($/YYL)   -93033.3368  -99065.8856  -59333.2590  -55263.3063
YYL based on Total Cost ($/YYL -535966.9930  -93933.5993   99238.5352   56583.9353

(b) Required Output: -

                                Drug A vs P  Drug A vs Q  Drug A vs R  Drug A vs S
Combined PP Difference                0.0903       0.0383       0.9933       0.0393
Medical Cost Difference (in $)     (985.0988)  (9999.5833)   (5253.2836)   -9603.5999
Total PP Cost Difference (in $)  (5,696.8688)   (335.3333)     5590.0962    9880.3909
YYL based on Med Cost ($/YYL)   (93,033.3368)  (99065.8856)  (59333.2590)  (55263.3063)
YYL based on Total Cost ($/YYL)(53,5966.9930) (93,933.5993)  (99238.5352)   56583.9353

The more deserved/required output is shown in (c). However, '(b)' is also somewhat okay.

(c) Required Output: -

                                Drug A vs P  Drug A vs Q  Drug A vs R  Drug A vs S
Combined PP Difference                0.0903       0.0383       0.9933       0.0393
Medical Cost Difference (in $)    $(985.0988)  $(9999.5833)   $(5253.2836)   -9603.5999
Total PP Difference (in $)      $(5,696.8688)   $(335.3333)     $5590.0962    9880.3909
YYL based on Med Cost ($/YYL)   $(93,033.3368)/YYL  $(99065.8856)/YYL  $(59333.2590)/YYL  $(55263.3063)/YYL
YYL based on Total Cost ($/YYL) $(53,5966.9930)/YYL $(93,933.5993)/YYL  $(99238.5352)/YYL   $56583.9353/YYL

Please note that I don't need "" (i.e. double/single codes) before the $ sign as a few codes/functions like dollar(x) etc. in R are assigning " at the beginning and at end of the numbers.

I have tried a few codes but neither I am getting -ve numbers in parenthesis and instead, dollars are coming with codes "" and with all rows except to came with row number 2 and 3 (in below codes I get rid of "" at least). Below are a few codes what I tried: -

Defining the results table - total PP difference, total cost difference and YYL based on medical cost and total cost

results.table = matrix(NA, nrow=5, ncol=4)

row.names(results.table) <- c("Combined PP Difference", "Medical Cost Difference (in $)", "Total PP Difference (in $)",
                              "YYL based on Med Cost ($/YYL)", "YYL based on Total Cost ($/YYL")
colnames(results.table) <- c(" Drug A vs P", " Drug A vs Q", " Drug A vs R", " Drug A vs S")

results.table[1,] <- INC.YYL[5,]
results.table[2,] <- INC.med.cost[5,]
results.table[3,] <- total.cost.diff[3,]
results.table[4,] <- INC.med.cost[5,]/INC.QALY[5,]
results.table[5,] <- total.cost.diff[3,]/INC.QALY[5,]

Printing the results table

round(results.table, 4)

Also, the below one is disturbing the right-alignment and giving $ to each row (and I want $ sign only at row 2 and 3).

library(formattable)
currency(results.table, symbol = "$", digits = 2)

Similarly, the below code is not serving the required: -

library(formattable)
rt1 <- currency(results.table[1,], "", 4)
rt2 <- currency(results.table[2,], "$", 0)
rt3 <- currency(results.table[3,], "$", 0)
rt4 <- currency(results.table[4,], "", 0)
rt5 <- currency(results.table[5,], "", 0)

rbind(rt1, rt2, rt3, rt4, rt5)

Also, it would be great if I can get the first row of output with 4 decimals and all other rows with no decimals.

Upvotes: 1

Views: 683

Answers (4)

Simon Woodward
Simon Woodward

Reputation: 2026

It's pretty easy using sprintf and ifelse. You can use two matrices of format strings to give each row a different format for positive vs negative values.

Edit: If you want comma separators in large numbers you can use format, but then you are working with strings, so I think it's easier to build the output matrices by line for positive and negative numbers separately. 

# data
results.table <-  matrix(2000*(runif(20)-runif(20)), nrow=5, ncol=4)
results.table[1,] <- results.table[1,] - round(results.table[1,], 0)
row.names(results.table) <- c("Combined PP Difference", "Medical Cost Difference (in $)", "Total PP Difference (in $)",
                              "YYL based on Med Cost ($/YYL)", "YYL based on Total Cost ($/YYL)")
colnames(results.table) <- c(" Drug A vs P", " Drug A vs Q", " Drug A vs R", " Drug A vs S")

# formatted for positive and negative
fp <- matrix(nrow=5, ncol=4)
fp[1,] <- sprintf("%s", format(round(results.table[1,], 4), big.mark=",", scientific=FALSE, trim=TRUE))
fp[2:3,] <- sprintf("$%s", format(round(results.table[2:3,], 0), big.mark=",", scientific=FALSE, trim=TRUE))
fp[4:5,] <- sprintf("$%s/YYL", format(round(results.table[4:5,], 0), big.mark=",", scientific=FALSE, trim=TRUE))
fn <- matrix(nrow=5, ncol=4)
fn[1,] <- sprintf("(%s)", format(abs(round(results.table[1,], 4)), big.mark=",", scientific=FALSE, trim=TRUE))
fn[2:3,] <- sprintf("$(%s)", format(abs(round(results.table[2:3,], 0)), big.mark=",", scientific=FALSE, trim=TRUE))
fn[4:5,] <- sprintf("$(%s)/YYL", format(abs(round(results.table[4:5,], 0)), big.mark=",", scientific=FALSE, trim=TRUE))

# formatted table
noquote(ifelse(results.table<0, fn, fp))

                                 Drug A vs P  Drug A vs Q  Drug A vs R  Drug A vs S
Combined PP Difference          0.1275       (0.4701)     0.2297       0.2005      
Medical Cost Difference (in $)  $472         $(141)       $(927)       $271        
Total PP Difference (in $)      $233         $(141)       $891         $288        
YYL based on Med Cost ($/YYL)   $(1,425)/YYL $(160)/YYL   $(759)/YYL   $1,307/YYL  
YYL based on Total Cost ($/YYL) $(377)/YYL   $(1,222)/YYL $545/YYL     $27/YYL

Created on 2019-04-12 by the reprex package (v0.2.1)

Upvotes: 1

Mr. A
Mr. A

Reputation: 63

Also, I found an interesting and simple combination of two codes and meeting the output requirement '(b)': -

library(formattable)
print(accounting(results.table, digits = 2, format = "f", big.mark = ","), right = T)

The output is:-

                                           Drug A vs P  Drug A vs Q  Drug A vs R  Drug A vs S
Combined PP Difference                          2.27         2.26         2.77         2.27
Medical Cost Difference (in $)              (782.22)   (7,779.29)   (2,822.88)   (7,627.22)
Total PP Difference (in $)                (2,696.87)     (772.28)     2,272.22     7,882.29
YYL based on Med Cost ($/YYL)            (77,272.26)  (79,262.88)  (27,222.82)  (22,867.27)
YYL based on Total Cost ($/YYL)         (222,966.79)  (72,727.29)    79,828.27    26,282.97

Upvotes: 1

TheRimalaya
TheRimalaya

Reputation: 4592

I have managed to get the bracket in negative but the function is able to append or prepend anything, hope this will help,

library(dplyr)
neg_to_brac <- function(x, ..., prepend = "$", append = "/YYL") {
  x <- formatC(x, ...)
  idx <- grepl("-", x)
  x[idx] <- paste0("(", gsub("-", "", x[idx]), ")")
  paste0(prepend, x, append)
}
with_currency <- as.data.frame(results.table) %>% 
  tibble::rownames_to_column("Rows") %>% 
  mutate_at(-1, function(x) neg_to_brac(x, append = ""))
with_currency

The Result:

                            Rows  Drug A vs P  Drug A vs Q  Drug A vs R  Drug A vs S
1         Combined PP Difference     $(1.272)       $1.123    $(0.6018)       $1.705
2 Medical Cost Difference (in $)      $0.8326    $(0.1195)    $(0.5047)      $0.5463
3     Total PP Difference (in $)    $(0.9378)       $0.452      $0.6503    $(0.2779)
4  YYL based on Med Cost ($/YYL)    $(0.5051)    $(0.7621)   $(0.02109)       $1.533
5 YYL based on Total Cost ($/YYL     $0.06244    $(0.1208)      $0.4944       $1.614

Upvotes: 0

Mike
Mike

Reputation: 4370

Here is a way to format a data.frame so it looks like you need it. 

library(dplyr)
df <- data.frame(`Drug A vs P` = c(0.0107,-252966.1950),
           `Drug A vs Q` = c(0.0587,-13157.2915),
           `Drug A vs R` = c(0.1137,-19438.2724),
           `Drug A vs S` = c(0.0715,26285.9723),
           row.names = c("Combined PP Difference","YYL based on Total Cost ($/YYL"),
           stringsAsFactors = FALSE)

df2 <- df %>% 
      mutate( type = row.names(df)) %>% 
      mutate_if(is.numeric, funs(ifelse(. < 0 & type == "YYL based on Total Cost ($/YYL", 
                                   paste0("$(",.,")/YLL"),
                            ifelse(. < 0 & type =="Combined PP Difference",
                                   paste0("$(",.,")"), . ))))

colnames(df2) <- c('Drug A vs P','Drug A vs Q','Drug A vs R','Drug A vs S','type')

First I made the rownames a column so it was easier to work with. Then I formatted the numeric variables if they were less than 0 to your specifications above.

Upvotes: 0

Related Questions