Export tibbles to Excel

General
1

Hello Community members!

I'm looking for help as I'm learning RStudio.
I am trying to export in an efficient way an output that has the following structure, which comes from dataframe:

rstudio
rstudio1114×51 13.7 KB

results$irf_lin_mean
[,1] [,2] [,3] [,4] [,5] [,6] [,7] [,8] [,9] [,10] [,11] [,12]
[1,] 0.1288852 -0.01553665 -0.08035717 -0.06080564 0.04415783 0.1586054 -0.05872253 0.1637892 -0.2667759 0.2672431 -0.03526649 -0.1207621

I sincerely don't know how to write that on Excel and copying each element in each cell, and if I can round them it would be better.

I am also using another command of the function that displays different tibbles, how can I export one of them (e.g. they start with $x_lin) to Excel?

Thank you in advance.

2

It looks like you have a matrix instead of a tibble. In any case, you can use the write.xlsx function of the openxlsx package. Also, you can round the values of the matrix with the round() function.

MAT <- matrix(rnorm(24), nrow = 6)
MAT
#>             [,1]       [,2]       [,3]         [,4]
#> [1,] -0.49577634 -0.3764917  1.4474626 -0.320157371
#> [2,] -0.47712991 -0.2016727 -1.5090314  1.332959866
#> [3,] -0.08505565 -0.8829869 -0.7956639  0.152407869
#> [4,] -0.48298904 -0.9057929  1.8238755 -0.197606816
#> [5,]  1.80832951  0.5135401  0.1134887 -1.145556781
#> [6,]  1.99126703  0.5218464 -0.3390569  0.005652402
MAT <- round(MAT, 3)
MAT
#>        [,1]   [,2]   [,3]   [,4]
#> [1,] -0.496 -0.376  1.447 -0.320
#> [2,] -0.477 -0.202 -1.509  1.333
#> [3,] -0.085 -0.883 -0.796  0.152
#> [4,] -0.483 -0.906  1.824 -0.198
#> [5,]  1.808  0.514  0.113 -1.146
#> [6,]  1.991  0.522 -0.339  0.006
openxlsx::write.xlsx(MAT, "/home/fjcc/R/Play/MyExcel.xlsx")

Created on 2020-04-26 by the reprex package (v0.2.1)

3

Thank you FJCC, I was trying write write_xlsx and I was not aware of the "openxlsx" function.

If I have three of those vectors ($irf_lin_mean,$irf_lin_max,$irf_lin_min), how could I append them in order to generate the Excel output afterwards, avoiding the headers of the last two?

I have tried to round another matrix but I am not allowed as there is a letter:
" R-sqrd. Adj. R-sqrd. F-stat p-value
h 1 0.4740820 0.20205541 1.7427782 0.097212157
h 2 0.5577194 0.32078330 2.3538813 0.024436442
h 3 0.5303938 0.26950153 2.0329991 0.052639206
h 4 0.5414593 0.27691654 2.0467743 0.052835056
h 5 0.5941959 0.35071339 2.4404051 0.023482988
h 6 0.6600040 0.44750648 3.1059375 0.006535617
h 7 0.3406962 -0.08928446 0.7923524 0.674243140
h 8 0.3453960 -0.10092496 0.7738736 0.691089683
h 9 0.5483948 0.22581973 1.7000532 0.129006075
h 10 0.5535230 0.21866525 1.6530094 0.145541515
h 11 0.5060875 0.11615661 1.2978902 0.292111968
h 12 0.6234552 0.30966785 1.9868717 0.083210391"

Do you know how to avoid this?

Thank you again.

4

The code to write three data sets into a sheet would look something like this. I have not tested it so do not be surprised if an error pops up.

library(openxlsx)
#Make a workbook
wb <- createWorkbook()
#Add a sheet to the workbook
addWorksheet(wb, "MyData")

writeData(wb, sheet = "MyData", x = results$irf_lin_mean, 
          startCol = 1, startRow = 1, colNames = TRUE)
NumOfRows <- nrow(results$irf_lin_mean)

writeData(wb, sheet = "MyData", x = results$irf_lin_min, 
          startCol = 1, startRow = NumOfRows+1, colNames = FALSE)

NumOfRows <- NumOfRows + nrow(results$irf_lin_min) + 1

writeData(wb, sheet = "MyData", x = results$irf_lin_max, 
          startCol = 1, startRow = NumOfRows+1, colNames = FALSE)

saveWorkbook(wb, "MyNewFile.xlsx", overwrite = TRUE)

For your rounding problem, it would be easier if I had that data. Please run the command

dput(head(NameOfTheObject))

(where NameOfTheObject is replaced with the actual varable) and paste the results here. Be sure to place three back ticks (```) on the lines before and after the text you paste.

5

Hey FJCC,

Regarding the code, I marginally modified the code and works perfect! Thanks

For the data:

list(`Endog. Variable: GDP` = structure(c(0.474081974590918, 
0.557719359210806, 0.530393839388452, 0.541459269211916, 0.594195871489285, 
0.660003986400042, 0.340696249858606, 0.345395971003976, 0.548394843658558, 
0.553523000333392, 0.506087515063566, 0.623455192323393, 0.202055409724151, 
0.320783301645167, 0.269501527937592, 0.276916539911099, 0.350713394382856, 
0.447506477900068, -0.0892844567553466, -0.100924957856949, 0.2258197319861, 
0.218665250583435, 0.116156605903223, 0.309667852592888, 1.74277822764521, 
2.35388131692998, 2.03299911921074, 2.04677433639165, 2.44040506959996, 
3.10593752867518, 0.792352411673419, 0.773873570942496, 1.70005317773987, 
1.6530093771063, 1.29789022407418, 1.98687172292816, 0.0972121570824099, 
0.0244364421564119, 0.0526392055985252, 0.0528350564492983, 0.023482988228381, 
0.00653561720347269, 0.674243140276974, 0.691089683136054, 0.129006074746146, 
0.145541515438787, 0.292111968277419, 0.0832103912139589), .Dim = c(12L, 
4L), .Dimnames = list(c("h 1", "h 2", "h 3", "h 4", "h 5", "h 6", 
"h 7", "h 8", "h 9", "h 10", "h 11", "h 12"), c("R-sqrd.", "Adj. R-sqrd.", 
"F-stat", " p-value"))))```

If I can keep the "h x" as well it'd be great.

Thank you!
6

With the data you posted, I rounded it like this.

DATA <- list(`Endog. Variable: GDP` = structure(c(0.474081974590918, 
                                          0.557719359210806, 0.530393839388452, 0.541459269211916, 0.594195871489285, 
                                          0.660003986400042, 0.340696249858606, 0.345395971003976, 0.548394843658558, 
                                          0.553523000333392, 0.506087515063566, 0.623455192323393, 0.202055409724151, 
                                          0.320783301645167, 0.269501527937592, 0.276916539911099, 0.350713394382856, 
                                          0.447506477900068, -0.0892844567553466, -0.100924957856949, 0.2258197319861, 
                                          0.218665250583435, 0.116156605903223, 0.309667852592888, 1.74277822764521, 
                                          2.35388131692998, 2.03299911921074, 2.04677433639165, 2.44040506959996, 
                                          3.10593752867518, 0.792352411673419, 0.773873570942496, 1.70005317773987, 
                                          1.6530093771063, 1.29789022407418, 1.98687172292816, 0.0972121570824099, 
                                          0.0244364421564119, 0.0526392055985252, 0.0528350564492983, 0.023482988228381, 
                                          0.00653561720347269, 0.674243140276974, 0.691089683136054, 0.129006074746146, 
                                          0.145541515438787, 0.292111968277419, 0.0832103912139589), .Dim = c(12L, 
                                                                                                              4L), .Dimnames = list(c("h 1", "h 2", "h 3", "h 4", "h 5", "h 6", 
                                                                                                                                      "h 7", "h 8", "h 9", "h 10", "h 11", "h 12"), c("R-sqrd.", "Adj. R-sqrd.", 
                                                                                                                                                                                      "F-stat", " p-value"))))

RoundData <- round(DATA$`Endog. Variable: GDP`, 4)
RoundData
#>      R-sqrd. Adj. R-sqrd. F-stat  p-value
#> h 1   0.4741       0.2021 1.7428   0.0972
#> h 2   0.5577       0.3208 2.3539   0.0244
#> h 3   0.5304       0.2695 2.0330   0.0526
#> h 4   0.5415       0.2769 2.0468   0.0528
#> h 5   0.5942       0.3507 2.4404   0.0235
#> h 6   0.6600       0.4475 3.1059   0.0065
#> h 7   0.3407      -0.0893 0.7924   0.6742
#> h 8   0.3454      -0.1009 0.7739   0.6911
#> h 9   0.5484       0.2258 1.7001   0.1290
#> h 10  0.5535       0.2187 1.6530   0.1455
#> h 11  0.5061       0.1162 1.2979   0.2921
#> h 12  0.6235       0.3097 1.9869   0.0832

Created on 2020-04-26 by the reprex package (v0.3.0)

7

Thanks.

I got different errors when trying:

try2<-round(results$diagnostic_list,2)
Error in round(results$diagnostic_list, 2) :
non-numeric argument to mathematical function

try2<-round(results$Endog. Variable: GDP,2)
Error in round(results$Endog. Variable: GDP, 2) :
non-numeric argument to mathematical function

What could be wrong?
Also, If the second one could work, how could I make a change of variable? like changing "GDP" to other name, and still be recognized as an input?

Thanks.

8

I do not have your data so it is very hard to tell what is going wrong. What is the result of

str(results)

What function produces the object results? The documentation of that function might help me understand better.

9

Hey FJCC,

My e-mail was sending the posts to junk-mails:

here it is the output:

str(results)
List of 5

 $ irf_lin_low    : num [1, 1:12] 0.2967 0.5602 -0.2547 -0.2151 -0.0722 ...
 $ irf_lin_up     : num [1, 1:12] 2.04 2.4 1.05 1.35 1.31 ...
 $ diagnostic_list:List of 1
  ..$ Endog. Variable: GDP: num [1:12, 1:4] 0.474 0.558 0.53 0.541 0.594 ...
  .. ..- attr(*, "dimnames")=List of 2
  .. .. ..$ : chr [1:12] "h 1" "h 2" "h 3" "h 4" ...
  .. .. ..$ : chr [1:4] "R-sqrd." "Adj. R-sqrd." "F-stat" " p-value"
 $ specs          :List of 20
  ..$ shock         :Classes ‘tbl_df’, ‘tbl’ and 'data.frame':	49 obs. of  1 variable:
  .. ..$ Shock: num [1:49] 0.00373 -0.00137 0.00802 -0.00149 -0.00116 ...
  ..$ use_twosls    : logi FALSE
  ..$ lags_endog_lin: num 4
  ..$ exog_data     :Classes ‘tbl_df’, ‘tbl’ and 'data.frame':	49 obs. of  1 variable:
  .. ..$ WTIIndex     : num [1:49] 0.1163 0.1593 0.206 0.0777 0.2671 ...
  ..$ lags_exog     : num 0
  ..$ lags_criterion: num NaN
  ..$ max_lags      : num NaN
  ..$ trend         : num 0
  ..$ confint       : num 1.96
  ..$ hor           : num 12
  ..$ use_nw        : logi TRUE
  ..$ nw_prewhite   : logi FALSE
  ..$ adjust_se     : logi FALSE
  ..$ model_type    : num 1
  ..$ starts        : num 1
  ..$ ends          : int 49
  ..$ column_names  : chr "GDP"
  ..$ endog         : int 1
  ..$ y_lin         : num [1:45, 1] 0.00187 -0.00885 -0.00869 -0.01056 -0.00482 ...
  ..$ x_lin         : num [1:45, 1:15] -1.16e-03 -1.11e-02 -9.48e-03 2.64e-05 1.52e-02 ...
  .. ..- attr(*, "dimnames")=List of 2
  .. .. ..$ : chr [1:45] "5" "6" "7" "8" ...
  .. .. ..$ : chr [1:15] "shock" "GDP_lag_1" "GDP_lag_2" "GDP_lag_3" ...
 - attr(*, "class")= chr "lpirfs_lin_iv_obj"```


The function I'm using is lpirfs. https://cran.r-project.org/web/packages/lpirfs/lpirfs.pdf


Thank you so much!
10

You can see from the following part of the result of the str() function

diagnostic_list:List of 1
  ..$ Endog. Variable: GDP: num [1:12, 1:4] 0.474 0.558 0.53 0.541 0.594 ...

that results$diangnostic_list is itself a list of length 1. The one element of that list is a numeric matrix of 12 rows and 4 columns. You can get a rounded version of that matrix with

round(results$diangnostic_list[[1]], 2)

This should also work

round(results$diangnostic_list[[`Endog. Variable: GDP:`]], 2)
11

Hey FJCC,

It works nice, thank you so much, you have helped me a lot, I cannot thank you enough!

Have a good week.

12

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