how can I make a nice table from summary()

[Erica]

Hi there,

I want to make a nice summary table of of all variables in my dataset
I used summary(df) and stored the result as an object,

next I used write.xlsx(object), the issue is that it writes it to an excel file with the names and the value combined in each cell so the excel file looks like

Variable      Variable 
 Mean: 1       mean: 3
 sd: 2         sd: 4
 NAs: 0        NAs: 5

instead of looking like

      Variable   Variable
Mean     1          3
sd       2          4
NAs      0          5

Any suggestions? I am open to changing the way I create the excel or creating an html file, whatever will look nice that I will be able to upload to shared folders etc
any suggestions?

Thanks so much!

[siddharthprabhu]

What you need is an alternative summary() function that produces a table-like object. Try using the skimr package.

library(skimr)

iris_summary <- skim(iris)

print(iris_summary)
#> -- Data Summary ------------------------
#>                            Values
#> Name                       iris  
#> Number of rows             150   
#> Number of columns          5     
#> _______________________          
#> Column type frequency:           
#>   factor                   1     
#>   numeric                  4     
#> ________________________         
#> Group variables            None  
#> 
#> -- Variable type: factor -------------------------------------------------------
#> # A tibble: 1 x 6
#>   skim_variable n_missing complete_rate ordered n_unique
#> * <chr>             <int>         <dbl> <lgl>      <int>
#> 1 Species               0             1 FALSE          3
#>   top_counts               
#> * <chr>                    
#> 1 set: 50, ver: 50, vir: 50
#> 
#> -- Variable type: numeric ------------------------------------------------------
#> # A tibble: 4 x 11
#>   skim_variable n_missing complete_rate  mean    sd    p0   p25   p50   p75
#> * <chr>             <int>         <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
#> 1 Sepal.Length          0             1  5.84 0.828   4.3   5.1  5.8    6.4
#> 2 Sepal.Width           0             1  3.06 0.436   2     2.8  3      3.3
#> 3 Petal.Length          0             1  3.76 1.77    1     1.6  4.35   5.1
#> 4 Petal.Width           0             1  1.20 0.762   0.1   0.3  1.3    1.8
#>    p100 hist 
#> * <dbl> <chr>
#> 1   7.9 <U+2586><U+2587><U+2587><U+2585><U+2582>
#> 2   4.4 <U+2581><U+2586><U+2587><U+2582><U+2581>
#> 3   6.9 <U+2587><U+2581><U+2586><U+2587><U+2582>
#> 4   2.5 <U+2587><U+2581><U+2587><U+2585><U+2583>

^{Created on 2020-06-02 by the reprex package (v0.3.0)}

The resulting object is a tibble which you can export to an Excel file.