2

So I have this excel spreadsheet called 'Birth.xls'. and I have 10 sheets inside this single spreadsheet in this format:

Sheet1
Number  Value 
3   0.125
6   0.2
9   0.275
12  0.35
15  0.425
17  0.5
19  0.575
21  0.65
23  0.725
25  0.8
27  0.875
29  0.95
31  1.025
Sheet2
Number  Value
3   0.614
6   0.654
9   0.694
12  0.734
15  0.774
17  0.814
19  0.854
21  0.894
23  0.934
25  0.974
27  1.014
29  1.054
31  1.094

How would I create another 'sheet' in this spreadsheet that has the row averages of the sheets I have given above using R?...I understand that I could do this in excel but since I have 10 sheets or more sometimes, it can be quite a pain...especially when I have to do more complicated things than averages....So all in all, I would want an output similar to the one below

Sheet3
Number  Average Value
3   0.3695
6   0.427
9   0.4845
12  0.542
15  0.5995
17  0.657
19  0.7145
21  0.772
23  0.8295
25  0.887
27  0.9445
29  1.002
31  1.0595
Gladdys Gotherin
  • 167
  • 1
  • 3
  • 7
  • What is reason to keep Excel in the loop? If there is not pressing need, the added complexity of going back and forth only takes time and introduces the potential for errors. – Paul Hiemstra Mar 31 '13 at 13:46

1 Answers1

3

Here is a way I would do it. First, load all data, make them as one data frame, calculate means and then export to new sheet.

library(XLConnect)

#load xls file
wb<-loadWorkbook("Birth.xls")

#get sheet names of file
lp<-getSheets(wb)

#load each sheet in seperate list element
dat<-lapply(seq_along(lp),function(i) readWorksheet(wb,sheet=lp[i]))

#convert all data to one data frame by merging individual data frame
dat2<-Reduce(function(...) merge(..., by="Number"), dat)

#calculate row means  of merged data frames (-1 as first column contains Number)
dat2$res<-rowMeans(dat2[,-1])

#create new sheet to export data
createSheet(wb, name = "together")

#write calculated data to new sheet
writeWorksheet(wb,dat2[,c("Number","res")],sheet="together")

#save workbook
saveWorkbook(wb)
Didzis Elferts
  • 95,661
  • 14
  • 264
  • 201
  • thanks Didzis, but I'm not so sure what to do the function(...) and merge(...)?? – Gladdys Gotherin Mar 31 '13 at 09:31
  • @GladdysGotherin in that line I put all data frames of list dat in one data frame dat2. So it will be easier to calculate mean value for each row. You can find more explanation in this [SO question](http://stackoverflow.com/questions/8091303/merge-multiple-data-frames-in-a-list-simultaneously) – Didzis Elferts Mar 31 '13 at 09:35