Preventing Decimal Places in Specific Rows in R Data Frame

Question

I'm working with an R data frame where I have columns representing "year" and "sales". I want to display these values as whole numbers without any decimal places. Currently, when I create a table or print the data frame, the "sales" values are displayed with decimal places (e.g., 10.00) and the "year" values are also showing decimal parts (e.g., 2019.00).

I'd like to know how to format these columns so that they are displayed as integers without any decimal places. I've tried using the format() function, but it doesn't seem to affect these specific columns.

Here's a snippet of my current code:

# Data
sales <- c(10, 30, 50, 20, 40)
revenue <- c(100.25, 300.50, 500.75, 200.00, 400.50)
profit <- c(10.4, 200.7, 400.5, 100.00, 356.79)
year <- c(2019:2023)
data <- rbind(Year = year, Sales = sales, Revenue = revenue, Profit = profit)

# Create a new table with different rows
Table <- kbl(data,
             caption = "Development",
             booktabs = TRUE,
             linesep = c("\\addlinespace[0.2cm]"),
             align = c("l", "r")) %>% 
  kable_styling(latex_options = c("striped"), font_size = 10, position = "left")

Table

Any guidance on how to achieve this formatting for "sales" and "year" columns would be greatly appreciated. Thank you in advance for your help!

Answer 1

Not sure how well included reprex describes your actual dataset format. If you start with Year / Sales / Revenue / Profit columns, you could first format all columns as strings, transpose to get a column for every year in a character matrix and optionally turn it back to data.frame ( kbl() will handle a matrix as well ).

library(kableExtra)
library(dplyr, warn.conflicts = FALSE)

# Data
sales <- c(10, 30, 50, 20, 40)
revenue <- c(100.25, 300.50, 500.75, 200.00, 400.50)
profit <- c(10.4, 200.7, 400.5, 100.00, 356.79)
year <- c(2019:2023)

# reprex from Q, a numeric matrix:
data_old <- rbind(Year = year, Sales = sales, Revenue = revenue, Profit = profit)
data_old
#>            [,1]   [,2]    [,3] [,4]    [,5]
#> Year    2019.00 2020.0 2021.00 2022 2023.00
#> Sales     10.00   30.0   50.00   20   40.00
#> Revenue  100.25  300.5  500.75  200  400.50
#> Profit    10.40  200.7  400.50  100  356.79
str(data_old)
#>  num [1:4, 1:5] 2019 10 100.2 10.4 2020 ...
#>  - attr(*, "dimnames")=List of 2
#>   ..$ : chr [1:4] "Year" "Sales" "Revenue" "Profit"
#>   ..$ : NULL

# a more conventional representation of that dataset:
data_new <- data.frame(Year = year, Sales = sales, Revenue = revenue, Profit = profit)
data_new
#>   Year Sales Revenue Profit
#> 1 2019    10  100.25  10.40
#> 2 2020    30  300.50 200.70
#> 3 2021    50  500.75 400.50
#> 4 2022    20  200.00 100.00
#> 5 2023    40  400.50 356.79
str(data_new)
#> 'data.frame':    5 obs. of  4 variables:
#>  $ Year   : int  2019 2020 2021 2022 2023
#>  $ Sales  : num  10 30 50 20 40
#>  $ Revenue: num  100 300 501 200 400
#>  $ Profit : num  10.4 200.7 400.5 100 356.8

data_wide <- data_new %>% 
  mutate(across(everything(), format)) %>% 
  tibble::column_to_rownames("Year") %>% 
  t() %>% 
  as.data.frame()
data_wide
#>           2019   2020   2021   2022   2023
#> Sales       10     30     50     20     40
#> Revenue 100.25 300.50 500.75 200.00 400.50
#> Profit   10.40 200.70 400.50 100.00 356.79
str(data_wide)
#> 'data.frame':    3 obs. of  5 variables:
#>  $ 2019: chr  "10" "100.25" " 10.40"
#>  $ 2020: chr  "30" "300.50" "200.70"
#>  $ 2021: chr  "50" "500.75" "400.50"
#>  $ 2022: chr  "20" "200.00" "100.00"
#>  $ 2023: chr  "40" "400.50" "356.79"

kbl(data_wide,
    caption = "Development",
    booktabs = TRUE,
    linesep = c("\\addlinespace[0.2cm]"),
    align = c("l", "r")) %>% 
  kable_styling(latex_options = c("striped"), font_size = 10, position = "left")

^{Created on 2023-08-31 with reprex v2.0.2}

Answer 2

Thank you for your assistance; I have now resolved it using the following solution.

# Data
sales <- c(10, 30, 50, 20, 40)
revenue <- round(c(100.25, 300.50, 500.75, 200.00, 400.50),1)
profit <- round(c(10.4, 200.7, 400.5, 100.00, 356.79),1)
year <- c(2019:2023)
data <- rbind(Year = year, Sales = sales, Revenue = revenue, Profit = profit)
colnames(data) <- data[1,]; data <- data[-1,]
data <- as.data.frame(data)

data <- data %>%
  rowwise()%>%
  mutate(across(everything(), ~ if(cur_group_id() >= 2) format(.x, nsmall = 1) else as.character(.x)))

# Create a new table with different rows
Table <- kbl(data,
               caption = "Development",
               booktabs = TRUE,
               linesep = c("\\addlinespace[0.2cm]")) %>% 
  kable_styling(latex_options = c("striped"), font_size = 10, position = "left")

Table