CODEWITHSUNDEEP
rexcelsummary
Shery
Shery

Reputation: 1882

How to Copy Summary() output from R to Excel

Can you transfer / copy the output of a summary method (shown below) to an excel sheet?

> summary(p2p_dt_SKILL_A[p2p_dt_SKILL_A$Patch %in% c("BVG1")])
   Patch             Skill          Date               TOR_MWF          TOR_MA          TOR_DL          TOR_A        TT_PR_TSK_A      TT_PR_TSK_DL  
 BVG1 :100   A          :100   Min.   :2014-01-17   Min.   :4.050   Min.   :3.740   Min.   :3.720   Min.   :3.830   Min.   : 98.49   Min.   :104.8  
 BVG11:  0   BROADBAND  :  0   1st Qu.:2014-07-09   1st Qu.:4.718   1st Qu.:4.550   1st Qu.:3.958   1st Qu.:4.100   1st Qu.:105.07   1st Qu.:109.9  
 BVG12:  0   CAL        :  0   Median :2014-12-29   Median :5.205   Median :4.860   Median :4.040   Median :4.165   Median :109.48   Median :112.9  
 BVG13:  0   CIDT       :  0   Mean   :2014-12-29   Mean   :5.126   Mean   :4.884   Mean   :4.050   Mean   :4.214   Mean   :108.43   Mean   :112.7  
 BVG14:  0   COPPER_PROV:  0   3rd Qu.:2015-06-20   3rd Qu.:5.487   3rd Qu.:5.237   3rd Qu.:4.150   3rd Qu.:4.340   3rd Qu.:111.22   3rd Qu.:115.2  
 BVG15:  0   FTTC_CEASE :  0   Max.   :2015-12-11   Max.   :6.040   Max.   :5.990   Max.   :4.350   Max.   :4.630   Max.   :119.06   Max.   :122.6  
 BVG16:  0   (Other)    :  0                                                                                                                        
  TT_PR_TSK_MA    TT_PR_TSK_MWF    TASK_COUNT_MWF TASK_COUNT_MA    TASK_COUNT_DL    TASK_COUNT_A       SR_MWF          SR_MA           SR_DL      
 Min.   : 76.13   Min.   : 75.50   Min.   : 889   Min.   : 434.0   Min.   : 7391   Min.   : 8852   Min.   :81.18   Min.   :65.44   Min.   :76.30  
 1st Qu.: 87.06   1st Qu.: 83.10   1st Qu.:1318   1st Qu.: 561.5   1st Qu.: 9983   1st Qu.:12192   1st Qu.:85.67   1st Qu.:76.81   1st Qu.:81.45  
 Median : 93.83   Median : 87.61   Median :1907   Median : 714.0   Median :10450   Median :13487   Median :87.06   Median :83.24   Median :82.23  
 Mean   : 94.26   Mean   : 89.88   Mean   :2074   Mean   : 783.1   Mean   :10643   Mean   :13501   Mean   :87.09   Mean   :81.34   Mean   :82.16  
 3rd Qu.:100.22   3rd Qu.: 96.66   3rd Qu.:2745   3rd Qu.: 925.0   3rd Qu.:11356   3rd Qu.:14560   3rd Qu.:88.82   3rd Qu.:85.81   3rd Qu.:83.12  
 Max.   :121.93   Max.   :112.59   Max.   :4016   Max.   :1827.0   Max.   :13977   Max.   :17518   Max.   :91.84   Max.   :89.61   Max.   :84.32  

      SR_A       FURTHERS_COUNT_MWF FURTHERS_COUNT_MA FURTHERS_COUNT_DL FURTHERS_COUNT_A   SOD_FWIH_A       Prod_MWF        Prod_MA         Prod_DL     
 Min.   :77.03   Min.   :114.0      Min.   : 79.0     Min.   :1405      Min.   :1632     Min.   :1.050   Min.   :3.370   Min.   :2.450   Min.   :2.960  
 1st Qu.:81.94   1st Qu.:176.8      1st Qu.:113.0     1st Qu.:1714      1st Qu.:2072     1st Qu.:1.070   1st Qu.:4.117   1st Qu.:3.580   1st Qu.:3.237  
 Median :82.67   Median :262.5      Median :131.5     Median :1868      Median :2223     Median :1.110   Median :4.590   Median :4.005   Median :3.315  
 Mean   :82.91   Mean   :258.3      Mean   :136.2     Mean   :1904      Mean   :2298     Mean   :1.099   Mean   :4.466   Mean   :3.987   Mean   :3.328  
 3rd Qu.:84.22   3rd Qu.:324.2      3rd Qu.:159.5     3rd Qu.:2048      3rd Qu.:2448     3rd Qu.:1.120   3rd Qu.:4.820   3rd Qu.:4.418   3rd Qu.:3.408  
 Max.   :85.72   Max.   :484.0      Max.   :217.0     Max.   :3111      Max.   :3801     Max.   :1.150   Max.   :5.360   Max.   :5.240   Max.   :3.650  

     Prod_A      OTDFTD_RATE_A     EOD_FWIH_MWF    EOD_FWIH_MA     EOD_FWIH_DL      EOD_FWIH_A    ASSIST_RATE_MWF  ASSIST_RATE_MA   ASSIST_RATE_DL 
 Min.   :3.050   Min.   : 7.050   Min.   :1.090   Min.   :1.120   Min.   :1.190   Min.   :1.170   Min.   :0.0300   Min.   :0.0000   Min.   :0.570  
 1st Qu.:3.357   1st Qu.: 8.883   1st Qu.:1.130   1st Qu.:1.167   1st Qu.:1.200   1st Qu.:1.190   1st Qu.:0.2900   1st Qu.:0.2000   1st Qu.:0.970  
 Median :3.440   Median : 9.790   Median :1.150   Median :1.200   Median :1.220   Median :1.210   Median :0.4000   Median :0.3600   Median :1.100  
 Mean   :3.495   Mean   : 9.809   Mean   :1.149   Mean   :1.237   Mean   :1.218   Mean   :1.207   Mean   :0.4867   Mean   :0.4174   Mean   :1.196  
 3rd Qu.:3.632   3rd Qu.:10.845   3rd Qu.:1.170   3rd Qu.:1.300   3rd Qu.:1.230   3rd Qu.:1.220   3rd Qu.:0.5975   3rd Qu.:0.5725   3rd Qu.:1.300  
 Max.   :3.950   Max.   :14.290   Max.   :1.230   Max.   :1.530   Max.   :1.310   Max.   :1.300   Max.   :2.0700   Max.   :1.9200   Max.   :2.430  

 ASSIST_RATE_A     HOME_DL_ENGS   VISITING_DL_ENGS     month         year      prod_dl_above_3
 Min.   :0.4300   Min.   :509.0   Min.   : 7.00    May    :10   Min.   :2014   no : 1         
 1st Qu.:0.8275   1st Qu.:585.8   1st Qu.:26.00    Oct    :10   1st Qu.:2014   yes:99         
 Median :0.9750   Median :617.0   Median :35.50    Jul    : 9   Median :2014                  
 Mean   :1.0453   Mean   :608.0   Mean   :38.38    Aug    : 9   Mean   :2014                  
 3rd Qu.:1.1300   3rd Qu.:639.0   3rd Qu.:51.25    Jan    : 8   3rd Qu.:2015                  
 Max.   :2.2300   Max.   :660.0   Max.   :83.00    Feb    : 8   Max.   :2015                  
                                                   (Other):46

I can write multiple objects to an excel sheet by:

save.xlsx <- function (file, ...)
  {
      require(xlsx, quietly = TRUE)
      objects <- list(...)
      fargs <- as.list(match.call(expand.dots = TRUE))
      objnames <- as.character(fargs)[-c(1, 2)]
      nobjects <- length(objects)
      for (i in 1:nobjects) {
          if (i == 1)
              write.xlsx(objects[[i]], file, sheetName = objnames[i])
          else write.xlsx(objects[[i]], file, sheetName = objnames[i],
              append = TRUE)
      }
      print(paste("Workbook", file, "has", nobjects, "worksheets."))
}

But this can't copy Summary() output from R to Excel sheet. Is there a way that you could do this?

The reason is that I need to copy this information/output to a nice tabular format in excel.

Upvotes: 3

Views: 13535

Answers (1)

Heroka
Heroka

Reputation: 13149

Exploring 'summary', we see that when calling summary(a_data_frame) we get a table of a summary of each column. Instead of capturing output, we can also solve our issue a little more upstream. We can call summary on each column using lapply, and then rbind these outputs together.

Edit: your data has categorical and continuous variables. In my own workflow, I've always found it easiest to process these differently, as their representations can be different. Note that in the provided output, variables with lots of categories are truncates yielding these results suboptimal.

#create some data
set.seed(123)
dat <- data.frame(matrix(rnorm(1000),ncol=5))
dat$CatA <- factor(sample(LETTERS[1:10],nrow(dat),T))
dat$CatB <- factor(sample(LETTERS[1:2], nrow(dat),T))

#find out which data are categorical and which continuous. 
#Cutoff depends on data, use your judgement.

cont_cols <- colnames(dat)[sapply(dat, function(x) length(unique(x)))>10]
cat_cols <- setdiff(colnames(dat),cont_cols)


#create summary data for continuous variables
res_cont <- data.frame(do.call(rbind,lapply(dat[,cont_cols],summary)))

#add column with variable name (unwise to store in rownames)
res_cont$variable <- rownames(res_cont)

res_cont

# Min. X1st.Qu.    Median     Mean X3rd.Qu.  Max. variable
# X1 -2.309  -0.6258 -0.058740 -0.00857   0.5684 3.241       X1
# X2 -2.466  -0.5908  0.022830  0.04212   0.7148 2.571       X2
# X3 -2.810  -0.5575  0.075830  0.03178   0.6810 2.430       X3
# X4 -2.602  -0.6931  0.002188 -0.02189   0.6433 2.692       X4
# X5 -2.508  -0.6687  0.024790  0.03720   0.6678 2.685       X5

we do the same for categorical variables

res_cat <- do.call(rbind,lapply(cat_cols, function(x){
  res <- data.frame(table(dat[,x],useNA="always")) #added it to deal with missings, can be changes
  res$variable <- x
  colnames(res)[1] <- "Category"
  res
}
))

Writing the outputs to file is then trivial.

Upvotes: 6

Related Questions