Appending rows to a dataframe - the factor problem

Question

I have a large dataframe (14552 rows by 15 columns) containing billing data from 2001 to 2007. I have used sqlFetch to get 2008 data. In order to append the 2008 data to the data of the preceding 7 years one would do as follows

alltime <-rbind(alltime,all2008)

Unfortunately that generates

Warning message: In [<-.factor(*tmp*, ri, value = c(NA, NA, NA, NA, NA, NA, NA, : invalid factor level, NAs generated

My guess is that there are some new patients whose names were not in the previous dataframe and therefore it would not know what level to give it. Another column is for the name of the referring doctor. A new referring doctor would cause the same problem.

The way R imports data and automatically works out what is numeric and what is not (and thereby makes it a factor) is wonderful - untill you have to manipulate it further and then it becomes a pain. How do I overcome my problem elegantly?

Answer 1

An "easy" way is to simply not have your strings set as factors when importing text data.

Note that the read.{table,csv,...} functions take a stringsAsFactors parameter, which is by default set to TRUE. You can set this to FALSE while you're importing and rbind-ing your data.

If you'd like to set the column to be a factor at the end, you can do that too.

For example:

alltime <- read.table("alltime.txt", stringsAsFactors=FALSE)
all2008 <- read.table("all2008.txt", stringsAsFactors=FALSE)
alltime <- rbind(alltime, all2008)
# If you want the doctor column to be a factor, make it so:
alltime$doctor <- as.factor(alltime$doctor)

Answer 2

As suggested in the previous answer, read the columns as character and do the conversion to factors after rbind. SQLFetch (I assume RODBC) has also the stringsAsFactors or the as.is argument to control the conversion of characters. Allowed values are as for read.table, e.g., as.is=TRUE or some column number.

Answer 3

It could be caused by mismatch of types in two data.frames.

First of all check types (classes). To diagnostic purposes do this:

new2old <- rbind( alltime, all2008 ) # this gives you a warning
old2new <- rbind( all2008, alltime ) # this should be without warning

cbind(
    alltime = sapply( alltime, class),
    all2008 = sapply( all2008, class),
    new2old = sapply( new2old, class),
    old2new = sapply( old2new, class)
)

I expect there be a row looks like:

            alltime  all2008   new2old  old2new
...         ...      ...       ...      ...
some_column "factor" "numeric" "factor" "character"
...         ...      ...       ...      ...

If so then explanation: rbind don't check types match. If you analyse rbind.data.frame code then you could see that the first argument initialized output types. If in first data.frame type is a factor, then output data.frame column is factor with levels unique(c(levels(x1),levels(x2))). But when in second data.frame column isn't factor then levels(x2) is NULL, so levels don't extend.

It means that your output data are wrong! There are NA's instead of true values

I suppose that:

1. you create you old data with another R/RODBC version so types were created with different methods (different settings - decimal separator maybe)
2. there are NULL's or some specific data in problematic column, eg. someone change column under database.

Solution:

find wrong column and find reason why its's wrong and fixed. Eliminate cause not symptoms.