Numeric data frame columns order incorrectly as string

Question

I have some hospital data in a dataframe, read in from a csv. I tried to order the dataframe by a user-defined column col and then by the hospital's name like so:

col <- 'Hospital.30.Day.Death..Mortality..Rates.from.Pneumonia'
hospitals.sorted <- hospitals[order(hospitals[,col], hospitals$Hospital.Name),]

But I think I'm missing something; it seems to sort col like strings:

> hospitals.sorted
... # so far so good # ...
2749                                                   10.0
2831                                                   10.0
2891                                                   10.0
2837                                                   10.1
2824                                                   10.1
2774                                                   10.1
... # not so good # ...
2856                                                   15.7
2834                                                   15.9
2797                                                   16.0
2835                                                    7.4
2850                                                    7.7
2789                                                    8.1
... # there are some non-numeric values at the very bottom # ...
2806                                                    9.9
2867                                                    9.9
2884                                                    9.9
2808                                          Not Available
2913                                          Not Available
2911                                          Not Available

Just to confirm the column is in fact numeric:

> sapply(hospitals, mode)
Hospital.30.Day.Death..Mortality..Rates.from.Pneumonia 
"numeric" 
Hospital.Name 
"numeric"

I don't know why Hospital.Name is numeric when it's clearly not.

Other things I tried to no avail:

• using as.numeric(hospitals[,col]) inside of order
• removing the "Not Available" values before sorting

I may be missing something fundamental. Halp!

Answer 1

In data frames, the individual components must be atomic vectors. You are including both numeric and character data in the variable you mention, and as such R will have read that as a character vector. However, due to the default setting of argument stringsAsFactors that character vector will have been converted to a factor. And hence it will look like the numbers are stored as numerics. Those are just labels however and you are being deceived.

Likewise, the mode() call is deceiving you too. Consider

> mode(factor(c(1:10, "a")))
[1] "numeric"

Yet this is clearly not "numeric" data. Next consider

> mode(factor(letters))
[1] "numeric"

This belies the fact that internally R's factors are stored as numeric variables and that is what mode() is telling you. mode() is the wrong tool for this job.

To test if a variable is numeric, use is.numeric() instead:

> is.numeric(factor(c(1:10, "a")))
[1] FALSE
> is.numeric(factor(letters))
[1] FALSE

As to the solution. The "Not Available" needs to be set to NA. You can do this when reading the data in by adding na.strings = "Not Available" to the read.table() (or whatever wrapper you used) call. That should be enough to sort out the character > factor conversion.

Top tip is to always look at the output of str() applied to your object to check that R has read the data in as you wanted it to. So you should do:

str(hospitals)

and note the types of variables according to R.

Regarding the other things you tried:

1. as.numeric(hospitals[,col]) will produce the numeric vector containing the level ID for each element of the factor. If the factor sorts in a particular order, so will it's levels representation. To convert a factor (it's labelled version) to a numeric you need an intermediate step: as.numeric(as.character(hospitals[, col])). This will not solve the actual problem you have here though, because you have character data in the variable and R won't be able to convert it to numeric. It will convert the "Not Available" to NA, which might have worked had you tried as.numeric(as.character(hospitals[, col])).
2. By removing the "Not Available", I presume by dropping those rows/elements?, will still leave the remaining observations in a factor. Which for reasons mentioned above won't work as it will alpha sort on the labels/levels.