DT table modifiying column display for large numbers

Thibaud · October 25, 2022, 1:18pm

Hola tod@s,

I am currently running a report in Rmarkdown and I am experiencing a display problem

When I have a large number in my data table, the number appears to be overwritten in the display column

To illustrate:

library(DT) 

 df <- data.frame (first_column  = c("2", "1500000"),
                    second_column = c("89998", "7475")
  )
  
  
  datatable(df,
            rownames = FALSE,
            caption = "OK",
            options = list(
              columnDefs = list(list(className = 'dt-center', targets = 0:1))
            ))

I would like to know if it is possible to solve this?

Thanking you in advance for your time and wishing you a pleasant day,

technocrat · October 25, 2022, 9:25pm

To get the delimiters, it's necessary to explicitly format the numeric values as character strings. One way to do this would be

df <- data.frame (first_column  = prettyNum(c("2", "1500000"),big.mark = ","),
                  second_column = prettyNum(c("89998", "7475"),big.mark = ","
))

Thibaud · October 26, 2022, 12:46pm

Wonderful ! It works even better by adding a space just like that (more natural than two commas in my natural language) :


df <- data.frame (first_column  = prettyNum(c("2", "1500000"),big.mark = " "),
                  second_column = prettyNum(c("89998", "7475"),big.mark = " "
                  ))

I just would like to know if you know how to implement it directly into the DT commands (I need to load my .csv/.xlsx file and then plot the DT way) ... Where would it fit in the syntax ? Into the datatable function or after by modifying the column format ?


# The way I am currently doing for loading data and plot it the DT way 

datatable(df,
          rownames = FALSE,
          caption = "OK",
          options = list(
          columnDefs = list(list(className = 'dt-center', targets = 0:1))
          ))

Thanks in advance, it would be perfect !

technocrat · October 27, 2022, 4:46am

I'd bring in the csv with

my_dt <- data.table(fread("path_to_csv file/your_file.csv")

then consider keeping the values of the numeric column as numeric and wait until preparing a table for distribution to do the formatting. The {gt} package, which does tables very well, has a function fmt_number() with a sep_mark argument that works the same way, in your case sep_mark = " ". If you do want to modify the data in the data.table, however, it would be just applying prettyNum() to your numeric columns to convert them to formatted character typeof.

Thibaud · November 14, 2022, 6:20pm

Hola,

Sorry for the re-opening again the subject,

But could you explain me how do you write your data.table() function to include the prettyNum() parameter for choosen column of the table please ?

I need to use the DT:package to do so, i would really help me !

Thanks in advance !

technocrat · November 14, 2022, 11:29pm

Not sure I understood the question, but

DT <- data.table::data.table(first_column  = prettyNum(c("2", "1500000"),big.mark = " "),
                     second_column = prettyNum(c("89998", "7475"),big.mark = " "
                     ))
DT
#>    first_column second_column
#> 1:            2        89 998
#> 2:    1 500 000         7 475

Again, you are better off dropping prettyNum() from the data.table and piping to a formatter, such as {gt}, which is almost as customizable as {ggplot2}

library(data.table)
library(gt)

DT <- data.table::data.table(first_column  = c(2,1500000),
                     second_column = c(89998,7475)
)

DT
#>    first_column second_column
#> 1:            2         89998
#> 2:      1500000          7475
sep <- " " # because a single space may be difficult to see in coding
DT |> gt() |> fmt_number(columns = everything(), 
                         decimals = 0,
                         sep_mark = sep)

html { font-family: -apple-system, BlinkMacSystemFont, 'Segoe UI', Roboto, Oxygen, Ubuntu, Cantarell, 'Helvetica Neue', 'Fira Sans', 'Droid Sans', Arial, sans-serif; }

#czlvrpygmv .gt_table {
display: table;
border-collapse: collapse;
margin-left: auto;
margin-right: auto;
color: #333333;
font-size: 16px;
font-weight: normal;
font-style: normal;
background-color: #FFFFFF;
width: auto;
border-top-style: solid;
border-top-width: 2px;
border-top-color: #A8A8A8;
border-right-style: none;
border-right-width: 2px;
border-right-color: #D3D3D3;
border-bottom-style: solid;
border-bottom-width: 2px;
border-bottom-color: #A8A8A8;
border-left-style: none;
border-left-width: 2px;
border-left-color: #D3D3D3;
}

#czlvrpygmv .gt_heading {
background-color: #FFFFFF;
text-align: center;
border-bottom-color: #FFFFFF;
border-left-style: none;
border-left-width: 1px;
border-left-color: #D3D3D3;
border-right-style: none;
border-right-width: 1px;
border-right-color: #D3D3D3;
}

#czlvrpygmv .gt_title {
color: #333333;
font-size: 125%;
font-weight: initial;
padding-top: 4px;
padding-bottom: 4px;
padding-left: 5px;
padding-right: 5px;
border-bottom-color: #FFFFFF;
border-bottom-width: 0;
}

#czlvrpygmv .gt_subtitle {
color: #333333;
font-size: 85%;
font-weight: initial;
padding-top: 0;
padding-bottom: 6px;
padding-left: 5px;
padding-right: 5px;
border-top-color: #FFFFFF;
border-top-width: 0;
}

#czlvrpygmv .gt_bottom_border {
border-bottom-style: solid;
border-bottom-width: 2px;
border-bottom-color: #D3D3D3;
}

#czlvrpygmv .gt_col_headings {
border-top-style: solid;
border-top-width: 2px;
border-top-color: #D3D3D3;
border-bottom-style: solid;
border-bottom-width: 2px;
border-bottom-color: #D3D3D3;
border-left-style: none;
border-left-width: 1px;
border-left-color: #D3D3D3;
border-right-style: none;
border-right-width: 1px;
border-right-color: #D3D3D3;
}

#czlvrpygmv .gt_col_heading {
color: #333333;
background-color: #FFFFFF;
font-size: 100%;
font-weight: normal;
text-transform: inherit;
border-left-style: none;
border-left-width: 1px;
border-left-color: #D3D3D3;
border-right-style: none;
border-right-width: 1px;
border-right-color: #D3D3D3;
vertical-align: bottom;
padding-top: 5px;
padding-bottom: 6px;
padding-left: 5px;
padding-right: 5px;
overflow-x: hidden;
}

#czlvrpygmv .gt_column_spanner_outer {
color: #333333;
background-color: #FFFFFF;
font-size: 100%;
font-weight: normal;
text-transform: inherit;
padding-top: 0;
padding-bottom: 0;
padding-left: 4px;
padding-right: 4px;
}

#czlvrpygmv .gt_column_spanner_outer:first-child {
padding-left: 0;
}

#czlvrpygmv .gt_column_spanner_outer:last-child {
padding-right: 0;
}

#czlvrpygmv .gt_column_spanner {
border-bottom-style: solid;
border-bottom-width: 2px;
border-bottom-color: #D3D3D3;
vertical-align: bottom;
padding-top: 5px;
padding-bottom: 5px;
overflow-x: hidden;
display: inline-block;
width: 100%;
}

#czlvrpygmv .gt_group_heading {
padding-top: 8px;
padding-bottom: 8px;
padding-left: 5px;
padding-right: 5px;
color: #333333;
background-color: #FFFFFF;
font-size: 100%;
font-weight: initial;
text-transform: inherit;
border-top-style: solid;
border-top-width: 2px;
border-top-color: #D3D3D3;
border-bottom-style: solid;
border-bottom-width: 2px;
border-bottom-color: #D3D3D3;
border-left-style: none;
border-left-width: 1px;
border-left-color: #D3D3D3;
border-right-style: none;
border-right-width: 1px;
border-right-color: #D3D3D3;
vertical-align: middle;
}

#czlvrpygmv .gt_empty_group_heading {
padding: 0.5px;
color: #333333;
background-color: #FFFFFF;
font-size: 100%;
font-weight: initial;
border-top-style: solid;
border-top-width: 2px;
border-top-color: #D3D3D3;
border-bottom-style: solid;
border-bottom-width: 2px;
border-bottom-color: #D3D3D3;
vertical-align: middle;
}

#czlvrpygmv .gt_from_md > :first-child {
margin-top: 0;
}

#czlvrpygmv .gt_from_md > :last-child {
margin-bottom: 0;
}

#czlvrpygmv .gt_row {
padding-top: 8px;
padding-bottom: 8px;
padding-left: 5px;
padding-right: 5px;
margin: 10px;
border-top-style: solid;
border-top-width: 1px;
border-top-color: #D3D3D3;
border-left-style: none;
border-left-width: 1px;
border-left-color: #D3D3D3;
border-right-style: none;
border-right-width: 1px;
border-right-color: #D3D3D3;
vertical-align: middle;
overflow-x: hidden;
}

#czlvrpygmv .gt_stub {
color: #333333;
background-color: #FFFFFF;
font-size: 100%;
font-weight: initial;
text-transform: inherit;
border-right-style: solid;
border-right-width: 2px;
border-right-color: #D3D3D3;
padding-left: 5px;
padding-right: 5px;
}

#czlvrpygmv .gt_stub_row_group {
color: #333333;
background-color: #FFFFFF;
font-size: 100%;
font-weight: initial;
text-transform: inherit;
border-right-style: solid;
border-right-width: 2px;
border-right-color: #D3D3D3;
padding-left: 5px;
padding-right: 5px;
vertical-align: top;
}

#czlvrpygmv .gt_row_group_first td {
border-top-width: 2px;
}

#czlvrpygmv .gt_summary_row {
color: #333333;
background-color: #FFFFFF;
text-transform: inherit;
padding-top: 8px;
padding-bottom: 8px;
padding-left: 5px;
padding-right: 5px;
}

#czlvrpygmv .gt_first_summary_row {
border-top-style: solid;
border-top-color: #D3D3D3;
}

#czlvrpygmv .gt_first_summary_row.thick {
border-top-width: 2px;
}

#czlvrpygmv .gt_last_summary_row {
padding-top: 8px;
padding-bottom: 8px;
padding-left: 5px;
padding-right: 5px;
border-bottom-style: solid;
border-bottom-width: 2px;
border-bottom-color: #D3D3D3;
}

#czlvrpygmv .gt_grand_summary_row {
color: #333333;
background-color: #FFFFFF;
text-transform: inherit;
padding-top: 8px;
padding-bottom: 8px;
padding-left: 5px;
padding-right: 5px;
}

#czlvrpygmv .gt_first_grand_summary_row {
padding-top: 8px;
padding-bottom: 8px;
padding-left: 5px;
padding-right: 5px;
border-top-style: double;
border-top-width: 6px;
border-top-color: #D3D3D3;
}

#czlvrpygmv .gt_striped {
background-color: rgba(128, 128, 128, 0.05);
}

#czlvrpygmv .gt_table_body {
border-top-style: solid;
border-top-width: 2px;
border-top-color: #D3D3D3;
border-bottom-style: solid;
border-bottom-width: 2px;
border-bottom-color: #D3D3D3;
}

#czlvrpygmv .gt_footnotes {
color: #333333;
background-color: #FFFFFF;
border-bottom-style: none;
border-bottom-width: 2px;
border-bottom-color: #D3D3D3;
border-left-style: none;
border-left-width: 2px;
border-left-color: #D3D3D3;
border-right-style: none;
border-right-width: 2px;
border-right-color: #D3D3D3;
}

#czlvrpygmv .gt_footnote {
margin: 0px;
font-size: 90%;
padding-left: 4px;
padding-right: 4px;
padding-left: 5px;
padding-right: 5px;
}

#czlvrpygmv .gt_sourcenotes {
color: #333333;
background-color: #FFFFFF;
border-bottom-style: none;
border-bottom-width: 2px;
border-bottom-color: #D3D3D3;
border-left-style: none;
border-left-width: 2px;
border-left-color: #D3D3D3;
border-right-style: none;
border-right-width: 2px;
border-right-color: #D3D3D3;
}

#czlvrpygmv .gt_sourcenote {
font-size: 90%;
padding-top: 4px;
padding-bottom: 4px;
padding-left: 5px;
padding-right: 5px;
}

#czlvrpygmv .gt_left {
text-align: left;
}

#czlvrpygmv .gt_center {
text-align: center;
}

#czlvrpygmv .gt_right {
text-align: right;
font-variant-numeric: tabular-nums;
}

#czlvrpygmv .gt_font_normal {
font-weight: normal;
}

#czlvrpygmv .gt_font_bold {
font-weight: bold;
}

#czlvrpygmv .gt_font_italic {
font-style: italic;
}

#czlvrpygmv .gt_super {
font-size: 65%;
}

#czlvrpygmv .gt_two_val_uncert {
display: inline-block;
line-height: 1em;
text-align: right;
font-size: 60%;
vertical-align: -0.25em;
margin-left: 0.1em;
}

#czlvrpygmv .gt_footnote_marks {
font-style: italic;
font-weight: normal;
font-size: 75%;
vertical-align: 0.4em;
}

#czlvrpygmv .gt_asterisk {
font-size: 100%;
vertical-align: 0;
}

#czlvrpygmv .gt_slash_mark {
font-size: 0.7em;
line-height: 0.7em;
vertical-align: 0.15em;
}

#czlvrpygmv .gt_fraction_numerator {
font-size: 0.6em;
line-height: 0.6em;
vertical-align: 0.45em;
}

#czlvrpygmv .gt_fraction_denominator {
font-size: 0.6em;
line-height: 0.6em;
vertical-align: -0.05em;
}

first_column	second_column
2	89 998
1 500 000	7 475

Thibaud · November 21, 2022, 1:36pm

Hello,

Thanks for your answer and the discovery of this new package {gt} whiwh is finally very useful for my kind of problem

Thanks for all your answer, it does work (and better) now, it's really flexible

system · November 28, 2022, 1:36pm

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.

If you have a query related to it or one of the replies, start a new topic and refer back with a link.