Importing csv file into R - numeric values read as characters

Question

I am aware that there are similar questions on this site  however  none of them seem to answer my question sufficiently    This is what I have done so far    I have a csv file which I open in excel  I manipulate the columns algebraically to obtain a new column  A   I import the file into R using read csv   and the entries in column A are stored as factors - I want them to be stored as numeric  I find this question on the topic   Imported a csv-dataset to R but the values becomes factors  Following the advice  I include stringsAsFactors   FALSE as an argument in read csv    however  as Hong Ooi suggested in the page linked above  this doesn t cause the entries in column A to be stored as numeric values    A possible solution is to use the advice given in the following page   How to convert a factor to an integer numeric without a loss of information   however  I would like a cleaner solution i e  a way to import the file so that the entries of column entries are stored as numeric values   Cheers for any help

User · Answer

In read table  and its relatives  it is the na strings argument which specifies which strings are to be interpreted as missing values NA  The default value is na strings    NA   If missing values in an otherwise numeric variable column are coded as something else than  NA   e g      or  N A   these rows will be interpreted as character  and then the whole column is converted to character   Thus  if your missing values are some else than  NA   you need to specify them in na strings

User · Answer

Whatever algebra you are doing in Excel to create the new column could probably be done more effectively in R     Please try the following    Read the raw file  before any excel manipulation  into R using read csv     stringsAsFactors FALSE      If that does not work  please take a look at  read table  which read csv wraps   however there may be some other underlying issue    For example       delim          or is it   t       dec            or is it          myDataFrame  lt - read csv  path to file csv   header TRUE  sep delim  dec dec  stringsAsFactors FALSE    Then  let s say your numeric columns is column 4     myDataFrame   4    lt - as numeric myDataFrame   4      you can also refer to the column by  itsName     Lastly  if you need any help with accomplishing in R the same tasks that you ve done in Excel  there are plenty of folks here who would be happy to help you out

User · Answer

version for data table based on code from dmanuge    convNumValues lt -function ds     ds lt -data table ds    dsnum lt -data table data matrix ds     num cols  lt - sapply dsnum function x  mean as numeric is na x    lt 0 5     nds  lt - data table   dsnum    SD   SDcols attributes num cols  names which num cols                             ds    SD   SDcols attributes num cols  names which  num cols      return nds

User · Answer

If you re dealing with large datasets  i e  datasets with a high number of columns   the solution noted above can be manually cumbersome  and requires you to know which columns are numeric a priori    Try this instead   char data  lt - read csv input filename  stringsAsFactors   F  num data  lt - data frame data matrix char data   numeric columns  lt - sapply num data function x  mean as numeric is na x    lt 0 5   final data  lt - data frame num data  numeric columns   char data   numeric columns     The code does the following    Imports your data as character columns  Creates an instance of your data as numeric columns  Identifies which columns from your data are numeric  assuming columns with less than 50  NAs upon converting your data to numeric are indeed numeric   Merging the numeric and character columns into a final dataset    This essentially automates the import of your  csv file by preserving the data types of the original columns  as character and numeric

User · Answer

I had a similar problem   Based on Joshua s premise that excel was the problem I looked at it and found that the numbers were formatted with commas between every third digit   Reformatting without commas fixed the problem

User · Answer

Including this in the read csv command worked for me  strip white   TRUE    I found this solution here

[r] Importing csv file into R - numeric values read as characters

Examples related to r