Load package
Import data
Add summary of data
Change name row
Make table
Add the header row
Change names of minor column headers
Merge collumn and row
Alignment
Borders and background
Fix border issues
Add vertical lines
Font
Background color

Load package

pacman::p_load(
  flextable,
  tidyverse,
  dplyr,
  forcats
)

Import data

pipo <- read.csv("C:/Users/thuyb/OneDrive - Universiteit Antwerpen/University of Antwerp/Semester 1/Data Management/Practice/Practical session/Practical session 5/data_visualisation_2023/3_tables/PIPO_R.csv")
sapply(pipo, function(x) sum(is.na(x)))

##       WEIGHT Gender_Child       LENGTH      FEV1pre       FVCpre          AGE 
##          692           62          692          694          694          692 
##           ID 
##            0

remove(pipo_1)

## Warning in remove(pipo_1): object 'pipo_1' not found

pipo_1 <- pipo %>% 
  filter(!is.na(FEV1pre))
str(pipo_1)

## 'data.frame':    496 obs. of  7 variables:
##  $ WEIGHT      : num  25 27 26.5 34 27 27 24.5 41 26.5 26.5 ...
##  $ Gender_Child: int  1 1 2 2 2 1 2 1 1 1 ...
##  $ LENGTH      : num  137 128 132 135 130 ...
##  $ FEV1pre     : num  1.74 1.89 1.74 1.94 1.67 1.72 1.77 1.91 2.1 1.53 ...
##  $ FVCpre      : num  2 2.21 1.98 2.18 1.81 1.89 2.17 2.31 2.32 1.86 ...
##  $ AGE         : num  8.67 8.51 8.96 8.44 8.39 8.47 8.35 9.31 8.24 8.22 ...
##  $ ID          : int  207 263 581 114 835 228 958 542 908 190 ...

pipo_1$Gender_Child <- as.factor(pipo_1$Gender_Child)

pipo_summary <-  pipo_1 %>% 
  group_by(Gender_Child) %>% 
  summarise(N = n(),
            FEV1_median = round(median(FEV1pre), 2),
            FEV1_iqr = round(IQR(FEV1pre),2),
            FVC_median = round(median(FVCpre),2),
            FVC_iqr = round(IQR(FVCpre),2))
pipo_summary

## # A tibble: 2 × 6
##   Gender_Child     N FEV1_median FEV1_iqr FVC_median FVC_iqr
##   <fct>        <int>       <dbl>    <dbl>      <dbl>   <dbl>
## 1 1              257        1.94     0.38       2.28    0.42
## 2 2              239        1.85     0.33       2.11    0.41

Add summary of data

pipo_summary <- pipo_summary %>%
  bind_rows(                                        
    pipo_1 %>% 
    summarise(N = n(),                                          
        FEV1_median = round(median(FEV1pre), 2),
        FEV1_iqr = round(IQR(FEV1pre), 2),
        FVC_median = round(median(FVCpre), 2),
        FVC_iqr = round(IQR(FVCpre), 2)))

pipo_summary

## # A tibble: 3 × 6
##   Gender_Child     N FEV1_median FEV1_iqr FVC_median FVC_iqr
##   <fct>        <int>       <dbl>    <dbl>      <dbl>   <dbl>
## 1 1              257        1.94     0.38       2.28    0.42
## 2 2              239        1.85     0.33       2.11    0.41
## 3 <NA>           496        1.89     0.36       2.18    0.43

Change name row

pipo_summary <- pipo_summary %>% 
  mutate(Gender_Child = forcats::fct_na_value_to_level(Gender_Child, "Total"))
pipo_summary

## # A tibble: 3 × 6
##   Gender_Child     N FEV1_median FEV1_iqr FVC_median FVC_iqr
##   <fct>        <int>       <dbl>    <dbl>      <dbl>   <dbl>
## 1 1              257        1.94     0.38       2.28    0.42
## 2 2              239        1.85     0.33       2.11    0.41
## 3 Total          496        1.89     0.36       2.18    0.43

Change categories of the Gender to “Boys” and “Girls” instead of 1 and 2 (1=boy, 2=girl)

pipo_summary <- pipo_summary %>% 
  mutate(Gender_Child=recode(Gender_Child, '1'="Boy", '2'="Girl"))
pipo_summary

## # A tibble: 3 × 6
##   Gender_Child     N FEV1_median FEV1_iqr FVC_median FVC_iqr
##   <fct>        <int>       <dbl>    <dbl>      <dbl>   <dbl>
## 1 Boy            257        1.94     0.38       2.28    0.42
## 2 Girl           239        1.85     0.33       2.11    0.41
## 3 Total          496        1.89     0.36       2.18    0.43

Make table

?flextable

## starting httpd help server ... done

my_table <- flextable(pipo_summary)
my_table

Gender_Child	N	FEV1_median	FEV1_iqr	FVC_median	FVC_iqr
Boy	257	1.94	0.38	2.28	0.42
Girl	239	1.85	0.33	2.11	0.41
Total	496	1.89	0.36	2.18	0.43

Add the header row

my_table <-  add_header_row(my_table, value = c("Gender", "N", "FEV1", "", "FVC1", "" ))
my_table

Gender	N	FEV1		FVC1
Gender_Child	N	FEV1_median	FEV1_iqr	FVC_median	FVC_iqr
Boy	257	1.94	0.38	2.28	0.42
Girl	239	1.85	0.33	2.11	0.41
Total	496	1.89	0.36	2.18	0.43

Change names of minor column headers

my_table <-  set_header_labels(my_table,
                  Gender_Child = "",
                  N = "",
                  FEV1_median = "Median",
                  FEV1_iqr = "IQR",
                  FVC_median = "Median",
                  FVC_iqr = "IQR")
my_table

Gender	N	FEV1		FVC1
		Median	IQR	Median	IQR
Boy	257	1.94	0.38	2.28	0.42
Girl	239	1.85	0.33	2.11	0.41
Total	496	1.89	0.36	2.18	0.43

Merge collumn and row

#i’ specifies the row, ‘j’ specifies the columns and ‘part’ specifies whether you want to merge ‘headers’ or ‘body’ of the table
my_table <- my_table %>% 
  merge_at(i= 1, j=3:4, part = "header") %>%  
  merge_at(i= 1, j=5:6, part = "header") %>% 
  merge_at(i= 1:2, j=1, part = "header") %>% 
  merge_at(i= 1:2, j=2, part = "header")
my_table

Gender	N	FEV1		FVC1
Gender	N	Median	IQR	Median	IQR
Boy	257	1.94	0.38	2.28	0.42
Girl	239	1.85	0.33	2.11	0.41
Total	496	1.89	0.36	2.18	0.43

Alignment

my_table <- align(my_table,  align = "center", part = "all")
my_table

Gender	N	FEV1		FVC1
Gender	N	Median	IQR	Median	IQR
Boy	257	1.94	0.38	2.28	0.42
Girl	239	1.85	0.33	2.11	0.41
Total	496	1.89	0.36	2.18	0.43

Borders and background

Remove the borders (border_remove()) and add horizontal lines with hline_top, hline_bottom and hline:

Above the major header
Below the minor header
Below the last row of the table

my_table <- my_table %>% 
  border_remove() %>% 
  hline_top(part = "header") %>%
  hline_top(part="body") %>% 
  hline_bottom() %>% 
  hline(i=2) #the second row without 2 header
my_table

Gender	N	FEV1		FVC1
Gender	N	Median	IQR	Median	IQR
Boy	257	1.94	0.38	2.28	0.42
Girl	239	1.85	0.33	2.11	0.41
Total	496	1.89	0.36	2.18	0.43

Fix border issues

my_table <- my_table %>% 
  fix_border_issues()
my_table

Gender	N	FEV1		FVC1
Gender	N	Median	IQR	Median	IQR
Boy	257	1.94	0.38	2.28	0.42
Girl	239	1.85	0.33	2.11	0.41
Total	496	1.89	0.36	2.18	0.43

Add vertical lines

my_table <- my_table %>% 
  vline_left() %>% 
  vline_right() %>% 
  vline(j=c(1,2,4)) %>% 
  fix_border_issues() 
my_table

Gender	N	FEV1		FVC1
Gender	N	Median	IQR	Median	IQR
Boy	257	1.94	0.38	2.28	0.42
Girl	239	1.85	0.33	2.11	0.41
Total	496	1.89	0.36	2.18	0.43

Font

my_table <-  my_table %>%  
  fontsize(i = 1, size = 12, part = "header") %>%   # adjust font size of major header (row 1 of header)
  bold(i = 1, bold = TRUE, part = "header") %>%      # adjust bold face of header (row 1 and 2 of header)
  bold(i = 3, bold = TRUE, part = "body")
my_table

Gender	N	FEV1		FVC1
Gender	N	Median	IQR	Median	IQR
Boy	257	1.94	0.38	2.28	0.42
Girl	239	1.85	0.33	2.11	0.41
Total	496	1.89	0.36	2.18	0.43

Background color

my_table <- my_table %>% 
  bg(bg = "#9FCC4B", part = "header") %>% 
  bg(bg = "#F9FADE", part = "body")
my_table

Gender	N	FEV1		FVC1
Gender	N	Median	IQR	Median	IQR
Boy	257	1.94	0.38	2.28	0.42
Girl	239	1.85	0.33	2.11	0.41
Total	496	1.89	0.36	2.18	0.43

Tables

Thanh Thuy Bui

Load package

Import data

Add summary of data

Change name row

Make table

Add the header row

Change names of minor column headers

Merge collumn and row

Alignment

Borders and background

Fix border issues

Add vertical lines

Font

Background color