thr3ads.net - R help - [R] how to turn column into column names and fill it with values [Sep 2020]

If this information is useful, please help other people find it:
Share via:

Ana Marija

2020-Sep-29 16:18 UTC

[R] how to turn column into column names and fill it with values

Hello,

I have a data frame like this:
> head(mc)      FID  IID   PLATE
1 fam0110 G110 4RWG569
2 fam0113 G113  cherry
3 fam0114 G114  cherry
4 fam0117 G117 4RWG569
5 fam0118 G118 5XAV049
6 fam0119 G119  cherry
...> dim(mc)
[1] 1625    4> length(unique(mc$PLATE))[1] 34

I am trying to make a new data frame which would look like this:
      FID  IID   PLATE   4RWG569  cherry 5XAV049 ...
1 fam0110 G110 4RWG569  2  1  1
2 fam0113 G113  cherry   1  2  1
3 fam0114 G114  cherry   1  2  1
4 fam0117 G117 4RWG569  2  1  1
5 fam0118 G118 5XAV049   2  1  1
6 fam0119 G119  cherry   1  2  1
...

so the new data frame would have an additional 34 columns (for every
unique mc$PLATE) and if in the row of PLATE column the value is ==to
that column name I would have 2 otherwise 1

I tried to do this with:

library(reshape2)>  m2=dcast(mc, IID ~ PLATE)Using PLATE as value column: use value.var to override.

Please advise,
Ana

Bert Gunter

2020-Sep-29 16:46 UTC

head link

[R] how to turn column into column names and fill it with values

I am not sure reshape2 is appropriate for this task, but, assuming I
understand correctly, it's quite easy without it. The following is one way,
which probably can be done more elegantly and efficiently, but I think it
does what you want.

"dat" is your example data frame, in which the columns were read in
with
"stringsAsFactors" = FALSE (this is important!)

dat <- cbind(dat, matrix(0,ncol = 3)) ## change 3 to 34 for your full data
names(dat) <- c(names(dat)[1:3], unique(dat$PLATE))
for(i in 4:ncol(dat)) dat[,i] <- 1 + (names(dat)[i]== dat$PLATE)
dat

Result:

      FID  IID   PLATE 4RWG569 cherry 5XAV049
1 fam0110 G110 4RWG569       2      1       1
2 fam0113 G113  cherry       1      2       1
3 fam0114 G114  cherry       1      2       1
4 fam0117 G117 4RWG569       2      1       1
5 fam0118 G118 5XAV049       1      1       2
6 fam0119 G119  cherry       1      2       1


Bert Gunter

"The trouble with having an open mind is that people keep coming along and
sticking things into it."
-- Opus (aka Berkeley Breathed in his "Bloom County" comic strip )


On Tue, Sep 29, 2020 at 9:19 AM Ana Marija <sokovic.anamarija at
gmail.com>
wrote:
> Hello,
>
> I have a data frame like this:
>
> > head(mc)
>       FID  IID   PLATE
> 1 fam0110 G110 4RWG569
> 2 fam0113 G113  cherry
> 3 fam0114 G114  cherry
> 4 fam0117 G117 4RWG569
> 5 fam0118 G118 5XAV049
> 6 fam0119 G119  cherry
> ...
> > dim(mc)
> [1] 1625    4
> > length(unique(mc$PLATE))
> [1] 34
>
> I am trying to make a new data frame which would look like this:
>       FID  IID   PLATE   4RWG569  cherry 5XAV049 ...
> 1 fam0110 G110 4RWG569  2  1  1
> 2 fam0113 G113  cherry   1  2  1
> 3 fam0114 G114  cherry   1  2  1
> 4 fam0117 G117 4RWG569  2  1  1
> 5 fam0118 G118 5XAV049   2  1  1
> 6 fam0119 G119  cherry   1  2  1
> ...
>
> so the new data frame would have an additional 34 columns (for every
> unique mc$PLATE) and if in the row of PLATE column the value is ==to
> that column name I would have 2 otherwise 1
>
> I tried to do this with:
>
> library(reshape2)
> >  m2=dcast(mc, IID ~ PLATE)
> Using PLATE as value column: use value.var to override.
>
> Please advise,
> Ana
>
> ______________________________________________
> R-help at r-project.org mailing list -- To UNSUBSCRIBE and more, see
> 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]]

Ana Marija

2020-Sep-29 17:08 UTC

head link

[R] how to turn column into column names and fill it with values

HI Bert,

thank you for getting back to me.
I tried this:
> dat <- cbind(mc, matrix(0,ncol = 34))
> head(dat)      FID  IID   PLATE 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22
1 fam0110 G110 4RWG569 0 0 0 0 0 0 0 0 0  0  0  0  0  0  0  0  0  0  0  0  0  0
2 fam0113 G113  cherry 0 0 0 0 0 0 0 0 0  0  0  0  0  0  0  0  0  0  0  0  0  0
3 fam0114 G114  cherry 0 0 0 0 0 0 0 0 0  0  0  0  0  0  0  0  0  0  0  0  0  0
4 fam0117 G117 4RWG569 0 0 0 0 0 0 0 0 0  0  0  0  0  0  0  0  0  0  0  0  0  0
5 fam0118 G118 5XAV049 0 0 0 0 0 0 0 0 0  0  0  0  0  0  0  0  0  0  0  0  0  0
6 fam0119 G119  cherry 0 0 0 0 0 0 0 0 0  0  0  0  0  0  0  0  0  0  0  0  0  0
  23 24 25 26 27 28 29 30 31 32 33 34
1  0  0  0  0  0  0  0  0  0  0  0  0
2  0  0  0  0  0  0  0  0  0  0  0  0
3  0  0  0  0  0  0  0  0  0  0  0  0
4  0  0  0  0  0  0  0  0  0  0  0  0
5  0  0  0  0  0  0  0  0  0  0  0  0
6  0  0  0  0  0  0  0  0  0  0  0  0> names(dat) <- c(names(dat)[1:34], unique(dat$PLATE))Error in names(dat) <- c(names(dat)[1:34], unique(dat$PLATE)) :
  'names' attribute [68] must be the same length as the vector [37]

so names should include FID,IID,PLATE  plus unique dat$PLATE
how do I fix that so the code works?

Also I tried a bit on my own:
> head(mc)      FID  IID   PLATE
1 fam0110 G110 4RWG569
2 fam0113 G113  cherry
3 fam0114 G114  cherry
4 fam0117 G117 4RWG569
5 fam0118 G118 5XAV049
6 fam0119 G119  cherry
...

m2=tapply(mc$IID, list(FID=mc$FID, PLATE=mc$PLATE), mean)
m2=as.data.frame(m2)
library(data.table)
m3=setDT(m2, keep.rownames = TRUE)[]
colnames(m3)[1] <- "FID"
mt=merge(mc,m3,by="FID")
> head(mt)      FID  IID   PLATE 0VXC556 1CNF297 1CWO500 1DXJ626 1LTX827 1SHK635 1TNP840
1 fam0110 G110 4RWG569      NA      NA      NA      NA      NA      NA      NA
2 fam0113 G113  cherry      NA      NA      NA      NA      NA      NA      NA
3 fam0114 G114  cherry      NA      NA      NA      NA      NA      NA      NA
4 fam0117 G117 4RWG569      NA      NA      NA      NA      NA      NA      NA
5 fam0118 G118 5XAV049      NA      NA      NA      NA      NA      NA      NA
6 fam0119 G119  cherry      NA      NA      NA      NA      NA      NA      NA
  1URP242 2BKX529 2PAG415 3DEF425 3ECO791 3FQM386 3KYJ479 3XHK903 4RWG569
1      NA      NA      NA      NA      NA      NA      NA      NA      NA
2      NA      NA      NA      NA      NA      NA      NA      NA      NA
3      NA      NA      NA      NA      NA      NA      NA      NA      NA
4      NA      NA      NA      NA      NA      NA      NA      NA      NA
5      NA      NA      NA      NA      NA      NA      NA      NA      NA
6      NA      NA      NA      NA      NA      NA      NA      NA      NA
...

so this gives me the correct columns. Now is the question of how to
replace NA with 2 id column name matches the rownname in PLATE column
with 2 otherwise it is 1.

Cheers,
Ana

On Tue, Sep 29, 2020 at 11:46 AM Bert Gunter <bgunter.4567 at gmail.com>
wrote:>
> I am not sure reshape2 is appropriate for this task, but, assuming I
understand correctly, it's quite easy without it. The following is one way,
which probably can be done more elegantly and efficiently, but I think it does
what you want.
>
> "dat" is your example data frame, in which the columns were read
in with "stringsAsFactors" = FALSE (this is important!)
>
> dat <- cbind(dat, matrix(0,ncol = 3)) ## change 3 to 34 for your full
data
> names(dat) <- c(names(dat)[1:3], unique(dat$PLATE))
> for(i in 4:ncol(dat)) dat[,i] <- 1 + (names(dat)[i]== dat$PLATE)
> dat
>
> Result:
>
>       FID  IID   PLATE 4RWG569 cherry 5XAV049
> 1 fam0110 G110 4RWG569       2      1       1
> 2 fam0113 G113  cherry       1      2       1
> 3 fam0114 G114  cherry       1      2       1
> 4 fam0117 G117 4RWG569       2      1       1
> 5 fam0118 G118 5XAV049       1      1       2
> 6 fam0119 G119  cherry       1      2       1
>
>
> Bert Gunter
>
> "The trouble with having an open mind is that people keep coming along
and sticking things into it."
> -- Opus (aka Berkeley Breathed in his "Bloom County" comic strip
)
>
>
> On Tue, Sep 29, 2020 at 9:19 AM Ana Marija <sokovic.anamarija at
gmail.com> wrote:
>>
>> Hello,
>>
>> I have a data frame like this:
>>
>> > head(mc)
>>       FID  IID   PLATE
>> 1 fam0110 G110 4RWG569
>> 2 fam0113 G113  cherry
>> 3 fam0114 G114  cherry
>> 4 fam0117 G117 4RWG569
>> 5 fam0118 G118 5XAV049
>> 6 fam0119 G119  cherry
>> ...
>> > dim(mc)
>> [1] 1625    4
>> > length(unique(mc$PLATE))
>> [1] 34
>>
>> I am trying to make a new data frame which would look like this:
>>       FID  IID   PLATE   4RWG569  cherry 5XAV049 ...
>> 1 fam0110 G110 4RWG569  2  1  1
>> 2 fam0113 G113  cherry   1  2  1
>> 3 fam0114 G114  cherry   1  2  1
>> 4 fam0117 G117 4RWG569  2  1  1
>> 5 fam0118 G118 5XAV049   2  1  1
>> 6 fam0119 G119  cherry   1  2  1
>> ...
>>
>> so the new data frame would have an additional 34 columns (for every
>> unique mc$PLATE) and if in the row of PLATE column the value is ==to
>> that column name I would have 2 otherwise 1
>>
>> I tried to do this with:
>>
>> library(reshape2)
>> >  m2=dcast(mc, IID ~ PLATE)
>> Using PLATE as value column: use value.var to override.
>>
>> Please advise,
>> Ana
>>
>> ______________________________________________
>> R-help at r-project.org mailing list -- To UNSUBSCRIBE and more, see
>> 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.

Rui Barradas

2020-Sep-29 18:19 UTC

head link

[R] how to turn column into column names and fill it with values

Hello,

Something like this?

mc <- read.table(text = "
       FID  IID   PLATE
1 fam0110 G110 4RWG569
2 fam0113 G113  cherry
3 fam0114 G114  cherry
4 fam0117 G117 4RWG569
5 fam0118 G118 5XAV049
6 fam0119 G119  cherry
", header = TRUE)



library(dplyr)
library(tidyr)

mc %>%
   group_by(PLATE) %>%
   mutate(counts = n()) %>%
   pivot_wider(
     id_cols = c("FID", "IID"),
     names_from = "PLATE",
     values_from = counts,
     values_fill = list(counts = 0)
   )


Hope this helps,

Rui Barradas

?s 17:18 de 29/09/20, Ana Marija escreveu:> Hello,
> 
> I have a data frame like this:
> 
>> head(mc)
>        FID  IID   PLATE
> 1 fam0110 G110 4RWG569
> 2 fam0113 G113  cherry
> 3 fam0114 G114  cherry
> 4 fam0117 G117 4RWG569
> 5 fam0118 G118 5XAV049
> 6 fam0119 G119  cherry
> ...
>> dim(mc)
> [1] 1625    4
>> length(unique(mc$PLATE))
> [1] 34
> 
> I am trying to make a new data frame which would look like this:
>        FID  IID   PLATE   4RWG569  cherry 5XAV049 ...
> 1 fam0110 G110 4RWG569  2  1  1
> 2 fam0113 G113  cherry   1  2  1
> 3 fam0114 G114  cherry   1  2  1
> 4 fam0117 G117 4RWG569  2  1  1
> 5 fam0118 G118 5XAV049   2  1  1
> 6 fam0119 G119  cherry   1  2  1
> ...
> 
> so the new data frame would have an additional 34 columns (for every
> unique mc$PLATE) and if in the row of PLATE column the value is ==to
> that column name I would have 2 otherwise 1
> 
> I tried to do this with:
> 
> library(reshape2)
>>   m2=dcast(mc, IID ~ PLATE)
> Using PLATE as value column: use value.var to override.
> 
> Please advise,
> Ana
> 
> ______________________________________________
> R-help at r-project.org mailing list -- To UNSUBSCRIBE and more, see
> 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.
>

Rui Barradas

2020-Sep-29 18:31 UTC

head link

[R] how to turn column into column names and fill it with values

Hello,

Sorry, I didn't understand that 1 and 2 are the final values, I thought 
they would be counts of PLATE. I have changed the auxiliary column 
'counts' to 'flag'.


mc %>%
   group_by(PLATE) %>%
   mutate(flag = 2) %>%
   pivot_wider(
     id_cols = c("FID", "IID"),
     names_from = "PLATE",
     values_from = flag,
     values_fill = list(flag = 1)
   )


Hope this helps,

Rui Barradas

?s 19:19 de 29/09/20, Rui Barradas escreveu:> Hello,
> 
> Something like this?
> 
> mc <- read.table(text = "
>  ????? FID? IID?? PLATE
> 1 fam0110 G110 4RWG569
> 2 fam0113 G113? cherry
> 3 fam0114 G114? cherry
> 4 fam0117 G117 4RWG569
> 5 fam0118 G118 5XAV049
> 6 fam0119 G119? cherry
> ", header = TRUE)
> 
> 
> 
> library(dplyr)
> library(tidyr)
> 
> mc %>%
>  ? group_by(PLATE) %>%
>  ? mutate(counts = n()) %>%
>  ? pivot_wider(
>  ??? id_cols = c("FID", "IID"),
>  ??? names_from = "PLATE",
>  ??? values_from = counts,
>  ??? values_fill = list(counts = 0)
>  ? )
> 
> 
> Hope this helps,
> 
> Rui Barradas
> 
> ?s 17:18 de 29/09/20, Ana Marija escreveu:
>> Hello,
>>
>> I have a data frame like this:
>>
>>> head(mc)
>> ?????? FID? IID?? PLATE
>> 1 fam0110 G110 4RWG569
>> 2 fam0113 G113? cherry
>> 3 fam0114 G114? cherry
>> 4 fam0117 G117 4RWG569
>> 5 fam0118 G118 5XAV049
>> 6 fam0119 G119? cherry
>> ...
>>> dim(mc)
>> [1] 1625??? 4
>>> length(unique(mc$PLATE))
>> [1] 34
>>
>> I am trying to make a new data frame which would look like this:
>> ?????? FID? IID?? PLATE?? 4RWG569? cherry 5XAV049 ...
>> 1 fam0110 G110 4RWG569? 2? 1? 1
>> 2 fam0113 G113? cherry?? 1? 2? 1
>> 3 fam0114 G114? cherry?? 1? 2? 1
>> 4 fam0117 G117 4RWG569? 2? 1? 1
>> 5 fam0118 G118 5XAV049?? 2? 1? 1
>> 6 fam0119 G119? cherry?? 1? 2? 1
>> ...
>>
>> so the new data frame would have an additional 34 columns (for every
>> unique mc$PLATE) and if in the row of PLATE column the value is ==to
>> that column name I would have 2 otherwise 1
>>
>> I tried to do this with:
>>
>> library(reshape2)
>>> ? m2=dcast(mc, IID ~ PLATE)
>> Using PLATE as value column: use value.var to override.
>>
>> Please advise,
>> Ana
>>
>> ______________________________________________
>> R-help at r-project.org mailing list -- To UNSUBSCRIBE and more, see
>> 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.
>>
> 
> ______________________________________________
> R-help at r-project.org mailing list -- To UNSUBSCRIBE and more, see
> 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.

Bert Gunter

2020-Sep-29 19:18 UTC

head link

[R] how to turn column into column names and fill it with values

A simpler, cleaner, and maybe faster approach is to use outer():

nm <- unique(dat$PLATE)
dat <- cbind(dat, 1+outer(dat$PLATE,nm,  "=="))
names(dat)[-(1:3)] <- nm

Bert

Bert Gunter

"The trouble with having an open mind is that people keep coming along and
sticking things into it."
-- Opus (aka Berkeley Breathed in his "Bloom County" comic strip )


On Tue, Sep 29, 2020 at 11:20 AM Rui Barradas <ruipbarradas at sapo.pt>
wrote:
> Hello,
>
> Something like this?
>
> mc <- read.table(text = "
>        FID  IID   PLATE
> 1 fam0110 G110 4RWG569
> 2 fam0113 G113  cherry
> 3 fam0114 G114  cherry
> 4 fam0117 G117 4RWG569
> 5 fam0118 G118 5XAV049
> 6 fam0119 G119  cherry
> ", header = TRUE)
>
>
>
> library(dplyr)
> library(tidyr)
>
> mc %>%
>    group_by(PLATE) %>%
>    mutate(counts = n()) %>%
>    pivot_wider(
>      id_cols = c("FID", "IID"),
>      names_from = "PLATE",
>      values_from = counts,
>      values_fill = list(counts = 0)
>    )
>
>
> Hope this helps,
>
> Rui Barradas
>
> ?s 17:18 de 29/09/20, Ana Marija escreveu:
> > Hello,
> >
> > I have a data frame like this:
> >
> >> head(mc)
> >        FID  IID   PLATE
> > 1 fam0110 G110 4RWG569
> > 2 fam0113 G113  cherry
> > 3 fam0114 G114  cherry
> > 4 fam0117 G117 4RWG569
> > 5 fam0118 G118 5XAV049
> > 6 fam0119 G119  cherry
> > ...
> >> dim(mc)
> > [1] 1625    4
> >> length(unique(mc$PLATE))
> > [1] 34
> >
> > I am trying to make a new data frame which would look like this:
> >        FID  IID   PLATE   4RWG569  cherry 5XAV049 ...
> > 1 fam0110 G110 4RWG569  2  1  1
> > 2 fam0113 G113  cherry   1  2  1
> > 3 fam0114 G114  cherry   1  2  1
> > 4 fam0117 G117 4RWG569  2  1  1
> > 5 fam0118 G118 5XAV049   2  1  1
> > 6 fam0119 G119  cherry   1  2  1
> > ...
> >
> > so the new data frame would have an additional 34 columns (for every
> > unique mc$PLATE) and if in the row of PLATE column the value is ==to
> > that column name I would have 2 otherwise 1
> >
> > I tried to do this with:
> >
> > library(reshape2)
> >>   m2=dcast(mc, IID ~ PLATE)
> > Using PLATE as value column: use value.var to override.
> >
> > Please advise,
> > Ana
> >
> > ______________________________________________
> > R-help at r-project.org mailing list -- To UNSUBSCRIBE and more, see
> > 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.
> >
>
> ______________________________________________
> R-help at r-project.org mailing list -- To UNSUBSCRIBE and more, see
> 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 - Sep 2020 - how to turn column into column names and fill it with values

[R] how to turn column into column names and fill it with values

[R] how to turn column into column names and fill it with values

[R] how to turn column into column names and fill it with values

[R] how to turn column into column names and fill it with values

[R] how to turn column into column names and fill it with values

[R] how to turn column into column names and fill it with values