How select the excel sheet using for loop in R

Question

I want the app that I am developing automatically go to the excel sheet and calculate the transition probability matrix.

 action<-actions
    state<-states
    p<-array(0,c(length(state),length(state),length(action)))
    colnames(p)=state
    rownames(p)=state
    # transition probability matrix based on the action that we have choosen
    empiricala1<-read.xlsx("www/dynamic model.xlsx",1)
    empiricala2<-read.xlsx("www/dynamic model.xlsx",2)


    #show(empirical) calculate transition probability from historical data
    em1<-as.data.frame(empiricala1)
    em2<-as.data.frame(empiricala2)
    tab1 <- table(em1)
    tab2 <- table(em2)

    tab1<-addmargins(prop.table(table(em1$current,em1$nextstate),1),2)
    tab2<-addmargins(prop.table(table(em2$current,em2$nextstate),1),2)
    transitionprob1<-p[,,1]<-prop.table(table(em1$current,em1$nextstate),1)
    transitionprob2<-p[,,2]<-prop.table(table(em2$current,em2$nextstate),2)
    print(transitionprob1)
    print(transitionprob2)


    for(i in 1:length(action)){

      p[,,i]<-prop.table(table(em[i]$current,em[i]$nextstate),i)

    }

The error that I got is as following:

Error in em[i] : object of type 'closure' is not subsettable

How can I fix this problem.

Answer 1

Ok, so to expand on the comments...

You have two data frames em1 and em2. You seem to want to apply the same operations to both data frames (e.g. table(em1) and table(em2). This becomes really tedious to write, especially once you get way more variables.

What you tried to do, was:

for (i in 1:2) em[i]

The problem with this is that you do NOT get em1 and em2. Instead, you get em[1] and em[2] and are thus referring to the em object (which does not exist to begin with).

There are a couple of ways to solve this.

1. Move the data frames to a list

lis <- list(em1, em2)

This way, you can iterate over the list with, for example, the *apply family or a for-loop:

sapply(lis, nrow)
for (i in 1:length(lis)) nrow(lis[[i]])

2. Use get

Another option is to use get, which allows you to provide a string and it will subsequently return the variable described in that string.

vec <- c("em1","em2")
sapply(vec, function(x) nrow(get(x)))

Note that using get is generally discouraged. I'd also opt for the first option instead.