Changing data type in data frameDrop factor levels in a subsetted data frameHow to join (merge) data frames (inner, outer, left, right)What is a correct mime type for docx, pptx etc?R - list to data frameDrop data frame columns by nameChanging column names of a data frameHow to create a nested for loop in R for this (second) peculiar calculationRead excel data as is using readxl in RFormatting the exported data in RChanging data format from DataTime to general r
Has there been any indication at all that further negotiation between the UK and EU is possible?
Can the negators "jamais, rien, personne, plus, ni, aucun" be used in a single sentence?
Links to webpages in books
How would modern naval warfare have to have developed differently for battleships to still be relevant in the 21st century?
What does "play with your toy’s toys" mean?
Did Karl Marx ever use any example that involved cotton and dollars to illustrate the way capital and surplus value were generated?
Impossible darts scores
If you snatch, I trade
Accidentals and ties
If I wouldn't want to read the story, is writing it still a good idea?
STM Microcontroller burns every time
Why doesn't a marching band have strings?
Can humans ever directly see a few photons at a time? Can a human see a single photon?
Would it be a copyright violation if I made a character’s full name refer to a song?
Why do some professors with PhDs leave their professorships to teach high school?
Why do all the teams that I have worked with always finish a sprint without completion of all the stories?
Are all instances of trolls turning to stone ultimately references back to Tolkien?
What is the legal status of travelling with methadone in your carry-on?
Why cruise at 7000' in an A319?
How to make clear to people I don't want to answer their "Where are you from?" question?
Suggested order for Amazon Prime Doctor Who series
What are the penalties for overstaying in USA?
Unusual mail headers, evidence of an attempted attack. Have I been pwned?
3D Crossword, Cryptic, Statue View & Maze
Changing data type in data frame
Drop factor levels in a subsetted data frameHow to join (merge) data frames (inner, outer, left, right)What is a correct mime type for docx, pptx etc?R - list to data frameDrop data frame columns by nameChanging column names of a data frameHow to create a nested for loop in R for this (second) peculiar calculationRead excel data as is using readxl in RFormatting the exported data in RChanging data format from DataTime to general r
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;
I have some data tables in Excel spreadsheets which I am using in R. Some of the tables store numbers as text i.e. numeric values are stored as characters.
To clarify, it is not a formatting that is a problem but numbers themselves. The Excel (and R) sees such numbers as characters such as letters, rather then numbers.
Because formatting seems to be an issue, addStyle function in openxlsx did not work for me.
After some googling, I've decided to try and write a for loop that will check each value individually.I wrote a nested for loop that checks each value and overwrites it if it is a number (code is below).This seems to work logically but values do not get overwritten i.e. values that were stored as text are still there.
library(readxl)
library(openxlsx)
wb<-loadWorkbook(choose.files())
data0<-as.data.frame(read_excel(choose.files(),sheet=1,range = "B1:E1131"))
data<-data0
for(i in 1:ncol(data))
for(j in 1:nrow(data))
if(is.numeric(as.numeric(data[j,i]))&&!is.na(as.numeric(data[j,i])))
data[j,i]<-as.numeric(data[j,i])
Desired outcome:
I would like to change data in column "Expenses" (in a picture below) to data in a column to its right via R.
r excel
asked Mar 25 at 9:42
JoeJoe
498 bronze badges
|
show 1 more comment
I have some data tables in Excel spreadsheets which I am using in R. Some of the tables store numbers as text i.e. numeric values are stored as characters.
To clarify, it is not a formatting that is a problem but numbers themselves. The Excel (and R) sees such numbers as characters such as letters, rather then numbers.
Because formatting seems to be an issue, addStyle function in openxlsx did not work for me.
After some googling, I've decided to try and write a for loop that will check each value individually.I wrote a nested for loop that checks each value and overwrites it if it is a number (code is below).This seems to work logically but values do not get overwritten i.e. values that were stored as text are still there.
library(readxl)
library(openxlsx)
wb<-loadWorkbook(choose.files())
data0<-as.data.frame(read_excel(choose.files(),sheet=1,range = "B1:E1131"))
data<-data0
for(i in 1:ncol(data))
for(j in 1:nrow(data))
if(is.numeric(as.numeric(data[j,i]))&&!is.na(as.numeric(data[j,i])))
data[j,i]<-as.numeric(data[j,i])
Desired outcome:
I would like to change data in column "Expenses" (in a picture below) to data in a column to its right via R.
r excel
asked Mar 25 at 9:42
JoeJoe
498 bronze badges
I sometimes use the trick of multiplying by 1 ie = E1 * 1...
– Solar Mike
Mar 25 at 9:48
1
Why don't you just useas.numericon the whole columns? e.g.df$col1 <- as.numeric(df$col1)?
– symbolrush
Mar 25 at 9:48
3
You can use thecol_types-argument in thereadxl::read_excel()-function to force reading of text/numeric/date/... data
– Wimpel
Mar 25 at 9:55
@Wimpel That seems to work quite nicely. Would it work, if say I have some empty cells or character cells in a column which I would like to be numeric? Also, are there situations when it will not work ( I had some situations when I used it before, in a big piece of code and it did not work).
– Joe
Mar 25 at 10:03
@Num I have not yet encoutered problems with empty cells. My production data is well over 100k rows per excel-sheet. For specific cases; you can define a vector with 'empty'-values by defining thena-argument form theread_excel()-function to handle cells with specific content as missing.
– Wimpel
Mar 25 at 10:12
|
show 1 more comment
I have some data tables in Excel spreadsheets which I am using in R. Some of the tables store numbers as text i.e. numeric values are stored as characters.
To clarify, it is not a formatting that is a problem but numbers themselves. The Excel (and R) sees such numbers as characters such as letters, rather then numbers.
Because formatting seems to be an issue, addStyle function in openxlsx did not work for me.
After some googling, I've decided to try and write a for loop that will check each value individually.I wrote a nested for loop that checks each value and overwrites it if it is a number (code is below).This seems to work logically but values do not get overwritten i.e. values that were stored as text are still there.
library(readxl)
library(openxlsx)
wb<-loadWorkbook(choose.files())
data0<-as.data.frame(read_excel(choose.files(),sheet=1,range = "B1:E1131"))
data<-data0
for(i in 1:ncol(data))
for(j in 1:nrow(data))
if(is.numeric(as.numeric(data[j,i]))&&!is.na(as.numeric(data[j,i])))
data[j,i]<-as.numeric(data[j,i])
Desired outcome:
I would like to change data in column "Expenses" (in a picture below) to data in a column to its right via R.
r excel
asked Mar 25 at 9:42
JoeJoe
498 bronze badges
I have some data tables in Excel spreadsheets which I am using in R. Some of the tables store numbers as text i.e. numeric values are stored as characters.
To clarify, it is not a formatting that is a problem but numbers themselves. The Excel (and R) sees such numbers as characters such as letters, rather then numbers.
Because formatting seems to be an issue, addStyle function in openxlsx did not work for me.
After some googling, I've decided to try and write a for loop that will check each value individually.I wrote a nested for loop that checks each value and overwrites it if it is a number (code is below).This seems to work logically but values do not get overwritten i.e. values that were stored as text are still there.
library(readxl)
library(openxlsx)
wb<-loadWorkbook(choose.files())
data0<-as.data.frame(read_excel(choose.files(),sheet=1,range = "B1:E1131"))
data<-data0
for(i in 1:ncol(data))
for(j in 1:nrow(data))
if(is.numeric(as.numeric(data[j,i]))&&!is.na(as.numeric(data[j,i])))
data[j,i]<-as.numeric(data[j,i])
Desired outcome:
I would like to change data in column "Expenses" (in a picture below) to data in a column to its right via R.
r excel
r excel
asked Mar 25 at 9:42
JoeJoe
498 bronze badges
asked Mar 25 at 9:42
JoeJoe
498 bronze badges
asked Mar 25 at 9:42
JoeJoe
498 bronze badges
asked Mar 25 at 9:42
JoeJoe
498 bronze badges
asked Mar 25 at 9:42
JoeJoe
498 bronze badges
498 bronze badges
I sometimes use the trick of multiplying by 1 ie = E1 * 1...
– Solar Mike
Mar 25 at 9:48
1
Why don't you just useas.numericon the whole columns? e.g.df$col1 <- as.numeric(df$col1)?
– symbolrush
Mar 25 at 9:48
3
You can use thecol_types-argument in thereadxl::read_excel()-function to force reading of text/numeric/date/... data
– Wimpel
Mar 25 at 9:55
@Wimpel That seems to work quite nicely. Would it work, if say I have some empty cells or character cells in a column which I would like to be numeric? Also, are there situations when it will not work ( I had some situations when I used it before, in a big piece of code and it did not work).
– Joe
Mar 25 at 10:03
@Num I have not yet encoutered problems with empty cells. My production data is well over 100k rows per excel-sheet. For specific cases; you can define a vector with 'empty'-values by defining thena-argument form theread_excel()-function to handle cells with specific content as missing.
– Wimpel
Mar 25 at 10:12
|
show 1 more comment
I sometimes use the trick of multiplying by 1 ie = E1 * 1...
– Solar Mike
Mar 25 at 9:48
1
Why don't you just useas.numericon the whole columns? e.g.df$col1 <- as.numeric(df$col1)?
– symbolrush
Mar 25 at 9:48
3
You can use thecol_types-argument in thereadxl::read_excel()-function to force reading of text/numeric/date/... data
– Wimpel
Mar 25 at 9:55
@Wimpel That seems to work quite nicely. Would it work, if say I have some empty cells or character cells in a column which I would like to be numeric? Also, are there situations when it will not work ( I had some situations when I used it before, in a big piece of code and it did not work).
– Joe
Mar 25 at 10:03
@Num I have not yet encoutered problems with empty cells. My production data is well over 100k rows per excel-sheet. For specific cases; you can define a vector with 'empty'-values by defining thena-argument form theread_excel()-function to handle cells with specific content as missing.
– Wimpel
Mar 25 at 10:12
I sometimes use the trick of multiplying by 1 ie = E1 * 1...
– Solar Mike
Mar 25 at 9:48
I sometimes use the trick of multiplying by 1 ie = E1 * 1...
– Solar Mike
Mar 25 at 9:48
1
1
Why don't you just use
as.numeric on the whole columns? e.g. df$col1 <- as.numeric(df$col1)?– symbolrush
Mar 25 at 9:48
Why don't you just use
as.numeric on the whole columns? e.g. df$col1 <- as.numeric(df$col1)?– symbolrush
Mar 25 at 9:48
3
3
You can use the
col_types-argument in the readxl::read_excel()-function to force reading of text/numeric/date/... data– Wimpel
Mar 25 at 9:55
You can use the
col_types-argument in the readxl::read_excel()-function to force reading of text/numeric/date/... data– Wimpel
Mar 25 at 9:55
@Wimpel That seems to work quite nicely. Would it work, if say I have some empty cells or character cells in a column which I would like to be numeric? Also, are there situations when it will not work ( I had some situations when I used it before, in a big piece of code and it did not work).
– Joe
Mar 25 at 10:03
@Wimpel That seems to work quite nicely. Would it work, if say I have some empty cells or character cells in a column which I would like to be numeric? Also, are there situations when it will not work ( I had some situations when I used it before, in a big piece of code and it did not work).
– Joe
Mar 25 at 10:03
@Num I have not yet encoutered problems with empty cells. My production data is well over 100k rows per excel-sheet. For specific cases; you can define a vector with 'empty'-values by defining the
na-argument form the read_excel()-function to handle cells with specific content as missing.– Wimpel
Mar 25 at 10:12
@Num I have not yet encoutered problems with empty cells. My production data is well over 100k rows per excel-sheet. For specific cases; you can define a vector with 'empty'-values by defining the
na-argument form the read_excel()-function to handle cells with specific content as missing.– Wimpel
Mar 25 at 10:12
|
show 1 more comment
1 Answer
1
active
oldest
votes
coming from my comment:
You can use the col_types-argument in the readxl::read_excel()-function to force reading of text/numeric/date/... data
answered Mar 25 at 10:26
WimpelWimpel
7,1225 silver badges23 bronze badges
add a comment |
Your Answer
StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55334965%2fchanging-data-type-in-data-frame%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
coming from my comment:
You can use the col_types-argument in the readxl::read_excel()-function to force reading of text/numeric/date/... data
answered Mar 25 at 10:26
WimpelWimpel
7,1225 silver badges23 bronze badges
add a comment |
coming from my comment:
You can use the col_types-argument in the readxl::read_excel()-function to force reading of text/numeric/date/... data
answered Mar 25 at 10:26
WimpelWimpel
7,1225 silver badges23 bronze badges
add a comment |
coming from my comment:
You can use the col_types-argument in the readxl::read_excel()-function to force reading of text/numeric/date/... data
answered Mar 25 at 10:26
WimpelWimpel
7,1225 silver badges23 bronze badges
coming from my comment:
You can use the col_types-argument in the readxl::read_excel()-function to force reading of text/numeric/date/... data
answered Mar 25 at 10:26
WimpelWimpel
7,1225 silver badges23 bronze badges
answered Mar 25 at 10:26
WimpelWimpel
7,1225 silver badges23 bronze badges
answered Mar 25 at 10:26
WimpelWimpel
7,1225 silver badges23 bronze badges
answered Mar 25 at 10:26
WimpelWimpel
7,1225 silver badges23 bronze badges
7,1225 silver badges23 bronze badges
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55334965%2fchanging-data-type-in-data-frame%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
I sometimes use the trick of multiplying by 1 ie = E1 * 1...
– Solar Mike
Mar 25 at 9:48
1
Why don't you just use
as.numericon the whole columns? e.g.df$col1 <- as.numeric(df$col1)?– symbolrush
Mar 25 at 9:48
3
You can use the
col_types-argument in thereadxl::read_excel()-function to force reading of text/numeric/date/... data– Wimpel
Mar 25 at 9:55
@Wimpel That seems to work quite nicely. Would it work, if say I have some empty cells or character cells in a column which I would like to be numeric? Also, are there situations when it will not work ( I had some situations when I used it before, in a big piece of code and it did not work).
– Joe
Mar 25 at 10:03
@Num I have not yet encoutered problems with empty cells. My production data is well over 100k rows per excel-sheet. For specific cases; you can define a vector with 'empty'-values by defining the
na-argument form theread_excel()-function to handle cells with specific content as missing.– Wimpel
Mar 25 at 10:12