[R] Read in alphanumeric column without decimals

[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:
> 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:
>> 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
>>
>> ______________________________________________
>> R-help at r-project.org mailing list
>> https://stat.ethz.ch/mailman/listinfo/r-help
>> PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
>> and provide commented, minimal, self-contained, reproducible code.
>
>
>
> --
> Jim Holtman
> Data Munger Guru
>
> What is the problem that you are trying to solve?
> Tell me what you want to do, not how you want to do it.