1

I want to convert a basket format to single,and used the code by "Convert basket to single", but an error happened as below:

> Data <- read.table("r9.txt")
> Data
      V1      V2  V3  V4  V5  V6  V7  V8  V9 V10 V11
1  A3322 neutral 157 158 159 160 161 162 163 164 165
2  A4003    cold 157 158 159 160 161 162 163 164 165
3  A3928    none 154 155 157 159 160 163 164 165  NA
4  A4002 neutral 158 159 160 161 162 163 164 165  NA
5  A3992    none 159 160 161 162 163 164 165  NA  NA
6  A3993    none 159 160 161 162 163 164 165  NA  NA
7  A3994    cold 159 160 161 162 163 164 165  NA  NA
8  A3995    cold 159 160 161 162 163 164 165  NA  NA
9  A3996    none 159 160 161 162 163 164 165  NA  NA
10 A3997    none 159 160 161 162 163 164 165  NA  NA
11 A4001 neutral 159 160 161 162 163 164 165  NA  NA
12 A1458    none 160 161 162 163 164 165  NA  NA  NA
13 A2042    cold 160 161 162 163 164 165  NA  NA  NA
> col1=NULL
> col2=NULL
> for(i in 1:nrow(Data)){
+ col1=c(col1,rep(Data[i],ncol(Data)-1))
+ col2=c(col2,Data[i,-1])}
erro`[.data.frame`(Data, i) : undefined columns selected
>
David Arenburg
  • 91,361
  • 17
  • 137
  • 196
user4962603
  • 13
  • 2

1 Answers1

2

What you trying to do is to "melt" you data while making V1 your id variable, try the following

library(reshape2) 
res <- na.omit(melt(Data, "V1")[-2])
res[order(res$V1), ]
#        V1   value
# 12  A1458    none
# 25  A1458     160
# 38  A1458     161
# 51  A1458     162
# 64  A1458     163
# 77  A1458     164
# 90  A1458     165
# 13  A2042    cold
# 26  A2042     160
# 39  A2042     161
# 52  A2042     162
# 65  A2042     163
....

# or, for a general set of id columns:
idvars = c("V1","V2")
res <- na.omit(melt(Data,idvars)[-(1+length(idvars))])
res[do.call(order,res[,idvars]),]
#       V1   V2 value
# 12 A1458 none   160
# 25 A1458 none   161
# 38 A1458 none   162
# 51 A1458 none   163
# 64 A1458 none   164
# 77 A1458 none   165
....

Or similarly with dplyr/tidyr

library(tidyr)
library(dplyr)
Data %>%
  gather(variable, value, -V1) %>%
  na.omit() %>%
  arrange(V1) %>%  
  select(-variable)
Frank
  • 66,179
  • 8
  • 96
  • 180
David Arenburg
  • 91,361
  • 17
  • 137
  • 196
  • I don't understand the OP, but it seems like "cold" etc should be part of the grouping/id var, not mixed in with the numbers, like `res <- na.omit(melt(Data, c("V1","V2")))[-3]; res[with(res,order(V1,V2)), ]`... not sure the `dplyr` analogue. – Frank Jun 01 '15 at 20:55
  • Maybe. Im also not sure. You are free to edit as Im not infront of a compiter right now – David Arenburg Jun 01 '15 at 21:05
  • 1
    Dear David, thank you so much, I got the result I want by the code you presented, you are awsome. – user4962603 Jun 02 '15 at 06:22