Skip to content

Read in alphanumeric column without decimals

3 messages · jim holtman, Steven Ranney

Steven Ranney
# 
All -

How can I read in a column of alphanumeric values without including
".0" on the numeric values?

Original column:

TeamLeaderID
258
342
316
U8
331
279
D1
116
235
296
...
[truncated]

leaders = read.xlsx2('FILE', sheetIndex = 1, header = T)

Column after it's been read in:

leaders$TeamLeaderID
258.0
342.0
316.0
U8
331.0
279.0
D1
116.0
235.0
296.0
...
[truncated]

If I try

as.numeric(leaders$TeamLeaderID), everything gets converted:

leaders$TeamLeaderID
11
27
19
54
23
13
28
2
8
15
...
[truncated]

as.character() and as.vector() leave the ".0" in place.

These data are being used to merge two files, one whose
data$TeamLeaderID is identical to the original column of values above.
 When I try to merge the dataframes by "TeamLeaderID," R can't match
the numbers without decimal to the ones that have decimal points.  The
alphanumeric TeamLeaderID values merge without a problem.  As a
result, I get a dataframe filled with the proper values for those
TeamLeaderID values that are alphanumeric, but "NA" values for those
whose values are strictly numeric.

How can I read in the values without R adding the ".0" to the end of
the numerals?  If there isn't a way, how can I automate the removal of
the ".0," as I have several 10s of TeamLeaderID values?

I'm using 64-bit R v. 2.15.1 on a Windows 7 machine.

Thank you -

Steven H. Ranney
jim holtman
# 
Your problem is the the data read in from the spreadsheet is probably
a 'factor' since it has a non-numeric in the column.  To change it to
number you have to do the following

as.numeric(as.character(yourdata$TeamLeaderID))

What you as seeing with just the call to as.numeric is the value of
the 'factor', not the value of the number.
On Thu, Nov 29, 2012 at 2:53 PM, Steven Ranney <steven.ranney at gmail.com> wrote:

  
    

      
      
    

  


  


      

    

    

      
      

        


  

        

      Steven Ranney
    

    

      
      #
    

  


  

      
Thanks for the replies.

I hadn't considered changing the original spreadsheet to a table as
there are several different analyses using the .xlsx spreadsheet that
contains the alphanumeric data, thus requiring an alteration of all of
our code.  However, the first response did prompt me to try changing
the read.xlsx2() statement to read.xlsx().  This worked.  I was able
to complete the merge successfully without any further issue.

I don't understand why read.xlsx2 was causing such an issue when
read.xlsx didn't have a problem adding the ".0."  I would have thought
that both read.xlsx2() and read.xlsx() would have brought the data in
as a factor.

SR
Steven H. Ranney

        
On Thu, Nov 29, 2012 at 2:33 PM, jim holtman <jholtman at gmail.com> wrote: