Styjun

Why can't my huge trees be chopped down?

Word for showing a small part of something briefly to hint to its existence or beauty without fully uncovering it

Request for a Latin phrase as motto "God is highest/supreme"

Why does Canada require mandatory bilingualism in all government posts?

What is the most common end of life issue for a car?

Is it legal for private citizens to "impound" e-scooters?

Finding minimum time for vehicle to reach to its destination

How much were the LMs maneuvered to their landing points?

How to handle academic references for US PhD program, when I have been out of academia for a (very) long time?

Why didn't Britain or any other European power colonise Abyssinia/Ethiopia before 1936?

If Trump gets impeached, how long would Pence be president?

Send a single HTML email from Thunderbird, overriding the default "plain text" setting

What is the most efficient way to write 'for' loops in Matlab?

What are the different qualities of the intervals?

How to avoid theft of intellectual property when trying to obtain a Ph.D?

Why/when is AC-DC-AC conversion superior to direct AC-AC conversion?

Unethical behavior : should I report it?

Can you type a tilde on the key under ESC on a European ISO keyboard set to US

Is my employer paying me fairly? Going from 1099 to W2

Why isn't there a serious attempt at creating a third mass-appeal party in the US?

Heisenberg uncertainty principle in daily life

Suggestions for protecting jeans from saddle clamp bolt

How can I say in Russian "they cannot make the tournament attractive by itself"?

sfdx force:org:list --all doesn't show all scratch orgs

How to merge data to apply to all unique conditions of a column in second data set, even when not occuring

How to join (merge) data frames (inner, outer, left, right)Grouping functions (tapply, by, aggregate) and the *apply familyMerge data sets by row differening columnsSimultaneously merge multiple data.frames in a listdata.table vs dplyr: can one do something well the other can't or does poorly?How can I rename all columns of a data frame based on another data frame in R?R Merge two data frames , with one data frame having non-unique keyUsing rbind to merge all columns in data frameAdding name of individual data frame to new column in merged data frameMerge 3 columns based on unique values?

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;

0

I am trying to insert new rows of data based on unique values of a column in my original data set. I have the following dummy data set:

sites<-c("10","10","11","11","12","12")
ID<-c("A","A","B","B","C","D")
value<-c("4","6","5","2","7","8")
dataframe<-data.frame(sites, ID, value)

sites<-c("10","10","11","11","12","12","13","14","15")
dataframe2<-data.frame(sites)

Producing:

 sites ID value
 10 A 4
 10 A 6
 11 B 5
 11 B 2
 12 C 7
 12 D 8

 sites
 10
 10
 11
 11
 12
 12
 13
 14
 15

For each unique value in column ID, I would like each site number from the second data frame applied, and when there is no value I would like it to print 0.

So for example, ID A would have all sites from site2 listed and when there is no value (ie for site 11, 12, 13,14) I would like it to list 0 for value.

I have tried the following:

mergeddata<-merge(dataframe, dataframe2, by="sites", all.y=TRUE)

But that only adds the new sites at the bottom with NA's for each value other than site. I want dataframe2 to be applied for each unique value under column ID, so that each ID has an occurrence of all sites. I'm not sure what the best way to go about this would be, any help is much appreciated!

r dplyr tidyr data-manipulation

share|improve this question

asked Mar 26 at 18:50

Elizabeth SmithElizabeth Smith

3955 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  can you change all.y to all and see what happens ?
  
  – Mike
  Mar 26 at 19:02
  

  
  
  
  

add a comment | 

0

I am trying to insert new rows of data based on unique values of a column in my original data set. I have the following dummy data set:

sites<-c("10","10","11","11","12","12")
ID<-c("A","A","B","B","C","D")
value<-c("4","6","5","2","7","8")
dataframe<-data.frame(sites, ID, value)

sites<-c("10","10","11","11","12","12","13","14","15")
dataframe2<-data.frame(sites)

Producing:

 sites ID value
 10 A 4
 10 A 6
 11 B 5
 11 B 2
 12 C 7
 12 D 8

 sites
 10
 10
 11
 11
 12
 12
 13
 14
 15

For each unique value in column ID, I would like each site number from the second data frame applied, and when there is no value I would like it to print 0.

So for example, ID A would have all sites from site2 listed and when there is no value (ie for site 11, 12, 13,14) I would like it to list 0 for value.

I have tried the following:

mergeddata<-merge(dataframe, dataframe2, by="sites", all.y=TRUE)

But that only adds the new sites at the bottom with NA's for each value other than site. I want dataframe2 to be applied for each unique value under column ID, so that each ID has an occurrence of all sites. I'm not sure what the best way to go about this would be, any help is much appreciated!

r dplyr tidyr data-manipulation

share|improve this question

asked Mar 26 at 18:50

Elizabeth SmithElizabeth Smith

3955 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  can you change all.y to all and see what happens ?
  
  – Mike
  Mar 26 at 19:02
  

  
  
  
  

add a comment | 

I am trying to insert new rows of data based on unique values of a column in my original data set. I have the following dummy data set:

sites<-c("10","10","11","11","12","12")
ID<-c("A","A","B","B","C","D")
value<-c("4","6","5","2","7","8")
dataframe<-data.frame(sites, ID, value)

sites<-c("10","10","11","11","12","12","13","14","15")
dataframe2<-data.frame(sites)

Producing:

 sites ID value
 10 A 4
 10 A 6
 11 B 5
 11 B 2
 12 C 7
 12 D 8

 sites
 10
 10
 11
 11
 12
 12
 13
 14
 15

For each unique value in column ID, I would like each site number from the second data frame applied, and when there is no value I would like it to print 0.

So for example, ID A would have all sites from site2 listed and when there is no value (ie for site 11, 12, 13,14) I would like it to list 0 for value.

I have tried the following:

mergeddata<-merge(dataframe, dataframe2, by="sites", all.y=TRUE)

But that only adds the new sites at the bottom with NA's for each value other than site. I want dataframe2 to be applied for each unique value under column ID, so that each ID has an occurrence of all sites. I'm not sure what the best way to go about this would be, any help is much appreciated!

r dplyr tidyr data-manipulation

share|improve this question

asked Mar 26 at 18:50

Elizabeth SmithElizabeth Smith

3955 bronze badges

sites<-c("10","10","11","11","12","12")
ID<-c("A","A","B","B","C","D")
value<-c("4","6","5","2","7","8")
dataframe<-data.frame(sites, ID, value)

sites<-c("10","10","11","11","12","12","13","14","15")
dataframe2<-data.frame(sites)

 sites ID value
 10 A 4
 10 A 6
 11 B 5
 11 B 2
 12 C 7
 12 D 8

 sites
 10
 10
 11
 11
 12
 12
 13
 14
 15

mergeddata<-merge(dataframe, dataframe2, by="sites", all.y=TRUE)

share|improve this question

asked Mar 26 at 18:50

Elizabeth SmithElizabeth Smith

3955 bronze badges

share|improve this question

asked Mar 26 at 18:50

Elizabeth SmithElizabeth Smith

3955 bronze badges

asked Mar 26 at 18:50

Elizabeth SmithElizabeth Smith

3955 bronze badges

asked Mar 26 at 18:50

Elizabeth SmithElizabeth Smith

3955 bronze badges

1 Answer
1

active

oldest

votes

2

This could be a job for complete() from package tidyr. You can group your first dataset by ID and then use complete() to add rows for the site values from dataframe2 within each group.

This results in having at least one row for each site in each ID. I use the fill argument to add the 0 to value for the new rows (after converting value to numeric).

library(dplyr)
library(tidyr)

dataframe$value = as.numeric( as.character(dataframe$value) )

dataframe %>%
 group_by(ID) %>%
 complete(sites = dataframe2$sites, fill = list(value = 0) )

# A tibble: 26 x 3
# Groups: ID [4]
 ID sites value
 <fct> <chr> <dbl>
 1 A 10 4
 2 A 10 6
 3 A 11 0
 4 A 12 0
 5 A 13 0
 6 A 14 0
 7 A 15 0
 8 B 10 0
 9 B 11 5
10 B 11 2
# ... with 16 more rows
Warning message:
Column `sites` joining factors with different levels, coercing to character vector 

The warning message has to do with site being a factor in the two datasets, which complete() deals with by converting the two columns to characters instead.

share|improve this answer

answered Mar 26 at 19:03

aosmithaosmith

23.1k44 gold badges4848 silver badges7676 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  This is what I'm looking for! Many thanks. I've run your code, but instead of zeros receive NA's. I fixed this by using dataframe[is.na(dataframe)] <- 0
  
  – Elizabeth Smith
  Mar 26 at 19:18
  

  
  
  
  

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

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55364344%2fhow-to-merge-data-to-apply-to-all-unique-conditions-of-a-column-in-second-data-s%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

2

This could be a job for complete() from package tidyr. You can group your first dataset by ID and then use complete() to add rows for the site values from dataframe2 within each group.

This results in having at least one row for each site in each ID. I use the fill argument to add the 0 to value for the new rows (after converting value to numeric).

library(dplyr)
library(tidyr)

dataframe$value = as.numeric( as.character(dataframe$value) )

dataframe %>%
 group_by(ID) %>%
 complete(sites = dataframe2$sites, fill = list(value = 0) )

# A tibble: 26 x 3
# Groups: ID [4]
 ID sites value
 <fct> <chr> <dbl>
 1 A 10 4
 2 A 10 6
 3 A 11 0
 4 A 12 0
 5 A 13 0
 6 A 14 0
 7 A 15 0
 8 B 10 0
 9 B 11 5
10 B 11 2
# ... with 16 more rows
Warning message:
Column `sites` joining factors with different levels, coercing to character vector 

The warning message has to do with site being a factor in the two datasets, which complete() deals with by converting the two columns to characters instead.

share|improve this answer

answered Mar 26 at 19:03

aosmithaosmith

23.1k44 gold badges4848 silver badges7676 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  This is what I'm looking for! Many thanks. I've run your code, but instead of zeros receive NA's. I fixed this by using dataframe[is.na(dataframe)] <- 0
  
  – Elizabeth Smith
  Mar 26 at 19:18
  

  
  
  
  

add a comment | 

2

This could be a job for complete() from package tidyr. You can group your first dataset by ID and then use complete() to add rows for the site values from dataframe2 within each group.

This results in having at least one row for each site in each ID. I use the fill argument to add the 0 to value for the new rows (after converting value to numeric).

library(dplyr)
library(tidyr)

dataframe$value = as.numeric( as.character(dataframe$value) )

dataframe %>%
 group_by(ID) %>%
 complete(sites = dataframe2$sites, fill = list(value = 0) )

# A tibble: 26 x 3
# Groups: ID [4]
 ID sites value
 <fct> <chr> <dbl>
 1 A 10 4
 2 A 10 6
 3 A 11 0
 4 A 12 0
 5 A 13 0
 6 A 14 0
 7 A 15 0
 8 B 10 0
 9 B 11 5
10 B 11 2
# ... with 16 more rows
Warning message:
Column `sites` joining factors with different levels, coercing to character vector 

The warning message has to do with site being a factor in the two datasets, which complete() deals with by converting the two columns to characters instead.

share|improve this answer

answered Mar 26 at 19:03

aosmithaosmith

23.1k44 gold badges4848 silver badges7676 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  This is what I'm looking for! Many thanks. I've run your code, but instead of zeros receive NA's. I fixed this by using dataframe[is.na(dataframe)] <- 0
  
  – Elizabeth Smith
  Mar 26 at 19:18
  

  
  
  
  

add a comment | 

This could be a job for complete() from package tidyr. You can group your first dataset by ID and then use complete() to add rows for the site values from dataframe2 within each group.

This results in having at least one row for each site in each ID. I use the fill argument to add the 0 to value for the new rows (after converting value to numeric).

library(dplyr)
library(tidyr)

dataframe$value = as.numeric( as.character(dataframe$value) )

dataframe %>%
 group_by(ID) %>%
 complete(sites = dataframe2$sites, fill = list(value = 0) )

# A tibble: 26 x 3
# Groups: ID [4]
 ID sites value
 <fct> <chr> <dbl>
 1 A 10 4
 2 A 10 6
 3 A 11 0
 4 A 12 0
 5 A 13 0
 6 A 14 0
 7 A 15 0
 8 B 10 0
 9 B 11 5
10 B 11 2
# ... with 16 more rows
Warning message:
Column `sites` joining factors with different levels, coercing to character vector 

The warning message has to do with site being a factor in the two datasets, which complete() deals with by converting the two columns to characters instead.

share|improve this answer

answered Mar 26 at 19:03

aosmithaosmith

23.1k44 gold badges4848 silver badges7676 bronze badges

library(dplyr)
library(tidyr)

dataframe$value = as.numeric( as.character(dataframe$value) )

dataframe %>%
 group_by(ID) %>%
 complete(sites = dataframe2$sites, fill = list(value = 0) )

# A tibble: 26 x 3
# Groups: ID [4]
 ID sites value
 <fct> <chr> <dbl>
 1 A 10 4
 2 A 10 6
 3 A 11 0
 4 A 12 0
 5 A 13 0
 6 A 14 0
 7 A 15 0
 8 B 10 0
 9 B 11 5
10 B 11 2
# ... with 16 more rows
Warning message:
Column `sites` joining factors with different levels, coercing to character vector 

share|improve this answer

answered Mar 26 at 19:03

aosmithaosmith

23.1k44 gold badges4848 silver badges7676 bronze badges

answered Mar 26 at 19:03

aosmithaosmith

23.1k44 gold badges4848 silver badges7676 bronze badges

answered Mar 26 at 19:03

aosmithaosmith

23.1k44 gold badges4848 silver badges7676 bronze badges