Fill NA Values in R with Data from Other Data Frame

-1

I have 2 data frames with some identical and unique columns. The first data frame has some NA values in the identical columns. I would like to replace those with the data from the second data frame and join all columns into 1 data frame. Ultimately, the solution will need to be done with very large data frames so efficiency is ideal.

Intial data frames:

df1 = data.frame(x = c("Canada", "Canada", NA, NA), 

            y = c(2010, 2010, 2011, 2011), 

            z = c(NA, NA, "CAN", "CAN"),

            Code = c(2, 6, 2, 6))



df2 = data.frame(x = c("Canada", "Canada", "Canada", "Canada"), 

             y = c(2013, 2012, 2011, 2010), 

             z = c("CAN", "CAN", "CAN", "CAN"), 

             GDP = c(22, 20, 18, 16))

Expected result:

df3 = data.frame(x = c("Canada", "Canada", "Canada", "Canada"), 

             y = c(2010, 2010, 2011, 2011), 

             z = c("CAN", "CAN", "CAN", "CAN"),

             Code = c(2, 6, 2, 6), 

             GDP = c(16, 16, 18, 18))

edited Nov 24 '18 at 22:30

asked Nov 24 '18 at 21:39

Ray B

153

2

Please read How to create a Minimal, Complete, and Verifiable example and edit your question accordingly. Don't post code, data, or error messages as pictures, post the text directly here on SO.

– Mr. T
Nov 24 '18 at 21:42

2

Please do not post images of data: they cannot copy/pasted by us, they mask the true nature of the data, screen readers do nothing with them, and some mobile devices have a hard time with larger images. To make this question reproducible, can you replace the images with the output of dput(x) where x is a representative sample of the data? This might be something like dput(head(GDP[1:5])) if those are enough rows/columns to adequately represent the data. (Same for the other frame(s).)

– r2evans
Nov 24 '18 at 21:44

Easiest would be to use left_join() from dplyr, making a new variable rather than trying to fill the NAs. The code would be something like newdf <- dfA %>% left_join(dfB, by = "Country").

– Joe
Nov 24 '18 at 22:09

1

I think I've formatted the post appropriately. I'm unsure how to create the nice tables I see in other posts but the output of the code above demonstrates the problem I'm having.

– Ray B
Nov 24 '18 at 22:44

add a comment |

-1

Intial data frames:

df1 = data.frame(x = c("Canada", "Canada", NA, NA), 

            y = c(2010, 2010, 2011, 2011), 

            z = c(NA, NA, "CAN", "CAN"),

            Code = c(2, 6, 2, 6))



df2 = data.frame(x = c("Canada", "Canada", "Canada", "Canada"), 

             y = c(2013, 2012, 2011, 2010), 

             z = c("CAN", "CAN", "CAN", "CAN"), 

             GDP = c(22, 20, 18, 16))

Expected result:

df3 = data.frame(x = c("Canada", "Canada", "Canada", "Canada"), 

             y = c(2010, 2010, 2011, 2011), 

             z = c("CAN", "CAN", "CAN", "CAN"),

             Code = c(2, 6, 2, 6), 

             GDP = c(16, 16, 18, 18))

edited Nov 24 '18 at 22:30

asked Nov 24 '18 at 21:39

Ray B

153

2

Please read How to create a Minimal, Complete, and Verifiable example and edit your question accordingly. Don't post code, data, or error messages as pictures, post the text directly here on SO.

– Mr. T
Nov 24 '18 at 21:42

2

Please do not post images of data: they cannot copy/pasted by us, they mask the true nature of the data, screen readers do nothing with them, and some mobile devices have a hard time with larger images. To make this question reproducible, can you replace the images with the output of dput(x) where x is a representative sample of the data? This might be something like dput(head(GDP[1:5])) if those are enough rows/columns to adequately represent the data. (Same for the other frame(s).)

– r2evans
Nov 24 '18 at 21:44

Easiest would be to use left_join() from dplyr, making a new variable rather than trying to fill the NAs. The code would be something like newdf <- dfA %>% left_join(dfB, by = "Country").

– Joe
Nov 24 '18 at 22:09

1

I think I've formatted the post appropriately. I'm unsure how to create the nice tables I see in other posts but the output of the code above demonstrates the problem I'm having.

– Ray B
Nov 24 '18 at 22:44

add a comment |

-1

Intial data frames:

df1 = data.frame(x = c("Canada", "Canada", NA, NA), 

            y = c(2010, 2010, 2011, 2011), 

            z = c(NA, NA, "CAN", "CAN"),

            Code = c(2, 6, 2, 6))



df2 = data.frame(x = c("Canada", "Canada", "Canada", "Canada"), 

             y = c(2013, 2012, 2011, 2010), 

             z = c("CAN", "CAN", "CAN", "CAN"), 

             GDP = c(22, 20, 18, 16))

Expected result:

df3 = data.frame(x = c("Canada", "Canada", "Canada", "Canada"), 

             y = c(2010, 2010, 2011, 2011), 

             z = c("CAN", "CAN", "CAN", "CAN"),

             Code = c(2, 6, 2, 6), 

             GDP = c(16, 16, 18, 18))

edited Nov 24 '18 at 22:30

asked Nov 24 '18 at 21:39

Ray B

153

Intial data frames:

df1 = data.frame(x = c("Canada", "Canada", NA, NA), 

            y = c(2010, 2010, 2011, 2011), 

            z = c(NA, NA, "CAN", "CAN"),

            Code = c(2, 6, 2, 6))



df2 = data.frame(x = c("Canada", "Canada", "Canada", "Canada"), 

             y = c(2013, 2012, 2011, 2010), 

             z = c("CAN", "CAN", "CAN", "CAN"), 

             GDP = c(22, 20, 18, 16))

Expected result:

df3 = data.frame(x = c("Canada", "Canada", "Canada", "Canada"), 

             y = c(2010, 2010, 2011, 2011), 

             z = c("CAN", "CAN", "CAN", "CAN"),

             Code = c(2, 6, 2, 6), 

             GDP = c(16, 16, 18, 18))

r dplyr

edited Nov 24 '18 at 22:30

asked Nov 24 '18 at 21:39

Ray B

153

edited Nov 24 '18 at 22:30

asked Nov 24 '18 at 21:39

Ray B

153

edited Nov 24 '18 at 22:30

asked Nov 24 '18 at 21:39

Ray B

153

asked Nov 24 '18 at 21:39

Ray B

153

asked Nov 24 '18 at 21:39

Ray B

153

2

Please read How to create a Minimal, Complete, and Verifiable example and edit your question accordingly. Don't post code, data, or error messages as pictures, post the text directly here on SO.

– Mr. T
Nov 24 '18 at 21:42

2

Please do not post images of data: they cannot copy/pasted by us, they mask the true nature of the data, screen readers do nothing with them, and some mobile devices have a hard time with larger images. To make this question reproducible, can you replace the images with the output of dput(x) where x is a representative sample of the data? This might be something like dput(head(GDP[1:5])) if those are enough rows/columns to adequately represent the data. (Same for the other frame(s).)

– r2evans
Nov 24 '18 at 21:44

Easiest would be to use left_join() from dplyr, making a new variable rather than trying to fill the NAs. The code would be something like newdf <- dfA %>% left_join(dfB, by = "Country").

– Joe
Nov 24 '18 at 22:09

1

I think I've formatted the post appropriately. I'm unsure how to create the nice tables I see in other posts but the output of the code above demonstrates the problem I'm having.

– Ray B
Nov 24 '18 at 22:44

add a comment |

2

Please read How to create a Minimal, Complete, and Verifiable example and edit your question accordingly. Don't post code, data, or error messages as pictures, post the text directly here on SO.

– Mr. T
Nov 24 '18 at 21:42

2

Please do not post images of data: they cannot copy/pasted by us, they mask the true nature of the data, screen readers do nothing with them, and some mobile devices have a hard time with larger images. To make this question reproducible, can you replace the images with the output of dput(x) where x is a representative sample of the data? This might be something like dput(head(GDP[1:5])) if those are enough rows/columns to adequately represent the data. (Same for the other frame(s).)

– r2evans
Nov 24 '18 at 21:44

Easiest would be to use left_join() from dplyr, making a new variable rather than trying to fill the NAs. The code would be something like newdf <- dfA %>% left_join(dfB, by = "Country").

– Joe
Nov 24 '18 at 22:09

1

I think I've formatted the post appropriately. I'm unsure how to create the nice tables I see in other posts but the output of the code above demonstrates the problem I'm having.

– Ray B
Nov 24 '18 at 22:44

Please read How to create a Minimal, Complete, and Verifiable example and edit your question accordingly. Don't post code, data, or error messages as pictures, post the text directly here on SO.

– Mr. T
Nov 24 '18 at 21:42

Please do not post images of data: they cannot copy/pasted by us, they mask the true nature of the data, screen readers do nothing with them, and some mobile devices have a hard time with larger images. To make this question reproducible, can you replace the images with the output of dput(x) where x is a representative sample of the data? This might be something like dput(head(GDP[1:5])) if those are enough rows/columns to adequately represent the data. (Same for the other frame(s).)

– r2evans
Nov 24 '18 at 21:44

Easiest would be to use left_join() from dplyr, making a new variable rather than trying to fill the NAs. The code would be something like newdf <- dfA %>% left_join(dfB, by = "Country").

– Joe
Nov 24 '18 at 22:09

I think I've formatted the post appropriately. I'm unsure how to create the nice tables I see in other posts but the output of the code above demonstrates the problem I'm having.

– Ray B
Nov 24 '18 at 22:44

add a comment |

1 Answer
1

active

oldest

votes

There's probably a more concise way to write this, but it should execute pretty quickly, since it relies primarily on two joins.

First, I make a lookup table from df2, which I assume has a single value of z for each value of x. The lookup table only needs those two columns.

library(dplyr)

lookup <- df2 %>% distinct(x, z)

Then I do two joins, first joining df1 with lookup using z to get a consistent x, and then using the clean set of x, y, and Code to join with df2 to get the corresponding z and GDP values.

df1 %>%

  left_join(lookup, by = "z") %>%

  mutate(x = if_else(is.na(x.x), x.y, x.x)) %>%

  select(x, y, Code) %>%

  left_join(df2, by = c("x", "y")) %>%

  select(x, y, z, Code, GDP)  # Optional, just to resort columns



#       x    y   z Code GDP

#1 Canada 2010 CAN    2  16

#2 Canada 2010 CAN    6  16

#3 Canada 2011 CAN    2  18

#4 Canada 2011 CAN    6  18

edited Nov 24 '18 at 23:28

answered Nov 24 '18 at 23:23

Jon Spring

6,5031726

Thank you so much! For some reason, it created 4 duplicates of all the observations it filled in z for but those can easily be dropped and is most likely something I've done wrong. This was exactly what I needed though.

– Ray B
Nov 26 '18 at 5:57

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
});

}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53462596%2ffill-na-values-in-r-with-data-from-other-data-frame%23new-answer', 'question_page');
}
);

Post as a guest

Name

Required, but never shown

1 Answer
1

active

oldest

votes

1 Answer
1

active

oldest

votes

There's probably a more concise way to write this, but it should execute pretty quickly, since it relies primarily on two joins.

First, I make a lookup table from df2, which I assume has a single value of z for each value of x. The lookup table only needs those two columns.

library(dplyr)

lookup <- df2 %>% distinct(x, z)

df1 %>%

  left_join(lookup, by = "z") %>%

  mutate(x = if_else(is.na(x.x), x.y, x.x)) %>%

  select(x, y, Code) %>%

  left_join(df2, by = c("x", "y")) %>%

  select(x, y, z, Code, GDP)  # Optional, just to resort columns



#       x    y   z Code GDP

#1 Canada 2010 CAN    2  16

#2 Canada 2010 CAN    6  16

#3 Canada 2011 CAN    2  18

#4 Canada 2011 CAN    6  18

edited Nov 24 '18 at 23:28

answered Nov 24 '18 at 23:23

Jon Spring

6,5031726

Thank you so much! For some reason, it created 4 duplicates of all the observations it filled in z for but those can easily be dropped and is most likely something I've done wrong. This was exactly what I needed though.

– Ray B
Nov 26 '18 at 5:57

add a comment |

There's probably a more concise way to write this, but it should execute pretty quickly, since it relies primarily on two joins.

First, I make a lookup table from df2, which I assume has a single value of z for each value of x. The lookup table only needs those two columns.

library(dplyr)

lookup <- df2 %>% distinct(x, z)

df1 %>%

  left_join(lookup, by = "z") %>%

  mutate(x = if_else(is.na(x.x), x.y, x.x)) %>%

  select(x, y, Code) %>%

  left_join(df2, by = c("x", "y")) %>%

  select(x, y, z, Code, GDP)  # Optional, just to resort columns



#       x    y   z Code GDP

#1 Canada 2010 CAN    2  16

#2 Canada 2010 CAN    6  16

#3 Canada 2011 CAN    2  18

#4 Canada 2011 CAN    6  18

edited Nov 24 '18 at 23:28

answered Nov 24 '18 at 23:23

Jon Spring

6,5031726

Thank you so much! For some reason, it created 4 duplicates of all the observations it filled in z for but those can easily be dropped and is most likely something I've done wrong. This was exactly what I needed though.

– Ray B
Nov 26 '18 at 5:57

add a comment |

There's probably a more concise way to write this, but it should execute pretty quickly, since it relies primarily on two joins.

First, I make a lookup table from df2, which I assume has a single value of z for each value of x. The lookup table only needs those two columns.

library(dplyr)

lookup <- df2 %>% distinct(x, z)

df1 %>%

  left_join(lookup, by = "z") %>%

  mutate(x = if_else(is.na(x.x), x.y, x.x)) %>%

  select(x, y, Code) %>%

  left_join(df2, by = c("x", "y")) %>%

  select(x, y, z, Code, GDP)  # Optional, just to resort columns



#       x    y   z Code GDP

#1 Canada 2010 CAN    2  16

#2 Canada 2010 CAN    6  16

#3 Canada 2011 CAN    2  18

#4 Canada 2011 CAN    6  18

edited Nov 24 '18 at 23:28

answered Nov 24 '18 at 23:23

Jon Spring

6,5031726

There's probably a more concise way to write this, but it should execute pretty quickly, since it relies primarily on two joins.

First, I make a lookup table from df2, which I assume has a single value of z for each value of x. The lookup table only needs those two columns.

library(dplyr)

lookup <- df2 %>% distinct(x, z)

df1 %>%

  left_join(lookup, by = "z") %>%

  mutate(x = if_else(is.na(x.x), x.y, x.x)) %>%

  select(x, y, Code) %>%

  left_join(df2, by = c("x", "y")) %>%

  select(x, y, z, Code, GDP)  # Optional, just to resort columns



#       x    y   z Code GDP

#1 Canada 2010 CAN    2  16

#2 Canada 2010 CAN    6  16

#3 Canada 2011 CAN    2  18

#4 Canada 2011 CAN    6  18

edited Nov 24 '18 at 23:28

answered Nov 24 '18 at 23:23

Jon Spring

6,5031726

edited Nov 24 '18 at 23:28

answered Nov 24 '18 at 23:23

Jon Spring

6,5031726

answered Nov 24 '18 at 23:23

Jon Spring

6,5031726

answered Nov 24 '18 at 23:23

Jon Spring

6,5031726

Thank you so much! For some reason, it created 4 duplicates of all the observations it filled in z for but those can easily be dropped and is most likely something I've done wrong. This was exactly what I needed though.

– Ray B
Nov 26 '18 at 5:57

add a comment |

Thank you so much! For some reason, it created 4 duplicates of all the observations it filled in z for but those can easily be dropped and is most likely something I've done wrong. This was exactly what I needed though.

– Ray B
Nov 26 '18 at 5:57

Thank you so much! For some reason, it created 4 duplicates of all the observations it filled in z for but those can easily be dropped and is most likely something I've done wrong. This was exactly what I needed though.

– Ray B
Nov 26 '18 at 5:57

add a comment |

draft saved

draft discarded

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.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

搜尋此網誌

Tukukkk