[R] Data reshaping with conditions

[sri vathsan]

Hi Jim,

Thanks for your time. But somehow this code did not help me to achieve my
expected output.
Problems: 1) x, y are coming as logical rather than values as I mentioned
in my post
               2) The values that I get for Max A and Max B not correct
               3) It looks like a pretty big data, but I just need to
concatenate the values with a comma, the final output will be a character
variable.

Regards,
Sri

On Thu, Apr 21, 2016 at 4:52 AM, Jim Lemon <drjimlemon at gmail.com> wrote:

> Hi sri,
> As your problem involves a few logical steps, I found it easier to
> approach it in a stepwise way. Perhaps there are more elegant ways to
> accomplish this.
>
> svdat<-read.table(text="Count id name type
> 117 335 sally A
> 19 335 sally A
> 167 335 sally B
> 18 340 susan A
> 56 340 susan A
> 22 340 susan B
> 53 340 susan B
> 135 351 lee A
> 114 351 lee A
> 84 351 lee A
> 80 351 lee A
> 19 351 lee A
> 8 351 lee A
> 21 351 lee A
> 88 351 lee B
> 111 351 lee B
> 46 351 lee B
> 108 351 lee B",header=TRUE)
> # you can also do this with other reshape functions
> library(prettyR)
> svdatstr<-stretch_df(svdat,"id",c("Count","type"))
> count_ind<-grep("Count",names(svdatstr))
> type_ind<-grep("type",names(svdatstr))
> svdatstr$maxA<-NA
> svdatstr$maxB<-NA
> svdatstr$x<-NA
> svdatstr$y<-NA
> for(row in 1:nrow(svdatstr)) {
>  svdatstr[row,"maxA"]<-
>
> max(svdatstr[row,count_ind[as.logical(match(svdatstr[1,type_ind],"A",0))]])
>  svdatstr[row,"maxB"]<-
>
> max(svdatstr[row,count_ind[as.logical(match(svdatstr[1,type_ind],"B",0))]])
>  svdatstr[row,"x"]<-svdatstr[row,"maxA"] < svdatstr[row,"maxB"]
>  svdatstr[row,"y"]<-!svdatstr[row,"x"]
> }
> svdatstr
>
> You can then just extract the columns that you need.
>
> Jim
>
>
> On Wed, Apr 20, 2016 at 3:03 PM, sri vathsan <srivibish at gmail.com> wrote:
> > Dear All,
> >
> > I am trying to reshape the data with some conditions. A small part of the
> > data looks like below. Like this there will be more data with repeating
> ID.
> >
> > Count id name type
> > 117 335 sally A
> > 19 335 sally A
> > 167 335 sally B
> > 18 340 susan A
> > 56 340 susan A
> > 22 340 susan B
> > 53 340 susan B
> > 135 351 lee A
> > 114 351 lee A
> > 84 351 lee A
> > 80 351 lee A
> > 19 351 lee A
> > 8 351 lee A
> > 21 351 lee A
> > 88 351 lee B
> > 111 351 lee B
> > 46 351 lee B
> > 108 351 lee B
> >
> > >From the above data I am expecting an output like below.
> >
> > id name type count_of_B Max of count B     x               y
> > 335 sally B 167 167 117,19      NA
> > 340 susan B 22,53 53 18              56
> > 351 lee B 88,111,46,108  111 84,80,19,8,2   135,114
> >
> > Where, the column x and column y are:
> >
> > x = Count_A_less_than_max of (Count type B)
> > y = Count_A_higher_than_max of (Count type B).
> >
> > *1)* I tried with dplyr with the following code for the initial step to
> get
> > the values for each column.
> > *2)*  I thought to transpose the columns which has the unique ID alone.
> >
> > I tried with the following code and I am struck with the intial step
> > itself. The code is executed but higher and lower value of A is not
> coming.
> >
> > Expected_output= data %>%
> >   group_by(id, Type) %>%
> >   mutate(Count_of_B = paste(unlist(count[Type=="B"]), collapse = ","))%>%
> >   mutate(Max_of_count_B = ifelse(Type == "B", max(count[Type ==
> > "B"]),max(count[Type == "A"]))) %>%
> >   mutate(count_type_A_lesser = ifelse
> > (Type=="B",(paste(unlist(count[Type=="A"]) < Max_of_count_B[Type=="B"],
> > collapse = ",")), "NA"))%>%
> >   mutate(count_type_A_higher =
> > ifelse(Type=="B",(paste(unlist(count[Type=="A"]) >
> > Max_of_count_B[Type=="B"], collapse = ",")), "NA"))
> >
> > I hope I make my point clear. Please bare with the code, as I am new to
> > this.
> >
> > Regards,
> > sri
> >
> >         [[alternative HTML version deleted]]
> >
> > ______________________________________________
> > 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.
>



-- 

Regards,
Srivathsan.K
Phone : 9600165206

	[[alternative HTML version deleted]]