r - 过滤变量以连接两个不同维度的数据框(filter variables to connect two dataframes over different dimensions)

Question

There are two dataframes which I want to connect.

(我要连接两个数据框。)

So because I have 2 dimensions to filter a value from the column of the second table which meets some conditions of the first table.

(因此，因为我有2个维度可以从第二个表的列中筛选出满足第一个表的某些条件的值。)

The first dataframe looks like this:

(第一个数据帧如下所示：)

 letter   year  value
    A        2001   
    B        2002
    C        2003
    D        2004

second one:

(第二个：)

       letter  2001  2002 2003 2004
        A         4     9    9   9
        B         6      7   6    6  
        C         2      3   5    8 
        D         1       1  1    1

which gives me something like this

(这给我这样的东西)

letter year    value
A       2001    4
B       2002    7
C       2003    5
D       2004    1

thank all of you

  ask by sven krug translate from so

Answer 1

One option is to row/column index.

(一种选择是row/column索引。)

Here, the row index can be sequence of rows, while the column index we get from match ing the 'year' column of first data with the column names of second, cbind the indexes to create a matrix ('m1') and use that to extract values from second dataset and assign those to 'value' column in first data

(在这里，行索引可以是行序列，而我们通过将第一个数据的“ year”列与第二个列名进行match而获得的列索引，将索引cbind以创建matrix （m1）并使用从第二个数据集中提取值，并将其分配给第一个数据中的“值”列)

i1 <- seq_len(nrow(df1))
j1 <- match(df1$year, names(df2)[-1])
m1 <- cbind(i1, j1)
df1$value <- df2[-1][m1]
df1
#   letter year value
#1      A 2001     4
#2      B 2002     7
#3      C 2003     5
#4      D 2004     1

---

For the specific example, the pattern to extract seems to be the diag onal elements, in that case, we can also use

(对于具体的例子，图案提取物似乎是diag Onal地区的元素，在这种情况下，我们还可以使用)

df1$value <- diag(as.matrix(df2[-1])) 

data (数据)

df1 <- structure(list(letter = c("A", "B", "C", "D"), year = 2001:2004),
class = "data.frame", row.names = c(NA, 
-4L))

df2 <- structure(list(letter = c("A", "B", "C", "D"), `2001` = c(4L, 
6L, 2L, 1L), `2002` = c(9L, 7L, 3L, 1L), `2003` = c(9L, 6L, 5L, 
1L), `2004` = c(9L, 6L, 8L, 1L)), class = "data.frame", 
row.names = c(NA, 
-4L))