Converting character to numeric using the package "XLConnect"

classic Classic list List threaded Threaded
8 messages Options
Reply | Threaded
Open this post in threaded view
|

Converting character to numeric using the package "XLConnect"

Nelly Reduan
Hello,

From an Excel file imported into R where each cell contains characters, I would like to convert some characters to numeric. However, my code doesn’t work. When I write data to worksheets in an Excel file, some numbers in the cells are stored as text (instead of numeric). Here is my code (the Excel file is attached):

rm(list=ls(all=TRUE))
library(XLConnect)
tab <- loadWorkbook("C:/Users/Downloads/File.xlsx", create = TRUE)
set <- readWorksheet(tab, sheet = "settings")
setNum <- readWorksheet(tab, sheet = "settings", colTypes="numeric")
index <- which((!is.na(setNum)), arr.ind=TRUE)
if(length(index)!=0){
  set[index] <- unlist(lapply(set[index], function(x) as.numeric(as.character(x))))
}
## to check
set[10,1] ## Problem: must be numeric !!!!

writeWorksheetToFile("C:/Users/Downloads/Test.xlsx", data=set, sheet="settings", styleAction = XLC$"STYLE_ACTION.NONE")

How can I convert the numbers which are stored as characters to numeric?

Thanks so much for your time.

Have a nice day

Nell
______________________________________________
[hidden email] mailing list -- To UNSUBSCRIBE and more, see
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.
Reply | Threaded
Open this post in threaded view
|

Re: Converting character to numeric using the package "XLConnect"

David Winsemius

> On Aug 30, 2017, at 12:24 PM, Nelly Reduan <[hidden email]> wrote:
>
> Hello,
>
> From an Excel file imported into R where each cell contains characters, I would like to convert some characters to numeric. However, my code doesn’t work. When I write data to worksheets in an Excel file, some numbers in the cells are stored as text (instead of numeric). Here is my code (the Excel file is attached):

Nope. No file was attached in what was circulated. The list-server scrubs potentially malicious files (including zip, csv, or xls files). You could send a "csv" file with extension of ".txt".
>
> rm(list=ls(all=TRUE))
> library(XLConnect)
> tab <- loadWorkbook("C:/Users/Downloads/File.xlsx", create = TRUE)
> set <- readWorksheet(tab, sheet = "settings")
> setNum <- readWorksheet(tab, sheet = "settings", colTypes="numeric")

Presumably you could also send the results of

dput(setNum)

.. either as a .txt attachment or embedded in an email.

> index <- which((!is.na(setNum)), arr.ind=TRUE)
> if(length(index)!=0){
>  set[index] <- unlist(lapply(set[index], function(x) as.numeric(as.character(x))))
> }
> ## to check
> set[10,1] ## Problem: must be numeric !!!!
>
> writeWorksheetToFile("C:/Users/Downloads/Test.xlsx", data=set, sheet="settings", styleAction = XLC$"STYLE_ACTION.NONE")
>
> How can I convert the numbers which are stored as characters to numeric?

Have you tried using the formatting features of Excel to change the default "auto" sttings of black columns? You could select an entire column and set its format to numeric. I know this sometimes fixes the annoying habit of Excel of changing texts items withn interior dashes to dates.

--

David Winsemius
Alameda, CA, USA

'Any technology distinguishable from magic is insufficiently advanced.'   -Gehm's Corollary to Clarke's Third Law

______________________________________________
[hidden email] mailing list -- To UNSUBSCRIBE and more, see
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.
Reply | Threaded
Open this post in threaded view
|

Re: Converting character to numeric using the package "XLConnect"

Nelly Reduan
The Excel file can be found from this link: https://1drv.ms/x/s!Apkg2VlgfYyDgQ_mcJ8F4CaXV_Nh

[https://r1.res.office365.com/owa/prem/images/dc-xlsx_40.png]<https://1drv.ms/x/s!Apkg2VlgfYyDgQ_mcJ8F4CaXV_Nh>
File.xlsx<https://1drv.ms/x/s!Apkg2VlgfYyDgQ_mcJ8F4CaXV_Nh>
Partag� via OneDrive





Thanks very much for your help.

Nell

________________________________
De : David Winsemius <[hidden email]>
Envoy� : mercredi 30 ao�t 2017 12:59:57
� : Nelly Reduan
Cc : [hidden email]
Objet : Re: [R] Converting character to numeric using the package "XLConnect"


> On Aug 30, 2017, at 12:24 PM, Nelly Reduan <[hidden email]> wrote:
>
> Hello,
>
> From an Excel file imported into R where each cell contains characters, I would like to convert some characters to numeric. However, my code doesn�t work. When I write data to worksheets in an Excel file, some numbers in the cells are stored as text (instead of numeric). Here is my code (the Excel file is attached):

Nope. No file was attached in what was circulated. The list-server scrubs potentially malicious files (including zip, csv, or xls files). You could send a "csv" file with extension of ".txt".
>
> rm(list=ls(all=TRUE))
> library(XLConnect)
> tab <- loadWorkbook("C:/Users/Downloads/File.xlsx", create = TRUE)
> set <- readWorksheet(tab, sheet = "settings")
> setNum <- readWorksheet(tab, sheet = "settings", colTypes="numeric")

Presumably you could also send the results of

dput(setNum)

.. either as a .txt attachment or embedded in an email.

> index <- which((!is.na(setNum)), arr.ind=TRUE)
> if(length(index)!=0){
>  set[index] <- unlist(lapply(set[index], function(x) as.numeric(as.character(x))))
> }
> ## to check
[[elided Hotmail spam]]
>
> writeWorksheetToFile("C:/Users/Downloads/Test.xlsx", data=set, sheet="settings", styleAction = XLC$"STYLE_ACTION.NONE")
>
> How can I convert the numbers which are stored as characters to numeric?

Have you tried using the formatting features of Excel to change the default "auto" sttings of black columns? You could select an entire column and set its format to numeric. I know this sometimes fixes the annoying habit of Excel of changing texts items withn interior dashes to dates.

--

David Winsemius
Alameda, CA, USA

'Any technology distinguishable from magic is insufficiently advanced.'   -Gehm's Corollary to Clarke's Third Law






        [[alternative HTML version deleted]]


______________________________________________
[hidden email] mailing list -- To UNSUBSCRIBE and more, see
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.
Reply | Threaded
Open this post in threaded view
|

Re: Converting character to numeric using the package "XLConnect"

Jim Lemon-4
Hi Nelly,
Are you trying to convert hexadecimal to decimal?

 nrdf
      Setting Value
1  Parameters     a
2  Parameters     b
3  Parameters     c
4  Parameters     d
5  Parameters     e
6  Parameters     2
7  Parameters     8
8  Parameters     9
9  Parameters     7
10          1     a
11          2     b
12          3     c
13          4     d

strtoi(nrdf$Value,16)
 [1] 10 11 12 13 14  2  8  9  7 10 11 12 13

Jim

On Thu, Aug 31, 2017 at 6:30 AM, Nelly Reduan <[hidden email]> wrote:
> The Excel file can be found from this link: https://1drv.ms/x/s!Apkg2VlgfYyDgQ_mcJ8F4CaXV_Nh

______________________________________________
[hidden email] mailing list -- To UNSUBSCRIBE and more, see
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.
Reply | Threaded
Open this post in threaded view
|

Re: Converting character to numeric using the package "XLConnect"

Jeff Newmiller
In reply to this post by Nelly Reduan
This seems to be a case where the question exists because the asker thinks R acts like Excel, instead of learning how R does work.

Data frames in R are lists of columns. Each column is a vector, and all elements in a vector are the same type. [1] Since a number can be stored in a character string, but a character string cannot be stored in a number, when you try to pull in columns that contain both character and numeric data the only way to hold all of it within the same vector is to use character for all of it. Your attempts to convert any single element of a character vector into numeric are destined to fail.

The key to success will be to avoid attempting that. One way to deal with something like this is to read rows 2 through 10 as one data frame and rows 11 through 14 as another data frame, but I have no idea why you want your data laid out this way so I don't know if this helps you.
--
Sent from my phone. Please excuse my brevity.

On August 30, 2017 1:30:41 PM PDT, Nelly Reduan <[hidden email]> wrote:

>The Excel file can be found from this link:
>https://1drv.ms/x/s!Apkg2VlgfYyDgQ_mcJ8F4CaXV_Nh
>
>[https://r1.res.office365.com/owa/prem/images/dc-xlsx_40.png]<https://1drv.ms/x/s!Apkg2VlgfYyDgQ_mcJ8F4CaXV_Nh>
>File.xlsx<https://1drv.ms/x/s!Apkg2VlgfYyDgQ_mcJ8F4CaXV_Nh>
>Partag� via OneDrive
>
>
>
>
>
>Thanks very much for your help.
>
>Nell
>
>________________________________
>De : David Winsemius <[hidden email]>
>Envoy� : mercredi 30 ao�t 2017 12:59:57
>� : Nelly Reduan
>Cc : [hidden email]
>Objet : Re: [R] Converting character to numeric using the package
>"XLConnect"
>
>
>> On Aug 30, 2017, at 12:24 PM, Nelly Reduan <[hidden email]>
>wrote:
>>
>> Hello,
>>
>> From an Excel file imported into R where each cell contains
>characters, I would like to convert some characters to numeric.
>However, my code doesn�t work. When I write data to worksheets in an
>Excel file, some numbers in the cells are stored as text (instead of
>numeric). Here is my code (the Excel file is attached):
>
>Nope. No file was attached in what was circulated. The list-server
>scrubs potentially malicious files (including zip, csv, or xls files).
>You could send a "csv" file with extension of ".txt".
>>
>> rm(list=ls(all=TRUE))
>> library(XLConnect)
>> tab <- loadWorkbook("C:/Users/Downloads/File.xlsx", create = TRUE)
>> set <- readWorksheet(tab, sheet = "settings")
>> setNum <- readWorksheet(tab, sheet = "settings", colTypes="numeric")
>
>Presumably you could also send the results of
>
>dput(setNum)
>
>.. either as a .txt attachment or embedded in an email.
>
>> index <- which((!is.na(setNum)), arr.ind=TRUE)
>> if(length(index)!=0){
>>  set[index] <- unlist(lapply(set[index], function(x)
>as.numeric(as.character(x))))
>> }
>> ## to check
>[[elided Hotmail spam]]
>>
>> writeWorksheetToFile("C:/Users/Downloads/Test.xlsx", data=set,
>sheet="settings", styleAction = XLC$"STYLE_ACTION.NONE")
>>
>> How can I convert the numbers which are stored as characters to
>numeric?
>
>Have you tried using the formatting features of Excel to change the
>default "auto" sttings of black columns? You could select an entire
>column and set its format to numeric. I know this sometimes fixes the
>annoying habit of Excel of changing texts items withn interior dashes
>to dates.
>
>--
>
>David Winsemius
>Alameda, CA, USA
>
>'Any technology distinguishable from magic is insufficiently advanced.'
>  -Gehm's Corollary to Clarke's Third Law
>
>
>
>
>
>
> [[alternative HTML version deleted]]

______________________________________________
[hidden email] mailing list -- To UNSUBSCRIBE and more, see
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.
Reply | Threaded
Open this post in threaded view
|

Re: Converting character to numeric using the package "XLConnect"

Nelly Reduan
In reply to this post by Jim Lemon-4
Thanks very much for your answer.

I�m trying the function �strtoi� but it fails to convert character to numeric after the command "set[index] <- strtoi(set[index])".


> strtoi(set[index]) ## This works: all values are numeric !!!

[1] 1 2 3 4 2 8 9 7



> set[index] <- strtoi(set[index])



> print(set[10,1]) ## Problem: must be numeric !!!!

[1] "1"



Thanks very much for your help.
Nell


________________________________
De : Jim Lemon <[hidden email]>
Envoy� : mercredi 30 ao�t 2017 15:00:32
� : Nelly Reduan; r-help mailing list
Objet : Re: [R] Converting character to numeric using the package "XLConnect"

Hi Nelly,
Are you trying to convert hexadecimal to decimal?

 nrdf
      Setting Value
1  Parameters     a
2  Parameters     b
3  Parameters     c
4  Parameters     d
5  Parameters     e
6  Parameters     2
7  Parameters     8
8  Parameters     9
9  Parameters     7
10          1     a
11          2     b
12          3     c
13          4     d

strtoi(nrdf$Value,16)
 [1] 10 11 12 13 14  2  8  9  7 10 11 12 13

Jim

On Thu, Aug 31, 2017 at 6:30 AM, Nelly Reduan <[hidden email]> wrote:
> The Excel file can be found from this link: https://1drv.ms/x/s!Apkg2VlgfYyDgQ_mcJ8F4CaXV_Nh

        [[alternative HTML version deleted]]


______________________________________________
[hidden email] mailing list -- To UNSUBSCRIBE and more, see
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.
Reply | Threaded
Open this post in threaded view
|

Re: Converting character to numeric using the package "XLConnect"

Jim Lemon-4
Hi Nelly,
It would be helpful to see what you are actually trying to convert:

strtoi("1")
[1] 1

What is in set[10,1]?

Jim

On Thu, Aug 31, 2017 at 2:01 PM, Nelly Reduan <[hidden email]> wrote:

> Thanks very much for your answer.
>
> I’m trying the function “strtoi” but it fails to convert character to
> numeric after the command "set[index] <- strtoi(set[index])".
>
>
> > strtoi(set[index]) ## This works: all values are numeric !!!
>
> [1] 1 2 3 4 2 8 9 7
>
>
>
> > set[index] <- strtoi(set[index])
>
>
>
> > print(set[10,1]) ## Problem: must be numeric !!!!
>
> [1] "1"
>
> Thanks very much for your help.
> Nell
>
> ------------------------------
> *De :* Jim Lemon <[hidden email]>
> *Envoyé :* mercredi 30 août 2017 15:00:32
> *À :* Nelly Reduan; r-help mailing list
> *Objet :* Re: [R] Converting character to numeric using the package
> "XLConnect"
>
> Hi Nelly,
> Are you trying to convert hexadecimal to decimal?
>
>  nrdf
>       Setting Value
> 1  Parameters     a
> 2  Parameters     b
> 3  Parameters     c
> 4  Parameters     d
> 5  Parameters     e
> 6  Parameters     2
> 7  Parameters     8
> 8  Parameters     9
> 9  Parameters     7
> 10          1     a
> 11          2     b
> 12          3     c
> 13          4     d
>
> strtoi(nrdf$Value,16)
>  [1] 10 11 12 13 14  2  8  9  7 10 11 12 13
>
> Jim
>
> On Thu, Aug 31, 2017 at 6:30 AM, Nelly Reduan <[hidden email]>
> wrote:
> > The Excel file can be found from this link: https://1drv.ms/x/s!
> Apkg2VlgfYyDgQ_mcJ8F4CaXV_Nh
>

        [[alternative HTML version deleted]]

______________________________________________
[hidden email] mailing list -- To UNSUBSCRIBE and more, see
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.
Reply | Threaded
Open this post in threaded view
|

Re: Converting character to numeric using the package "XLConnect"

Jeff Newmiller
In reply to this post by Nelly Reduan
All elements of a vector must be of the same type.

Rephrasing: You cannot change one element of a character vector to a numeric value. The numeric value WILL be converted into character before it is put into the target element.
--
Sent from my phone. Please excuse my brevity.

On August 30, 2017 9:01:03 PM PDT, Nelly Reduan <[hidden email]> wrote:

>Thanks very much for your answer.
>
>I�m trying the function �strtoi� but it fails to convert character to
>numeric after the command "set[index] <- strtoi(set[index])".
>
>
>> strtoi(set[index]) ## This works: all values are numeric !!!
>
>[1] 1 2 3 4 2 8 9 7
>
>
>
>> set[index] <- strtoi(set[index])
>
>
>
>> print(set[10,1]) ## Problem: must be numeric !!!!
>
>[1] "1"
>
>
>
>Thanks very much for your help.
>Nell
>
>
>________________________________
>De : Jim Lemon <[hidden email]>
>Envoy� : mercredi 30 ao�t 2017 15:00:32
>� : Nelly Reduan; r-help mailing list
>Objet : Re: [R] Converting character to numeric using the package
>"XLConnect"
>
>Hi Nelly,
>Are you trying to convert hexadecimal to decimal?
>
> nrdf
>      Setting Value
>1  Parameters     a
>2  Parameters     b
>3  Parameters     c
>4  Parameters     d
>5  Parameters     e
>6  Parameters     2
>7  Parameters     8
>8  Parameters     9
>9  Parameters     7
>10          1     a
>11          2     b
>12          3     c
>13          4     d
>
>strtoi(nrdf$Value,16)
> [1] 10 11 12 13 14  2  8  9  7 10 11 12 13
>
>Jim
>
>On Thu, Aug 31, 2017 at 6:30 AM, Nelly Reduan <[hidden email]>
>wrote:
>> The Excel file can be found from this link:
>https://1drv.ms/x/s!Apkg2VlgfYyDgQ_mcJ8F4CaXV_Nh
>
> [[alternative HTML version deleted]]

______________________________________________
[hidden email] mailing list -- To UNSUBSCRIBE and more, see
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.