6

I have a dataframe in R that I loaded from a CSV file. One of the variables is called "Amount" and is meant to contain positive and negative numbers.

When I looked at the dataframe, this variable's datatype is listed as a factor, and I need it in a numeric format (Not sure which kind though - integer - numeric, umm...?). So, I tried to convert it to one of those two formats but saw some interesting behavior.

Initial dataframe:

str(df)

Amount        : Factor w/ 11837 levels "","-1","-10",..: 2 2 1664 4 6290 6290 6290 6290 6290 6290 ...

As I mentioned above, I saw something weird when I tried to convert it to either numeric or integer. To show this, I put together this comparison:

df2 <- data.frame(df$Amount, as.numeric(df$Amount), as.integer(df$Amount))

str(df2)
'data.frame':   2620276 obs. of  3 variables:
 $ df.Amount            : Factor w/ 11837 levels "","-1","-10",..: 2 2 1664 4 6290 6290 6290 6290 6290 6290 ...
 $ as.numeric.df.Amount.: num  2 2 1664 4 6290 ...
 $ as.integer.df.Amount.: int  2 2 1664 4 6290 6290 6290 6290 6290 6290 ...

> head(df2, 20)
         df.Amount        as.numeric.df.Amount.       as.integer.df.Amount.
1               -1                           2                           2
2               -1                           2                           2
3             -201                        1664                        1664
4             -100                           4                           4
5                1                        6290                        6290
6                1                        6290                        6290
7                1                        6290                        6290
8                1                        6290                        6290
9                1                        6290                        6290
10               1                        6290                        6290
11               1                        6290                        6290
12               1                        6290                        6290
13               1                        6290                        6290
14               1                        6290                        6290
15               1                        6290                        6290
16               1                        6290                        6290
17               1                        6290                        6290
18               2                        7520                        7520
19               2                        7520                        7520
20               2                        7520                        7520

The as.numeric and as.integer functions are taking the Amount variable and doing something to it, but I don't know that that is. My goal is to get the Amount variable into some sort of a number data type so I can perform sum/mean/etc on it.

What I am I doing incorrectly that's causing the weird numbers, and what can I do to fix it?

mikebmassey
  • 8,354
  • 26
  • 70
  • 95

4 Answers4

11

The root of the problem is likely some funky value in your imported csv. If it came from excel, this is not uncommon. It can be a percent symbol, a "comment" character from excel or any of a long list of things. I would look at the csv in your editor of choice and see what you can see.

Aside from that, you have a few options.

read.csv takes an optional argument stringsAsFactors which you can set to FALSE

A factor is stored as integer levels which map to values. When you convert directly with as.numeric you wind up with those integer levels rather than the initial values: 

> x<-10:20
> as.numeric(factor(x))
 [1]  1  2  3  4  5  6  7  8  9 10 11
>

otherwise look at ?factor:

In particular, as.numeric applied to a factor is meaningless, and may happen by implicit coercion. To transform a factor f to approximately its original numeric values, as.numeric(levels(f))[f] is recommended and slightly more efficient than as.numeric(as.character(f)).

However, I suspect this will error because the input has something in it besides a number.

Brian Diggs
  • 57,757
  • 13
  • 166
  • 188
Justin
  • 42,475
  • 9
  • 93
  • 111
  • Once the column in question is a character, rather than factor, I have been able to find the source of this kind of problem fairly quickly using `grep` or `grepl` to check for some likely suspects. – joran Feb 01 '12 at 19:37
  • +1 Good answer. I expanded on it a bit in my answer on how to find the offending values... – Tommy Feb 01 '12 at 19:41
11

@Justin is correct. Here's a walk-through on how to find the offending values:

# A sample data set with a weird value ("4%") in it
d <- read.table(text="A B\n1 2\n3 4%\n", header=TRUE)
str(d)
#'data.frame':   2 obs. of  2 variables:
# $ A: int  1 3
# $ B: Factor w/ 2 levels "2","4%": 1 2

as.numeric(d$B) # WRONG, returns 1 2 (the internal factor codes)

# This correctly converts to numeric
x <- as.numeric(levels(d$B))[d$B] # 2 NA

# ...and this finds the offending value(s):
d$B[is.na(x)]  # 4% 

# and this finds the offending row numbers:
which(is.na(x)) # row 2

Note that if your data set has missing values encoded as something other than an empty cell or the string "NA", you have to specify that to read.table:

# Here "N/A" is used instead of "NA"...
read.table(text="A B\n1 2\n3 N/A\n", header=TRUE, na.strings="N/A")
Tommy
  • 39,997
  • 12
  • 90
  • 85
1

I'm new here but I have been using this forum for my queries. I was having similar problem but the below worked for me. I am porting data from txt file to data frame

data <- read.delim(paste(folderpath,"data.txt",sep=""),header=TRUE,sep="\\",as.is=6)

Note that I used as.is on the column 6 which had numeric data as well as some garbage characters in some rows. Using as.is ports the data as characters in column 6. then the following changed the characters in column 6 to numeric values. all garbage values were converted to NA which could be removed later.

data[,6] <- as.numeric(data[,6])

Hope this helps

Taran
  • 126
  • 4
1

Just convert the factor values to character first, and then convert to numeric. This should solve your problem.

as.numeric(as.character(df$Amount))
Praveen Kumar
  • 321
  • 3
  • 5