HI,
I have the following list:
crosspries
$crosspries[[1]]
Product Year_Month prod1
A 201208 1
B 201208 2
C 201208 1
$crosspries[[2]]
Product Year_Month prod1 prod2
A 201209 1 1
B 201209 2 2
D 201209 1 1
F 201209 2 1
I want to get the following dataframe:
Product Year_Month prod1 prod2
A 201208 1 NA
B 201208 2 NA
C 201208 1 NA
D 201208 NA NA
F 201208 NA NA
A 201209 1 1
B 201209 2 2
C 201209 NA NA
D 201209 1 1
F 201209 2 1
How can I get it in r?
Thanks.
Kind regards,
Tammy
[[alternative HTML version deleted]]
Tammy,
# use the function dput() to provide code for us to easily recreate your
example data
crosspries <- list(structure(list(Product = c("A", "B",
"C"), Year_Month c(201208L,
201208L, 201208L), prod1 = c(1L, 2L, 1L)), .Names = c("Product",
"Year_Month", "prod1"), class = "data.frame",
row.names = c(NA,
-3L)), structure(list(Product = c("A", "B", "D",
"F"), Year_Month c(201209L,
201209L, 201209L, 201209L), prod1 = c(1L, 2L, 1L, 2L), prod2 = c(1L,
2L, 1L, 1L)), .Names = c("Product", "Year_Month",
"prod1", "prod2"
), class = "data.frame", row.names = c(NA, -4L)))
# merge the two data frames
df1 <- merge(crosspries[[1]], crosspries[[2]], all=TRUE)
# create a new data frame with all possible combinations of Product and
Year_Month
template <- expand.grid(Product=unique(df1$Product),
Year_Month=unique(df1$Year_Month))
# merge the data with the template
df2 <- merge(df1, template, all=TRUE)
df2
Jean
On Thu, Jan 24, 2013 at 5:25 AM, Tammy Ma <metal_licaling@live.com> wrote:
>
> HI,
>
>
> I have the following list:
>
> crosspries
> $crosspries[[1]]
> Product Year_Month prod1
> A 201208 1
> B 201208 2
> C 201208 1
>
>
> $crosspries[[2]]
> Product Year_Month prod1 prod2
> A 201209 1 1
> B 201209 2 2
> D 201209 1 1
> F 201209 2 1
>
> I want to get the following dataframe:
>
>
> Product Year_Month prod1 prod2
> A 201208 1 NA
> B 201208 2 NA
> C 201208 1 NA
> D 201208 NA NA
> F 201208 NA NA
> A 201209 1 1
> B 201209 2 2
> C 201209 NA NA
> D 201209 1 1
> F 201209 2 1
>
>
>
> How can I get it in r?
>
>
> Thanks.
>
> Kind regards,
> Tammy
>
>
>
>
>
>
>
>
>
> [[alternative HTML version deleted]]
>
> ______________________________________________
> R-help@r-project.org mailing list
> 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.
>
[[alternative HTML version deleted]]
Hi,
Another way might be:
df1 <- merge(crosspries[[1]], crosspries[[2]], all=TRUE)
template1<-read.table(text=outer(unique(df1[,1]),unique(df1[,2]),FUN=paste),sep="",stringsAsFactors=F)
?res<-merge(df1,template1,by.x=c("Product","Year_Month"),by.y=c("V1","V2"),all=TRUE)
res1<-do.call(rbind,split(res,res$Year_Month))
?row.names(res1)<-1:nrow(res1)
?res1
#?? Product Year_Month prod1 prod2
#1??????? A???? 201208???? 1??? NA
#2??????? B???? 201208???? 2??? NA
#3??????? C???? 201208???? 1??? NA
#4??????? D???? 201208??? NA??? NA
#5??????? F???? 201208??? NA??? NA
#6??????? A???? 201209???? 1???? 1
#7??????? B???? 201209???? 2???? 2
#8??????? C???? 201209??? NA??? NA
#9??????? D???? 201209???? 1???? 1
#10?????? F???? 201209???? 2???? 1
A.K.
----- Original Message -----
From: "Adams, Jean" <jvadams at usgs.gov>
To: Tammy Ma <metal_licaling at live.com>
Cc: "r-help at r-project.org" <r-help at r-project.org>
Sent: Thursday, January 24, 2013 8:33 AM
Subject: Re: [R] how to combine unequal rows and columns in R
Tammy,
# use the function dput() to provide code for us to easily recreate your
example data
crosspries <- list(structure(list(Product = c("A", "B",
"C"), Year_Month c(201208L,
201208L, 201208L), prod1 = c(1L, 2L, 1L)), .Names = c("Product",
"Year_Month", "prod1"), class = "data.frame",
row.names = c(NA,
-3L)), structure(list(Product = c("A", "B", "D",
"F"), Year_Month c(201209L,
201209L, 201209L, 201209L), prod1 = c(1L, 2L, 1L, 2L), prod2 = c(1L,
2L, 1L, 1L)), .Names = c("Product", "Year_Month",
"prod1", "prod2"
), class = "data.frame", row.names = c(NA, -4L)))
# merge the two data frames
df1 <- merge(crosspries[[1]], crosspries[[2]], all=TRUE)
# create a new data frame with all possible combinations of Product and
Year_Month
template <- expand.grid(Product=unique(df1$Product),
Year_Month=unique(df1$Year_Month))
# merge the data with the template
df2 <- merge(df1, template, all=TRUE)
df2
Jean
On Thu, Jan 24, 2013 at 5:25 AM, Tammy Ma <metal_licaling at live.com>
wrote:
>
> HI,
>
>
> I have the following list:
>
> crosspries
> $crosspries[[1]]
>? ? ? ? Product? ? ? ? Year_Month? prod1
>? ? ? ? ? A? ? ? ? ? ? ? ? ? 201208? ? ? ? 1
>? ? ? ? ? B? ? ? ? ? ? ? ? ? 201208? ? ? ? 2
>? ? ? ? ? C? ? ? ? ? ? ? ? ? 201208? ? ? ? 1
>
>
> $crosspries[[2]]
>? ? ? Product? ? ? Year_Month? ? prod1? prod2
>? ? ? ? A? ? ? ? ? ? ? 201209? ? ? ? 1? ? ? ? ? 1
>? ? ? ? B? ? ? ? ? ? ? 201209? ? ? ? 2? ? ? ? ? 2
>? ? ? ? D? ? ? ? ? ? ? 201209? ? ? ? ? 1? ? ? ? ? 1
>? ? ? ? F? ? ? ? ? ? ? 201209? ? ? ? ? 2? ? ? ? ? 1
>
> I want to get the following dataframe:
>
>
> Product? Year_Month? ? prod1? ? prod2
>? ? A? ? ? ? 201208? ? ? ? ? ? 1? ? ? ? ? NA
>? ? B? ? ? ? ? 201208? ? ? ? ? ? 2? ? ? ? ? NA
>? C? ? ? ? ? 201208? ? ? ? ? ? ? 1? ? ? ? ? NA
>? D? ? ? ? ? 201208? ? ? ? ? ? NA? ? ? ? ? NA
>? F? ? ? ? ? ? 201208? ? ? ? ? ? NA? ? ? ? ? NA
>? A? ? ? ? 201209? ? ? ? ? ? 1? ? ? ? ? ? 1
>? B? ? ? ? 201209? ? ? ? ? ? 2? ? ? ? ? ? 2
>? C? ? ? ? 201209? ? ? ? ? ? NA? ? ? ? ? NA
>? D? ? ? ? 201209? ? ? ? ? ? 1? ? ? ? ? ? ? 1
>? F? ? ? ? 201209? ? ? ? ? ? 2? ? ? ? ? ? ? 1
>
>
>
> How can I get it in r?
>
>
> Thanks.
>
> Kind regards,
> Tammy
>
>
>
>
>
>
>
>
>
>? ? ? ? [[alternative HTML version deleted]]
>
> ______________________________________________
> R-help at r-project.org mailing list
> 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.
>
??? [[alternative HTML version deleted]]
______________________________________________
R-help at r-project.org mailing list
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.
HI,
If you use ?join(), it will preserve the order of the first dataframe.
library(plyr)
df3<-rbind.fill(crosspries[[1]],crosspries[[2]])
template2<-read.table(text=outer(unique(df3[,1]),unique(df3[,2]),FUN=paste),sep="",stringsAsFactors=F)
names(template2)<-names(df3[1:2])
res1<-join(template2,df3,by=c("Product","Year_Month"),type="full")
?res1
#?? Product Year_Month prod1 prod2
#1??????? A???? 201208???? 1??? NA
#2??????? B???? 201208???? 2??? NA
#3??????? C???? 201208???? 1??? NA
#4??????? D???? 201208??? NA??? NA
#5??????? F???? 201208??? NA??? NA
#6??????? A???? 201209???? 1???? 1
#7??????? B???? 201209???? 2???? 2
#8??????? C???? 201209??? NA??? NA
#9??????? D???? 201209???? 1???? 1
#10?????? F???? 201209???? 2???? 1
A.K.
----- Original Message -----
From: Tammy Ma <metal_licaling at live.com>
To: "r-help at r-project.org" <r-help at r-project.org>
Cc:
Sent: Thursday, January 24, 2013 6:25 AM
Subject: [R] how to combine unequal rows and columns in R
HI,
I have the following list:
crosspries
$crosspries[[1]]
? ? ? Product? ? ? ? Year_Month? prod1
? ? ? ? ? A? ? ? ? ? ? ? ? ? 201208? ? ? ? 1
? ? ? ? ? B? ? ? ? ? ? ? ? ? 201208? ? ? ? 2
? ? ? ? ? C? ? ? ? ? ? ? ? ? 201208? ? ? ? 1
$crosspries[[2]]
? ? Product? ? ? Year_Month? ? prod1? prod2
? ? ? ? A? ? ? ? ? ? ? 201209? ? ? ? 1? ? ? ? ? 1
? ? ? ? B? ? ? ? ? ? ? 201209? ? ? ? 2? ? ? ? ? 2
? ? ? ? D? ? ? ? ? ? ? 201209? ? ? ? ? 1? ? ? ? ? 1
? ? ? ? F? ? ? ? ? ? ? 201209? ? ? ? ? 2? ? ? ? ? 1
I want to get the following dataframe:
Product? Year_Month? ? prod1? ? prod2
? A? ? ? ? 201208? ? ? ? ? ? 1? ? ? ? ? NA
? B? ? ? ? ? 201208? ? ? ? ? ? 2? ? ? ? ? NA
? C? ? ? ? ? 201208? ? ? ? ? ? ? 1? ? ? ? ? NA
? D? ? ? ? ? 201208? ? ? ? ? ? NA? ? ? ? ? NA
? F? ? ? ? ? ? 201208? ? ? ? ? ? NA? ? ? ? ? NA
? A? ? ? ? 201209? ? ? ? ? ? 1? ? ? ? ? ? 1
? B? ? ? ? 201209? ? ? ? ? ? 2? ? ? ? ? ? 2
? C? ? ? ? 201209? ? ? ? ? ? NA? ? ? ? ? NA
? D? ? ? ? 201209? ? ? ? ? ? 1? ? ? ? ? ? ? 1
? F? ? ? ? 201209? ? ? ? ? ? 2? ? ? ? ? ? ? 1
How can I get it in r?
Thanks.
Kind regards,
Tammy
??? ??? ??? ? ??? ??? ?
??? [[alternative HTML version deleted]]
______________________________________________
R-help at r-project.org mailing list
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.