4

I am trying to get the mean of the data read from an excel sheet.

library(readxl)
URL <- "C:/Users/user/Documents/R/test.xlsx"
pivot <- read_excel(URL,sheet=2)
pivot
mean(pivot$Experimental)

Data in excel below. This data is shown on the console window when executing the code above.

 Experimental   Comparison
   -0.255      -0.324
   -0.213      -0.185
   -0.190      -0.299
   -0.185      -0.144
   -0.045      -0.027
   -0.025      -0.039
   -0.015      -0.264
    0.003      -0.077
    0.015      -0.017
    0.020      -0.169
    0.023      -0.096
    0.040      -0.330
    0.040      -0.346
    0.050      -0.191
    0.055      -0.128
    0.058      -0.182  

I am getting the below warning message and result of the mean function is NA.

Warning message:
In mean.default(pivot$Experimental) :
  argument is not numeric or logical: returning NA
Ayubx
  • 597
  • 2
  • 9
  • 19
  • 1
    Show us `str(pivot)`, i.e. edit your question! – jogo Feb 18 '16 at 15:27
  • 2
    Very likely `Experimental` column is `factor` for some reason. Coerce them through `pivot$Experimental<-as.numeric(as.character(pivot$Experimental))` – nicola Feb 18 '16 at 15:28
  • @jogo `chr " -0.255" ` etc etc result of `str(pivot)` – Ayubx Feb 18 '16 at 15:41
  • @nicola There is a warning `NAs introduced by coercion ` – Ayubx Feb 18 '16 at 15:48
  • Sure there are warnings. If they weren't, your column wouldn't have been `character` in the first place. Do you see why? – nicola Feb 18 '16 at 16:13
  • @Ayubx So, you want to calculate something like `mean(c("one", "two", "three"))`. ... Try to convert your data using `as.numeric(...)` or `as.numeric(as.character(...))` – jogo Feb 18 '16 at 18:20
  • @nicola @jojo Yes the character output when executing `str(pivot) ` did surprise me. I am a bit new to this so i do not get why the data is considered as character. Another thing i have noticed is that the average function in excel is also throwing the `#DIV/0!` error. So may be a data issue ? I did try the as.numeric function on the data as `e <- as.numeric(pivot$Experimental) str(e)` but the same warning message is shown `Warning message: NAs introduced by coercion > str(e) num [1:18] NA NA NA NA NA NA NA NA NA NA` – Ayubx Feb 19 '16 at 07:08
  • 1
    @Ayubx Why you don't just post the output of `dput(pivot)` to let us see what it actually contains? Every question should be reproducible, you won't receive any help if you don't provide the relevant information. – nicola Feb 19 '16 at 07:48
  • @nicola Like i said earlier i am new to this. And any info requested by you has been provided. FYI the output for `dput(pivot)` is almost similar to the output for `str(pivot)`. On a hunch i manually reentered the data on the excel sheet instead of copy and paste. I had used the `trim()` function in excel but obviously it did not remove the leading and trailing spaces. After reentering the data and executing the required functions in R there is now no warning/error. I thank you, @jogo and the gentleman below who suggested an answer below for your helpful input – Ayubx Feb 19 '16 at 08:11

1 Answers1

5

@Ayubx The question @nicola is posing to you is how do you take the mean of characters? You can't, so you need to convert the characters to numeric. The below is an example. 

> d <- c("5","7")
> str(d)
chr [1:2] "5" "7"
> e <- as.numeric(d)
> str(e)
num [1:2] 5 7
> mean(d)
[1] NA
Warning message:
In mean.default(d) : argument is not numeric or logical: returning NA
> mean(e)
[1] 6
Scott
  • 642
  • 7
  • 16