CODEWITHSUNDEEP
rexcelloopsindexingmatch
Mu Ak
Mu Ak

Reputation: 25

Equivalent of Index matching from Excel in R

I have two tables, Table 1 and 2

Table is given as thus

 Table1 = read.table( textConnection("TimeString    P3  P5  P7  P9  P11
                      202101152300  19.52   51.32   56.37   60.26   71.37
                       202101160000 19.52   51.32   56.37   60.26   71.37
                       202101160100 19.52   51.32   56.37   60.26   71.37
                       202101160200 19.52   51.32   56.37   60.26   71.37
                       202101160300 19.52   51.32   56.37   60.26   71.37
                       202101160400 19.52   51.32   56.37   60.26   71.37
                       202101160500 19.76   51.68   56.77   60.67   71.79
                       202101160600 19.76   51.68   56.77   60.67   71.79
                       202101160700 19.54   51.12   56.16   60.01   71.01
                       202101160800 19.54   51.12   56.16   60.01   71.01
                       202101160900 25.45   51.12   56.16   60.01   71.01
                       202101161000 25.45   51.12   56.16   60.01   71.01
                       202101161100 25.45   51.12   56.16   60.01   71.01
                       202101161200 25.45   51.12   56.16   60.01   71.01
                       202101161300 25.45   51.12   56.16   60.01   71.01
                       202101161400 25.45   51.12   56.16   60.01   71.01
                       202101161500 25.45   51.12   56.16   60.01   71.01
                       202101161600 25.45   54.08   59.11   75.78   105.49
                       202101161700 25.45   54.08   59.11   75.78   105.49
                       202101161800 25.45   54.08   59.11   75.78   105.49
                       202101161900 25.45   51.12   56.16   60.01   71.01
                       202101162000 25.45   51.12   56.16   60.01   71.01
                       202101162100 25.45   51.12   56.16   60.01   71.01
                       202101162200 25.73   51.68   56.77   60.67   71.79
                       " ), header = T)

Table 2 which is a very large table but a snippet is given as

 Table2 = read.table(textConnection("PNumber    StartTimeString Modified
3   202101152300    TRUE
                                   5    202101152300    TRUE
                                   7    202101152300    TRUE
                                   9    202101152300    TRUE
                                   11   202101152300    TRUE
                                   3    202101160000    TRUE
                                   5    202101160000    TRUE
                                   7    202101160000    TRUE
                                   9    202101160000    TRUE
                                   11   202101160000    TRUE
                                   3    202101160100    TRUE
                                   5    202101160100    TRUE
                                   7    202101160100    TRUE
                                   9    202101160100    TRUE
                                   11   202101160100    TRUE
                                   3    202101160200    TRUE
                                   5    202101160200    TRUE
                                   7    202101160200    TRUE
                                   9    202101160200    TRUE
                                   11   202101160200    TRUE
                                   3    202101160300    TRUE
                                   5    202101160300    TRUE
                                   7    202101160300    TRUE
                                   9    202101160300    TRUE
                                   11   202101160300    TRUE
                                   3    202101160400    TRUE
                                   5    202101160400    TRUE
                                   7    202101160400    TRUE
                                   "),header = T)

Now I need to bring the numbers from Table 1 into Table2 by matching: both Time Strings ("TimeString" column in Table 1 and "StartTimeString" column in Table 2) AND Column names of Table1 with the concatenation of letter "P" and values "PNumber" Column in Table2

I solved it in Excel using the formula and converting table 2 to an Excel Table

=IF([@Modified],INDEX(Sheet1!$C$4:$G$27,MATCH([@StartTimeString],Sheet1!$B$4:$B$27,0),MATCH("P"&[@PNumber],Sheet1!$C$3:$G$3,0)),"")

The Result was (and this is the result I am expecting)

 read.table(textConnection("PTrue
19.52
                                   51.32
                                   56.37
                                   60.26
                                   71.37
                                   19.52
                                   51.32
                                   56.37
                                   60.26
                                   71.37
                                   19.52
                                   51.32
                                   56.37
                                   60.26
                                   71.37
                                   19.52
                                   51.32
                                   56.37
                                   60.26
                                   71.37
                                   19.52
                                   51.32
                                   56.37
                                   60.26
                                   71.37
                                   19.52
                                   51.32
                                   56.37
                                   "),header = T)

In converting the Excel code to R,I created new columns by first pasting the value of PNumber and Letter "P" then creating a match column and finally converting the returned value to numeric

Table2$PNumberconcat = paste0("P",Table2$PNumber)
Table2$Match = ifelse(Table2$StartTimeString %in% Table1$TimeString,match(Table2$PNumberconcat,names(Table1)),"")
Table2$Match = as.numeric(Table2$Match)

After this I tried looping through this but it doesn't seem to work - I am getting an empty column, what am I missing?

for(i in nrow(Table2)) {
  for(j in nrow(Table1)){
    Table2$PTrue[i] = ifelse(Table2$StartTimeString[i] %in% Table1$TimeString,Table1[j,Table2$Match[i]],"")
  }
}

You can use any other process different from mine. Thanks in advance

Upvotes: 2

Views: 100

Answers (1)

Ronak Shah
Ronak Shah

Reputation: 389055

You can do this with match :

rowindex <- match(Table2$StartTimeString, Table1$TimeString)
columnindex <- match(paste0("P",Table2$PNumber), names(Table1))
Table2$PTrue <- Table1[cbind(rowindex, columnindex)]

It returns :

Table2

#   PNumber StartTimeString Modified  PTrue
#1        3    202101152300     TRUE  19.52
#2        5    202101152300     TRUE  51.32
#3        7    202101152300     TRUE  56.37
#4        9    202101152300     TRUE  60.26
#5       11    202101152300     TRUE  71.37
#6        3    202101160000     TRUE  19.52
#7        5    202101160000     TRUE  51.32
#8        7    202101160000     TRUE  56.37
#9        9    202101160000     TRUE  60.26
#10      11    202101160000     TRUE  71.37
#11       3    202101160100     TRUE  19.52
#12       5    202101160100     TRUE  51.32
#13       7    202101160100     TRUE  56.37
#14       9    202101160100     TRUE  60.26
#15      11    202101160100     TRUE  71.37
#16       3    202101160200     TRUE  19.52
#17       5    202101160200     TRUE  51.32
#18       7    202101160200     TRUE  56.37
#19       9    202101160200     TRUE  60.26
#20      11    202101160200     TRUE  71.37
#21       3    202101160300     TRUE  19.52
#22       5    202101160300     TRUE  51.32
#23       7    202101160300     TRUE  56.37
#24       9    202101160300     TRUE  60.26
#25      11    202101160300     TRUE  71.37
#26       3    202101160400     TRUE  19.52
#27       5    202101160400     TRUE  51.32
#28       7    202101160400     TRUE  56.37

Upvotes: 1

Related Questions