CODEWITHSUNDEEP
r
nik
nik

Reputation: 2584

how to rearrange even columns based on odd columns which are ordered in one column

I have a data like this 

df<- structure(list(V1 = c(0.7, 2.083, 2.517, 2.667, 3.883, NA, NA, 
NA), V2 = c(1.4, 1.65, 2.1, 2.267, 3.017, 3.383, NA, NA), V3 = c(0.85, 
1.633, 2.117, 2.267, 3.567, 5.35, 9.7, 15.867), V4 = c(1.6, 1.9, 
2.117, 2.3, 9.717, 21.6, NA, NA)), .Names = c("V1", "V2", "V3", 
"V4"), class = "data.frame", row.names = c(NA, -8L))


#     V1    V2     V3     V4
#1 0.700 1.400  0.850  1.600
#2 2.083 1.650  1.633  1.900
#3 2.517 2.100  2.117  2.117
#4 2.667 2.267  2.267  2.300
#5 3.883 3.017  3.567  9.717
#6    NA 3.383  5.350 21.600
#7    NA    NA  9.700     NA
#8    NA    NA 15.867     NA

I first get the odd columns out 

odd <- seq(1, ncol(df), by = 2)

Then I make a new data frame from it like this 

mdf <- df[,odd]

then I sort and put them together in one column

newdf <- data.frame(Acol1= sort(unname(unlist(mdf))))

#     Acol1
#1   0.700
#2   0.850
#3   1.633
#4   2.083
#5   2.117
#6   2.267
#7   2.517
#8   2.667
#9   3.567
#10  3.883
#11  5.350
#12  9.700
#13 15.867

Now I want to put each element from the even columns in front of those odd columns. so my output will be like this This means the column 1 is with column 2, column 3 is with column 4 etc. so the empty places could be filled by ZERO or NA or even stays empty. 

    #     Acol1     V2       V4
    #1   0.700     1.400
    #2   0.850             1.600
    #3   1.633             1.900
    #4   2.083     1.650
    #5   2.117             2.117
    #6   2.267             2.300
    #7   2.517     2.100
    #8   2.667     2.267
    #9   3.567             9.717
    #10  3.883    3.017
    #11  5.350             21.600
    #12  9.700     
    #13 15.867

Upvotes: 2

Views: 154

Answers (2)

Sotos
Sotos

Reputation: 51592

Here it is: We first create a function

f1 <- function(x,y){
  x[match(newdf$Acol1, y)]
}

We then use mapply,

mapply(f1, df[,c(FALSE, TRUE)], df[,c(TRUE, FALSE)])
 #        V2     V4
 #[1,] 1.400     NA
 #[2,]    NA  1.600
 #[3,]    NA  1.900
 #[4,] 1.650     NA
 #[5,]    NA  2.117
 #[6,]    NA  2.300

You can then cbind that output to your newdf as usual

Upvotes: 2

DeveauP
DeveauP

Reputation: 1237

Here is a suggestion:

 new_df$V2<-apply(new_df,MARGIN = 1, function(z){
       w<-which(as.numeric(as.character(z)) == df[,1])
       if(length(w)){ 
         return(df[w,2])
       }
       else{ return(NA)}
    }
    )
new_df$V3<-apply(new_df,MARGIN = 1, function(z){
       w<-which(as.numeric(as.character(z)) == df[,3])
       if(length(w)){ 
         return(df[w,4])
       }
       else{ return(NA)}
    }
    )

Edit: for high number of columns

for(i in 1:nrow(result)){
val<-newdf[i,1]
w<-which(sapply(odd,function(y){
sum(val==df[,y],na.rm = TRUE)>0
}
))## in which column does the value appear?
result[i,w]<-sapply(w,function(z){
df[which(df[,odd[z]]==val),odd[z]+1]
}
) ### this might not work if the value in one odd column appears more than once
}
result<-cbind(newdf,result)

Upvotes: 2

Related Questions