0

I have a data set of "X" (values from 0 to 80) and "Y" (values from 0 to 80). I would like to create a new column "Table". I have 36 tables in mind: In groups of 6... They should be grouped according to:

  • Tables 1-6:ALL Y 11-20... Table 7-12:Y 21-30, Table 13-18:Y 31-40, Table 19-24:Y 41-50, Table 25-30:Y 51-60, Table 31-36:Y 61-70
  • Table 1: X 21-30 and Tables 7, 13, 19, 25, 31
  • Table 2: X 31-40 and Tables 8, 14, 20, 26, 32
  • Table 3: X 41-50 and Tables 9, 15, 21, 27, 33
  • Table 4: X 51-60 and Tables 10, 16, 22, 28, 34
  • Table 5: X 61-70 and Tables 11, 17, 23, 29, 35
  • Table 6: X 71-80 and Tables 12, 18, 24, 30, 36

End Result:

X   Y   Table
45  13    3
66  59    29
21  70    31
17  66    NA (there is no table for X lower than 21)

Should I be using the If Else function to group the data from the "X" and "Y" into my new "Table", ranging from 1 to 36 or something else? Any help will be appreciated! Thank you!

head(data)

    value avg.temp  X  Y
1      0     6.69   45 13
2      0     6.01   48 14
3      0     7.35   39 15
4      0     5.86   45 15
5      0     6.43   42 16
6      0     5.68   48 16
Bala
  • 67
  • 1
  • 7
  • I fear I don't understand your grouping. There seems to be two distinct set of tables, one for X and one for Y. – juba Feb 25 '13 at 10:52
  • @juba, I guess you'll have to replace the word `table` with `columns`. and the end result is more than just 3 rows, i assume. – Arun Feb 25 '13 at 10:59
  • Table 1: Y11-20, X21-30 and Table 2: Y11-20, X31-40 and Table 3:Y11-20 and X41-50 and so on.... Table 7: Y21-30, X21-30 and Table 8: Y21-30, X31-40 and Table 9: Y21-20, X41-50 and so on... ending with Table 35: Y61-70, X61-70 and Table 36: Y61-70, X71-80.... is it better now?? The 2 conditions to satisfy are X and Y... and the tables are in groups of 6... tables 1-6, 7-12..all have the same Y... tables 1,7,13,19,25,31 all have the same X... – Bala Feb 25 '13 at 11:00
  • and you want this huge data.frame of X,Y,Table? – Arun Feb 25 '13 at 11:03
  • once again, does your end table have values in order or are you expecting just a sample? Your question is unclear at the moment. – Arun Feb 25 '13 at 11:17
  • My end "Table".. it does not have to be in order... my beginning only had X and Y and my end result looks like above: X, Y and "Table" (just a new column) and it has values from 1 to 36... does not have to be in order... It comes from meeting 2 conditions: the value of X and the value of Y... If X has a certain value and Y has a certain value, it belongs to a certain group. e.g if X is _45_ and Y is **13**, then it belongs in table 3 because Table 3: X _41-50_ and table 1-6: Y **11-20** – Bala Feb 25 '13 at 11:41
  • @Bala please `dput( yourdata )` or `dput( head( yourdata ) )` so we have something to work with :) – Anthony Damico Feb 25 '13 at 11:58
  • @AnthonyDamico I do not understand what you mean by dput(mydata)... it just gives me all my data again... I have included part of my data above in the original text... dput gives me: value = c(0, 0, 0, 0, 0, 0), avg.temp = c(6.69, 6.01, 7.35, 5.86, 6.43, 5.68), X = c(45, 48, 39, 45, 42, 48), Y = c(13, 14, 15, 15, 16, 16)), .Names = c("value", "avg.temp", "X", "Y"), row.names = c(NA, 6L), class = "data.frame") – Bala Feb 25 '13 at 12:20
  • This is confuse... You noticed that your second classification (1-6) is only based on X? Look table 1: X 21-30 and Tables 7, 13, 19, 25, 31 - that's, Y range from 21 until 70. What if Y is greater than 70 and X is in that range? In your example, X=45, Y=13, Table=3 is ok. But the next one, how you achieved that Table? Give us a step-by-step. – Rcoster Feb 25 '13 at 12:24
  • @Bala Using `dput` makes it easy for us to import your data back in our session. We just have to copy/paste the `dput` output and asign it to an object. – juba Feb 25 '13 at 12:24
  • > dput(head(df)) gives me structure(list(value = c(0, 0, 0, 0, 0, 0), avg.temp = c(6.69, 6.01, 7.35, 5.86, 6.43, 5.68), X = c(45, 48, 39, 45, 42, 48), Y = c(13, 14, 15, 15, 16, 16)), .Names = c("value", "avg.temp", "X", "Y"), row.names = c(NA, 6L), class = "data.frame") – Bala Feb 25 '13 at 13:16
  • @bala [required reading before your next post to SO](http://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example) ;) – Anthony Damico Feb 25 '13 at 14:16
  • @Rcoster I cant write all the X and Y for tables 1 to 36... if you write it yourself, u will realize... when u take an X value, e.g. 66... that belongs in X 61-70... so it can be in tables 5, 11, 17, 23, 29, 35... next condition, its Y value is 59.. that belongs in Table 25-30:Y 51-60... which of 5,11,17,23,29 and 35 belongs in Table 25-30.... that will be table 29 :) Another e.g. X 21 belongs in Table 1: X 21-30 and Tables 7, 13, 19, 25, 31, Y 70 belongs in Table 31-36:Y 61-70... combining only table 31 meets both X and Y conditions... – Bala Feb 25 '13 at 17:47

2 Answers2

0

I think you could use something like this. If your data frame is called df :

df$Table <- NA
df$Table[df$X>=21 & df$X<=30 & df$Y>=11 & df$Y<=20] <- 1
df$Table[df$X>=31 & df$X<=40 & df$Y>=11 & df$Y<=20] <- 2
...
juba
  • 47,631
  • 14
  • 113
  • 118
  • Yes this works... I understand this will be a really basic way of doing it but is R able to do this more intelligently? because if I use the method above, I am going to have to write 32 lines... – Bala Feb 25 '13 at 13:11
  • @Bala Well, I think it's up to you to organize and code your conditions as cleverly as possible. But maybe you won't be able to express your 32 conditions in less than 32 lines. – juba Feb 25 '13 at 13:13
  • @Bala You're welcome. And don't forget you can accept the answer with the green mark if you think it has solved your problem. – juba Feb 25 '13 at 13:20
0

Use math and indexes:

# demo data
x <- data.frame(X = c(45,66,21,17,0,1,21,80,45),Y = c(13,59,70,66,80,11,0,1,27))

# if each GROUP of Y tables was numbered 1-6, aka indexing
x$ytableindex <- ((x$Y-1) - (x$Y-1) %% 10) / 10

# NA if too low
x$ytableindex[x$ytableindex < 1] <- NA

# find lowest table based on Y index
x$ytable <- (0:5*6+1)[x$ytableindex]

# find difference from lowest Y table to arrive at correct table using X
x$xdiff <- floor((x$X - 1) / 10 - 2)

# NA if too low
x$xdiff[x$xdiff < 0] <- NA

# use difference to calculate the correct table, NA's stay NA
x$Table <- x$ytable + x$xdiff
ARobertson
  • 2,857
  • 18
  • 24