How to convert a data frame column to numeric type

Question

How do you convert a data frame column to a numeric type

User · Answer

if x is the column name of dataframe dat  and x is of type factor  use   as numeric as character dat x

User · Answer

Though others have covered the topic pretty well  I d like to add this additional quick thought hint   You could use regexp to check in advance whether characters potentially consist only of numerics    for i in seq along names df         potential numcol i   lt - all  grepl   a-zA-Z   d  i        and now just convert only the numeric ones d  lt - sapply d  potential numcol  as numeric    For more sophisticated regular expressions and a neat why to learn experience their power see this really nice website  http   regexr com

User · Answer

While your question is strictly on numeric  there are many conversions that are difficult to understand when beginning R  I ll aim to address methods to help  This question is similar to This Question     Type conversion can be a pain in R because  1  factors can t be converted directly to numeric  they need to be converted to character class first   2  dates are a special case that you typically need to deal with separately  and  3  looping across data frame columns can be tricky  Fortunately  the  tidyverse  has solved most of the issues    This solution uses mutate each   to apply a function to all columns in a data frame  In this case  we want to apply the type convert   function  which converts strings to numeric where it can  Because R loves factors  not sure why  character columns that should stay character get changed to factor  To fix this  the mutate if   function is used to detect columns that are factors and change to character  Last  I wanted to show how lubridate can be used to change a timestamp in character class to date-time because this is also often a sticking block for beginners        library tidyverse   library lubridate     Recreate data that needs converted to numeric  date-time  etc data df   gt    A tibble  5       9   gt              TIMESTAMP SYMBOL    EX  PRICE  SIZE  COND   BID BIDSIZ   OFR   gt                   lt chr gt    lt chr gt   lt chr gt    lt chr gt   lt chr gt   lt chr gt   lt chr gt    lt chr gt   lt chr gt    gt  1 2012-05-04 09 30 00    BAC     T 7 8900 38538     F  7 89    523  7 90   gt  2 2012-05-04 09 30 01    BAC     Z 7 8850   288        7 88  61033  7 90   gt  3 2012-05-04 09 30 03    BAC     X 7 8900  1000        7 88   1974  7 89   gt  4 2012-05-04 09 30 07    BAC     T 7 8900 19052     F  7 88   1058  7 89   gt  5 2012-05-04 09 30 08    BAC     Y 7 8900 85053     F  7 88 108101  7 90    Converting columns to numeric using  tidyverse  data df   gt       mutate all type convert    gt       mutate if is factor  as character    gt       mutate TIMESTAMP   as datetime TIMESTAMP  tz   Sys timezone       gt    A tibble  5       9   gt              TIMESTAMP SYMBOL    EX PRICE  SIZE  COND   BID BIDSIZ   OFR   gt                  lt dttm gt    lt chr gt   lt chr gt   lt dbl gt   lt int gt   lt chr gt   lt dbl gt    lt int gt   lt dbl gt    gt  1 2012-05-04 09 30 00    BAC     T 7 890 38538     F  7 89    523  7 90   gt  2 2012-05-04 09 30 01    BAC     Z 7 885   288        7 88  61033  7 90   gt  3 2012-05-04 09 30 03    BAC     X 7 890  1000        7 88   1974  7 89   gt  4 2012-05-04 09 30 07    BAC     T 7 890 19052     F  7 88   1058  7 89   gt  5 2012-05-04 09 30 08    BAC     Y 7 890 85053     F  7 88 108101  7 90

User · Answer

If the dataframe has multiple types of columns  some characters  some numeric try the following to convert just the columns that contain numeric values to numeric   for  i in 1 length data 1        if length as numeric data  i   is na data  i      is na as numeric data  i   is na data  i         0      else       data  i  lt -as numeric data  i

User · Answer

If you don t care about preserving the factors  and want to apply it to any column that can get converted to numeric  I used the script below  if df is your original dataframe  you can use the script below    df    lt - lapply df  as character  df  lt - data frame lapply df  function x  ifelse  is na as numeric x    as numeric x    x      I referenced Shane s and Joran s solution btw

User · Answer

Since  still  nobody got check-mark  I assume that you have some practical issue in mind  mostly because you haven t specified what type of vector you want to convert to numeric  I suggest that you should apply transform function in order to complete your task   Now I m about to demonstrate certain  conversion anomaly      create dummy data frame d  lt - data frame char   letters 1 5                    fake char   as character 1 5                    fac   factor 1 5                    char fac   factor letters 1 5                     num   1 5  stringsAsFactors   FALSE    Let us have a glance at data frame   gt  d   char fake char fac char fac num 1    a         1   1        a   1 2    b         2   2        b   2 3    c         3   3        c   3 4    d         4   4        d   4 5    e         5   5        e   5   and let us run    gt  sapply d  mode         char   fake char         fac    char fac         num   character   character     numeric     numeric     numeric    gt  sapply d  class         char   fake char         fac    char fac         num   character   character      factor      factor     integer     Now you probably ask yourself  Where s an anomaly   Well  I ve bumped into quite peculiar things in R  and this is not the most confounding thing  but it can confuse you  especially if you read this before rolling into bed   Here goes  first two columns are character  I ve deliberately called 2nd one fake char  Spot the similarity of this character variable with one that Dirk created in his reply  It s actually a numerical vector converted to character  3rd and 4th column are factor  and the last one is  purely  numeric   If you utilize transform function  you can convert the fake char into numeric  but not the char variable itself    gt  transform d  char   as numeric char     char fake char fac char fac num 1   NA         1   1        a   1 2   NA         2   2        b   2 3   NA         3   3        c   3 4   NA         4   4        d   4 5   NA         5   5        e   5 Warning message  In eval expr  envir  enclos    NAs introduced by coercion   but if you do same thing on fake char and char fac  you ll be lucky  and get away with no NA s    gt  transform d  fake char   as numeric fake char                   char fac   as numeric char fac      char fake char fac char fac num 1    a         1   1        1   1 2    b         2   2        2   2 3    c         3   3        3   3 4    d         4   4        4   4 5    e         5   5        5   5   If you save transformed data frame and check for mode and class  you ll get    gt  D  lt - transform d  fake char   as numeric fake char                        char fac   as numeric char fac     gt  sapply D  mode         char   fake char         fac    char fac         num   character     numeric     numeric     numeric     numeric    gt  sapply D  class         char   fake char         fac    char fac         num   character     numeric      factor     numeric     integer    So  the conclusion is  Yes  you can convert character vector into a numeric one  but only if it s elements are  convertible  to numeric  If there s just one character element in vector  you ll get error when trying to convert that vector to numerical one   And just to prove my point    gt  err  lt - c 1   b   3  4   e    gt  mode err   1   character   gt  class err   1   character   gt  char  lt - as numeric err  Warning message  NAs introduced by coercion   gt  char  1   1 NA  3  4 NA   And now  just for fun  or practice   try to guess the output of these commands    gt  fac  lt - as factor err   gt  fac      gt  num  lt - as numeric fac   gt  num       Kind regards to Patrick Burns

User · Answer

If you run into problems with   as numeric as character dat x     Take a look to your decimal marks  If they are     instead of      e g   5 3   the above won t work   A potential solution is   as numeric gsub           dat x     I believe this is quite common in some non English speaking countries

User · Answer

With the following code you can convert all data frame columns to numeric  X is the data frame that we want to convert it s columns    as data frame lapply X  as numeric     and for converting whole matrix into numeric you have two ways  Either   mode X   lt -  numeric    or   X  lt - apply X  2  as numeric    Alternatively you can use data matrix function to convert everything into numeric  although be aware that the factors might not get converted correctly  so it is safer to convert everything to character first   X  lt - sapply X  as character  X  lt - data matrix X    I usually use this last one if I want to convert to matrix and numeric simultaneously

User · Answer

I would have added a comment  cant low rating   Just to add on user276042 and pangratz  dat x   as numeric as character dat x     This will override the values of existing column x

User · Answer

with hablar  convert  To easily convert multiple columns to different data types you can use hablar  convert  Simple syntax  df   gt   convert num a   converts the column a from df to numeric   Detailed example   Lets convert all columns of mtcars to character   df  lt - mtcars   gt   mutate all as character    gt   as tibble     gt  df   A tibble  32 x 11    mpg   cyl   disp  hp    drat  wt    qsec  vs    am    gear  carb      lt chr gt   lt chr gt   lt chr gt   lt chr gt   lt chr gt   lt chr gt   lt chr gt   lt chr gt   lt chr gt   lt chr gt   lt chr gt   1 21    6     160   110   3 9   2 62  16 46 0     1     4     4      2 21    6     160   110   3 9   2 875 17 02 0     1     4     4      3 22 8  4     108   93    3 85  2 32  18 61 1     1     4     1       With hablar  convert   library hablar     Convert columns to integer  numeric and factor df   gt      convert int cyl  vs             num disp wt             fct gear     results in     A tibble  32 x 11    mpg     cyl  disp    hp  drat    wt qsec     vs am    gear  carb      lt chr gt   lt int gt   lt dbl gt   lt dbl gt   lt dbl gt   lt dbl gt   lt chr gt   lt int gt   lt chr gt   lt fct gt   lt chr gt   1 21        6  160    110  3 9   2 62 16 46     0 1     4     4      2 21        6  160    110  3 9   2 88 17 02     0 1     4     4      3 22 8      4  108     93  3 85  2 32 18 61     1 1     4     1      4 21 4      6  258    110  3 08  3 22 19 44     1 0     3     1

User · Answer

To convert a data frame column to numeric you just have to do -  factor to numeric -   data frame column  lt - as numeric as character data frame column

User · Answer

Universal way using type convert   and rapply     convert types  lt - function x        stopifnot is list x       x    lt - rapply x  utils  type convert  classes    character                     how    replace   as is   TRUE      return x    d  lt - data frame char   letters 1 5                    fake char   as character 1 5                    fac   factor 1 5                    char fac   factor letters 1 5                     num   1 5  stringsAsFactors   FALSE  sapply d  class    gt         char   fake char         fac    char fac         num    gt   character   character      factor      factor     integer  sapply convert types d   class    gt         char   fake char         fac    char fac         num    gt   character     integer      factor      factor     integer

User · Answer

In my PC  R v 3 2 3   apply or sapply give error  lapply works well   dt  2 4   lt - lapply dt  2 4   function  x  as factor as numeric x

User · Answer

Tim is correct  and Shane has an omission  Here are additional examples   R gt  df  lt - data frame a   as character 10 15   R gt  df  lt - data frame df  num   as numeric df a                            numchr   as numeric as character df a    R gt  df    a num numchr 1 10   1     10 2 11   2     11 3 12   3     12 4 13   4     13 5 14   5     14 6 15   6     15 R gt  summary df    a          num           numchr      10 1   Min     1 00   Min     10 0    11 1   1st Qu  2 25   1st Qu  11 2    12 1   Median  3 50   Median  12 5    13 1   Mean    3 50   Mean    12 5    14 1   3rd Qu  4 75   3rd Qu  13 8    15 1   Max     6 00   Max     15 0   R gt     Our data frame now has a summary of the factor column  counts  and numeric summaries of the as numeric   --- which is wrong as it got the numeric factor levels --- and the  correct  summary of the as numeric as character

User · Answer

Something that has helped me  if you have ranges of variables to convert  or just more then one   you can use sapply   A bit nonsensical but just for example   data cars  cars   1 2   lt - sapply cars   1 2   as factor    Say columns 3  6-15 and 37 of you dataframe need to be converted to numeric one could   dat   c 3 6 15 37    lt - sapply dat   c 3 6 15 37    as numeric

User · Answer

To convert character to numeric you have to convert it into factor by applying   BankFinal1  lt - transform BankLoan    LoanApproval as factor LoanApproval   BankFinal1  lt - transform BankFinal1  LoanApp as factor LoanApproval     You have to make two columns with the same data  because one column cannot convert into numeric  If you do one conversion it gives the below error   transform BankData  LoanApp as numeric LoanApproval      Warning message    In eval substitute list          data   parent frame        NAs introduced by coercion    so  after doing two column of the same data apply  BankFinal1  lt - transform BankFinal1  LoanApp        as numeric LoanApp                                        LoanApproval   as numeric LoanApproval     it will transform the character to numeric successfully

User · Answer

Considering there might exist char columns  this is based on  Abdou in Get column types of excel sheet automatically answer   makenumcols lt -function df     df lt -as data frame df    df    lt - lapply df  as character    cond  lt - apply df  2  function x        x  lt - x  is na x       all suppressWarnings  is na as numeric x            numeric cols  lt - names df  cond    df  numeric cols   lt - sapply df  numeric cols   as numeric    return df    df lt -makenumcols df

User · Answer

df ist your dataframe  x is a column of df you want to convert  as numeric factor df x

[r] How to convert a data frame column to numeric type?

Examples related to r

Examples related to dataframe

Examples related to type-conversion