If NA exists in column, replace with value in another column - sqldf The Next CEO of Stack OverflowHow to sort a dataframe by multiple column(s)?Grouping functions (tapply, by, aggregate) and the *apply familygiven a dataframe of 3 columns, plot the first 2 on 1 axis, and the 3rd on a separate axis below it using ggplot2Remove Duplicates by Unique Value in another ColumnMap (purrr) to add a range of numbers to a column one by onefilter by date range in sqldfHow to use “sqldf” in a functionRemove first two characters in column using sqldfIf Column contains “-” at the end of a value, remove the “-” at the end - sqldfParse By Delimitor with sqldf
Defamation due to breach of confidentiality
Axiom Schema vs Axiom
Can Plant Growth be repeatedly cast on the same area to exponentially increase the yield of harvests there (more than twice)?
How to avoid supervisors with prejudiced views?
How I can get glyphs from a fraktur font and use them as identifiers?
Poetry, calligrams and TikZ/PStricks challenge
Do they change the text of the seder in Israel?
Does Germany produce more waste than the US?
What happened in Rome, when the western empire "fell"?
Is there a way to save my career from absolute disaster?
Easy to read palindrome checker
Domestic-to-international connection at Orlando (MCO)
If Nick Fury and Coulson already knew about aliens (Kree and Skrull) why did they wait until Thor's appearance to start making weapons?
The exact meaning of 'Mom made me a sandwich'
Flying from Cape Town to England and return to another province
A Man With a Stainless Steel Endoskeleton (like The Terminator) Fighting Cloaked Aliens Only He Can See
Won the lottery - how do I keep the money?
RigExpert AA-35 - Interpreting The Information
Is it my responsibility to learn a new technology in my own time my employer wants to implement?
Is it convenient to ask the journal's editor for two additional days to complete a review?
Grabbing quick drinks
Example of a Mathematician/Physicist whose Other Publications during their PhD eclipsed their PhD Thesis
WOW air has ceased operation, can I get my tickets refunded?
How to find the nth term in the following sequence: 1,1,2,2,4,4,8,8,16,16
If NA exists in column, replace with value in another column - sqldf
The Next CEO of Stack OverflowHow to sort a dataframe by multiple column(s)?Grouping functions (tapply, by, aggregate) and the *apply familygiven a dataframe of 3 columns, plot the first 2 on 1 axis, and the 3rd on a separate axis below it using ggplot2Remove Duplicates by Unique Value in another ColumnMap (purrr) to add a range of numbers to a column one by onefilter by date range in sqldfHow to use “sqldf” in a functionRemove first two characters in column using sqldfIf Column contains “-” at the end of a value, remove the “-” at the end - sqldfParse By Delimitor with sqldf
I have a dataframe below:
df
ColA ColB ColC
NA BN 6
JH NA 8
NA rewr 9
NA NA 10
Expected Output:
newdf
ColA ColB ColC New_Col
NA BN 6 BN
JH NA 8 JH
NA rewr 9 rewr
NA NA 10 NA
How do I do this using sqldf?
This was my attempt but it did not get the output I was looking for:
newdf<- sqldf("SELECT *, replace([ColA], NULL, [ColB]) [New_Col] from df")
r sqldf
asked Mar 8 at 16:21
nak5120nak5120
1,5231230
add a comment |
I have a dataframe below:
df
ColA ColB ColC
NA BN 6
JH NA 8
NA rewr 9
NA NA 10
Expected Output:
newdf
ColA ColB ColC New_Col
NA BN 6 BN
JH NA 8 JH
NA rewr 9 rewr
NA NA 10 NA
How do I do this using sqldf?
This was my attempt but it did not get the output I was looking for:
newdf<- sqldf("SELECT *, replace([ColA], NULL, [ColB]) [New_Col] from df")
r sqldf
asked Mar 8 at 16:21
nak5120nak5120
1,5231230
add a comment |
I have a dataframe below:
df
ColA ColB ColC
NA BN 6
JH NA 8
NA rewr 9
NA NA 10
Expected Output:
newdf
ColA ColB ColC New_Col
NA BN 6 BN
JH NA 8 JH
NA rewr 9 rewr
NA NA 10 NA
How do I do this using sqldf?
This was my attempt but it did not get the output I was looking for:
newdf<- sqldf("SELECT *, replace([ColA], NULL, [ColB]) [New_Col] from df")
r sqldf
asked Mar 8 at 16:21
nak5120nak5120
1,5231230
I have a dataframe below:
df
ColA ColB ColC
NA BN 6
JH NA 8
NA rewr 9
NA NA 10
Expected Output:
newdf
ColA ColB ColC New_Col
NA BN 6 BN
JH NA 8 JH
NA rewr 9 rewr
NA NA 10 NA
How do I do this using sqldf?
This was my attempt but it did not get the output I was looking for:
newdf<- sqldf("SELECT *, replace([ColA], NULL, [ColB]) [New_Col] from df")
r sqldf
r sqldf
asked Mar 8 at 16:21
nak5120nak5120
1,5231230
asked Mar 8 at 16:21
nak5120nak5120
1,5231230
asked Mar 8 at 16:21
nak5120nak5120
1,5231230
asked Mar 8 at 16:21
nak5120nak5120
1,5231230
asked Mar 8 at 16:21
nak5120nak5120
1,5231230
1,5231230
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
Using coalesce
library(sqldf)
sqldf("SELECT ColA, ColB, ColC, coalesce(ColA, ColB) as New_Col from df")
# ColA ColB ColC New_Col
#1 <NA> BN 6 BN
#2 JH <NA> 8 JH
#3 <NA> rewr 9 rewr
#4 <NA> <NA> 10 <NA>
Or with tidyverse
library(dplyr)
df %>%
mutate(New_Col = coalesce(ColA, ColB))
# ColA ColB ColC New_Col
#1 <NA> BN 6 BN
#2 JH <NA> 8 JH
#3 <NA> rewr 9 rewr
#4 <NA> <NA> 10 <NA>
data
df <- structure(list(ColA = c(NA, "JH", NA, NA), ColB = c("BN", NA,
"rewr", NA), ColC = c(6L, 8L, 9L, 10L)), class = "data.frame", row.names = c(NA,
-4L))
answered Mar 8 at 16:27
akrunakrun
418k13206281
1
this worked great, thank you!
– nak5120
Mar 8 at 16:33
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%2f55067120%2fif-na-exists-in-column-replace-with-value-in-another-column-sqldf%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
Using coalesce
library(sqldf)
sqldf("SELECT ColA, ColB, ColC, coalesce(ColA, ColB) as New_Col from df")
# ColA ColB ColC New_Col
#1 <NA> BN 6 BN
#2 JH <NA> 8 JH
#3 <NA> rewr 9 rewr
#4 <NA> <NA> 10 <NA>
Or with tidyverse
library(dplyr)
df %>%
mutate(New_Col = coalesce(ColA, ColB))
# ColA ColB ColC New_Col
#1 <NA> BN 6 BN
#2 JH <NA> 8 JH
#3 <NA> rewr 9 rewr
#4 <NA> <NA> 10 <NA>
data
df <- structure(list(ColA = c(NA, "JH", NA, NA), ColB = c("BN", NA,
"rewr", NA), ColC = c(6L, 8L, 9L, 10L)), class = "data.frame", row.names = c(NA,
-4L))
answered Mar 8 at 16:27
akrunakrun
418k13206281
1
this worked great, thank you!
– nak5120
Mar 8 at 16:33
add a comment |
Using coalesce
library(sqldf)
sqldf("SELECT ColA, ColB, ColC, coalesce(ColA, ColB) as New_Col from df")
# ColA ColB ColC New_Col
#1 <NA> BN 6 BN
#2 JH <NA> 8 JH
#3 <NA> rewr 9 rewr
#4 <NA> <NA> 10 <NA>
Or with tidyverse
library(dplyr)
df %>%
mutate(New_Col = coalesce(ColA, ColB))
# ColA ColB ColC New_Col
#1 <NA> BN 6 BN
#2 JH <NA> 8 JH
#3 <NA> rewr 9 rewr
#4 <NA> <NA> 10 <NA>
data
df <- structure(list(ColA = c(NA, "JH", NA, NA), ColB = c("BN", NA,
"rewr", NA), ColC = c(6L, 8L, 9L, 10L)), class = "data.frame", row.names = c(NA,
-4L))
answered Mar 8 at 16:27
akrunakrun
418k13206281
1
this worked great, thank you!
– nak5120
Mar 8 at 16:33
add a comment |
Using coalesce
library(sqldf)
sqldf("SELECT ColA, ColB, ColC, coalesce(ColA, ColB) as New_Col from df")
# ColA ColB ColC New_Col
#1 <NA> BN 6 BN
#2 JH <NA> 8 JH
#3 <NA> rewr 9 rewr
#4 <NA> <NA> 10 <NA>
Or with tidyverse
library(dplyr)
df %>%
mutate(New_Col = coalesce(ColA, ColB))
# ColA ColB ColC New_Col
#1 <NA> BN 6 BN
#2 JH <NA> 8 JH
#3 <NA> rewr 9 rewr
#4 <NA> <NA> 10 <NA>
data
df <- structure(list(ColA = c(NA, "JH", NA, NA), ColB = c("BN", NA,
"rewr", NA), ColC = c(6L, 8L, 9L, 10L)), class = "data.frame", row.names = c(NA,
-4L))
answered Mar 8 at 16:27
akrunakrun
418k13206281
Using coalesce
library(sqldf)
sqldf("SELECT ColA, ColB, ColC, coalesce(ColA, ColB) as New_Col from df")
# ColA ColB ColC New_Col
#1 <NA> BN 6 BN
#2 JH <NA> 8 JH
#3 <NA> rewr 9 rewr
#4 <NA> <NA> 10 <NA>
Or with tidyverse
library(dplyr)
df %>%
mutate(New_Col = coalesce(ColA, ColB))
# ColA ColB ColC New_Col
#1 <NA> BN 6 BN
#2 JH <NA> 8 JH
#3 <NA> rewr 9 rewr
#4 <NA> <NA> 10 <NA>
data
df <- structure(list(ColA = c(NA, "JH", NA, NA), ColB = c("BN", NA,
"rewr", NA), ColC = c(6L, 8L, 9L, 10L)), class = "data.frame", row.names = c(NA,
-4L))
answered Mar 8 at 16:27
akrunakrun
418k13206281
answered Mar 8 at 16:27
akrunakrun
418k13206281
answered Mar 8 at 16:27
akrunakrun
418k13206281
answered Mar 8 at 16:27
akrunakrun
418k13206281
418k13206281
1
this worked great, thank you!
– nak5120
Mar 8 at 16:33
add a comment |
1
this worked great, thank you!
– nak5120
Mar 8 at 16:33
1
1
this worked great, thank you!
– nak5120
Mar 8 at 16:33
this worked great, thank you!
– nak5120
Mar 8 at 16:33
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%2f55067120%2fif-na-exists-in-column-replace-with-value-in-another-column-sqldf%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
