In my latest project, I found a different usage for the apply function.  I had a dataframe detailing the donations made each year, by individual people, for the last 7 years.  I also had a column specifying the year when they converted from being a ‘regular’ donor to donating through a specific program.  The idea was to create a column specifying the last yearly amount donated before the year they had converted.  In other words, for each row, information had to be extracted on the year they converted, when they had donated before that year, and how much was that amount.

Here’s the R code I used with some sample data to help recreate what I did:

test = matrix(c(31, NA, 33, 37, NA, 2011, 
                NA, 33, NA, 31, 37, 2012,
                37, 33, 31, NA, NA, 2009), nrow=3, ncol=6, byrow=TRUE, dimnames=list(c(1,2,3),c(seq(2008,2012),'year')))

value.b4.year = apply(test[,1:6], 1, function (x) 
  ifelse(any(!is.na(x[1:5])), 
  x[max(which(!is.na(x[1:which(seq(2008,2012) == x[6]) -1])))], 
         NA))

First, I set up the sample data.  It’s obviously pretty simple, and the dataframe I used in reality had some rows where there were all NA values throughout the first 5 columns.

It’s in the apply function where the real magic begins.  First, we load up all relevant columns into the apply functions for each row (test[,1:6]).  You can specify/insert whichever columns you need from your dataframe, so long as you use c() in the indexing brackets when you’re referencing your dataframe.  Once you load them up, no matter what the column indices/numbers, they become columns 1 through the total number of columns you loaded up as far as the rest of the apply function is concerned. (Say you load up 15 different columns indices, all spread out in terms of their relative position numbers.  Regardless of the position each index represents, those columns become 1:15 when referring to them in the subsequent function call).

After the function (x) part of the function, I put a condition stating the the following value assignment should only happen if there is any data in the first 5 elements of the row, otherwise an NA gets assigned.  Notice that you don’t have to operate on, or select, every single column that you’ve loaded into the apply statement; very important!

The next line is dizzifyingly complex, so let me break it down for you:

x[1:which(seq(2008,2012) == x[6]) -1]

In this part of the line, I’m calling up all elements of the row, from the first element, until the element before the one corresponding to the year listed in the sixth element (i.e. 1:end year).  We start with  seq(2008,2012) == x[6], which marks the specific year that corresponds with the value of the sixth element.   Feeding that statement into the which function gives you the position in the row represented by x of the year listed in the sixth element.  Subtracting by 1 sets the end index as that column just before the one corresponding with the year.

To show you what the above gets you on line 1 of the sample data:

test[1,1:which(seq(2008,2012) == test[1,6]) -1]

2008 2009 2010 
  31   NA   33

Next, by nesting the above statement in the which and !is.na function calls, we eliminate those elements in the row that have NA values in them and find the positions where there is some value.

which(!is.na(test[1,1:which(seq(2008,2012) == test[1,6]) -1]))

2008 2010 
   1    3

Now that we have the positions of all values in the row that occurred before the year listed, we just need to select the highest value, by nesting the above function call inside the a call to the max function:

max(which(!is.na(test[1,1:which(seq(2008,2012) == test[1,6]) -1])))

3

Right, so the the last value before the year in column 6 for row 1 is in position 3!  That finally leads us to the syntax I used in the apply statement (somewhat different here, because we’re looking at one row of the sample data):

test[1,max(which(!is.na(test[1,1:which(seq(2008,2012) == test[1,6]) -1])))]

33

So, for row 1, the last donation amount before conversion was 33. Let’s look at the results of the apply statement from above:

value.b4.year

 1  2  3 
33 31 37

Yup, for each of these rows, the values saved in value.b4.year are the last values donated before the year specified in the sixth column.

So, the lesson here is that when you are using apply to perform an operation on each row of a dataframe, the value of any one column from that row is easily accessible by referring to the position of that value relative the entire group of columns that you have passed to the apply function.  Hopefully this will help you in the future as I know it will help me

df = data.frame(a1 = c(4,1,1,3,2,4,2), a2 = c(“h”,”j”,”j”,”e”,”c”,”h”,”c”))

I expect that if I call the reorder function on the a2 vector, using the a1 vector as the vector to order the second one by, then any summary stats that I run on the a2 vector will be ordered according to the numbers in a1.  However, look what happens:

table(reorder(df$a2, df$a1))
c e h j 
2 1 2 2

I found out that in order to get it in the order specified by the numbers in the first vector, the following code seems to work:

df$a2 = factor(df$a2, levels=unique(df$a2)[order(unique(df$a1))], ordered=TRUE)

Now look at the result:

table(df$a2)

j c e h 
2 2 1 2

One thing I notice here is that R seems to be keeping the factor levels alphabetically organized. When I specify the levels by using the “unique” function, it allows itself to break the alphabetic organization.

Why won’t the reorder function work in this case?

Assuming the data frame is called ‘trans.file’, the numerical ID is called, ‘Num.ID’, and the categorical descriptor column of interest is called ‘appeal’, here’s the simple code:

first.appeal.by.id = ddply(trans.file, “Num.ID”, function (x) as.character(x$appeal[1]))

I do like simple, but I also like fast.  Is there a less computationally expensive way of doing this?  Please tell me if so

mtapply = function (dvar, ivar, funlist) {
  lenlist = length(funlist)
  outtable = matrix(NA, dim(table(ivar)), lenlist, dimnames=list(names(table(ivar)), funlist))
  c = 1
  for (f in funlist) {
    outtable[,c] = as.matrix(tapply(dvar, ivar, eval(parse(text=f))))
    c = c + 1
  }
  return (outtable)}

As you can see, I’ve made it so that the result is a bunch of tapply vectors inserted into a matrix.  “dvar”, unsurprisingly, is your dependent variable.  “ivar”, your independent variable.  “funlist” is a vector of function names typed in as strings (e.g. c(“median”,”mean”,”mode”).  I’ve wasted so much of my time trying to get ddply or ffdfdply to work on an ffdf, that I’m happy that I now have anything that does the job for me.

Now that I think about it, this will fall short if you ask it to output more than one quantile for each of your split levels.  If you can improve this function, please be my guest!

In this case, each row represented one person, but there were 1,551 columns!!  As it turned out, after the first column, which was the ID of the person donating the money, there were supposed to be just 31 extra columns to describe the gift in each row.  However, the person who put the data together decided that we should get 31*50 columns so that each row represented a person, and not a gift, and every subsequent gift from that person was represented by an extra 31 columns to the right of the previous 31.  Ridiculous!!

Anyway, I knew that I could reshape this using R, by stacking all 50 copies of the same variable together, and making sure that each new resultant 31 vectors should just take the names of the first 31 vectors.  Following is a gist that shows what eventually worked for me:

In conclusion, if you need your dataset to get in shape, you need only remember one letter: R!

So, I set out to learn how I could use the colClasses argument in the read.csv.ffdf command to manually set the data types for each column.  I adapted the following solution from a stackoverflow thread about specifying colClasses in the regular read.csv function.

First, load up a sample of the big dataset using the read.csv command (The following is obviously non-random. If you can figure out how to read the sample in randomly, I think it would work much better):

headset = read.csv(fname, header = TRUE, nrows = 5000)

The next command generates a list of all the variable names in your dataset, and the classes R was able to derive based on the number of rows you imported:

headclasses = sapply(headset, class)

Now comes the fairly manual part. Look at the list of variables and classes (data types) that you generated, and look for obvious mismatches. Examples could be a numeric variable that got coded as a factor or logical, or a factor that got coded as a numeric. When you find such a mismatch, the following syntax suffices for changing a class one at a time:

headclasses["variable.name"] = "numeric"

Obviously, the “variable.name” should be replaced by the actual variable name you’re reclassifying, and the “numeric” string can also be “factor”, “ordered”, “Date”, “POSIXct” (the last two being date/time data types). Finally, let’s say you want to change every variable that got coded as “logical” into “numeric”. Here’s some syntax you can use:

headclasses[grep("logical", headclasses)] = "numeric"

Once you are certain that all the classes represented in the list you just generated and modified are accurate to the dataset, you can load up the data with confidence, using the headclasses list:

bigdataset = read.csv.ffdf(file="C:/big/data/loc.csv", first.rows=5000, colClasses=headclasses)

This was certainly not easy, but I must say that I seem to be willing to jump through many hoops for R!!