Fill in values of a variable based on another variable in the same dataframe + another smaller dataframe

Question

I have two dataframes:

main dataframe called df:

    variable     value n p
1          1 0.4457915 0 0
2          1 0.3573796 0 0
3          1 0.4809338 0 0
4          7 0.4707770 0 0
5          2 0.4617186 0 0
6          1 0.4330623 0 0
7          1 0.4426557 0 0
8          1 0.5265566 0 0
9          1 0.4606076 0 0
10         3 0.4150958 0 0
11         1 0.4459441 0 0
12         1 0.4143590 0 0
13         1 0.4344068 0 0
14         5 0.3259516 0 0
15         1 0.4202466 0 0
16         1 0.3120299 0 0
17         1 0.3938266 0 0
18         1 0.5133825 0 0
19         1 0.3331676 0 0
20         1 0.5563704 0 0

another smaller dataframe called cheatsheat:

    X1    X2
1   10 0.000
2   10 0.200
3   10 0.800
4   10 0.999
5   30 0.000
6   30 0.200
7   30 0.800
8   30 0.999
9  100 0.000
10 100 0.200
11 100 0.800
12 100 0.999
13 200 0.000
14 200 0.200
15 200 0.800
16 200 0.999

What I try to accomplish is fill in the n and p in the main dataframe df based on the variable "variable" (This ranges from 1 to 16, the same as the number of rows in the dataframe cheatsheat) and the values of X1 and X2 in the dateframe cheatsheat.

This means the output should look this:

    variable     value  n   p
1          1 0.4457915 10 0.0
2          1 0.3573796 10 0.0
3          1 0.4809338 10 0.0
4          7 0.4707770 30 0.8
5          2 0.4617186 10 0.2
6          1 0.4330623 10 0.0
7          1 0.4426557 10 0.0
8          1 0.5265566 10 0.0
9          1 0.4606076 10 0.0
10         3 0.3201487 10 0.8
11         1 0.4459441 10 0.0
12         1 0.4143590 10 0.0
13         1 0.4344068 10 0.0
14         5 0.3259516 30 0.0
15         1 0.4202466 10 0.0
16         1 0.3120299 10 0.0
17         1 0.3938266 10 0.0
18         1 0.5133825 10 0.0
19         1 0.3331676 10 0.0
20         1 0.5563704 10 0.0

I already accomplished this with the following for loop:

  for (i in 1:nrow(df)) {
    df[i, "n"] <- cheatsheat[df[i, "variable"], "X1"]
    df[i, "p"] <- cheatsheat[df[i, "variable"], "X2"]
  }

However, you guys only see 20 row in the main dataframe while in reality I have more than 200000. This means it would take a really long time to finish the script. Do you guys know how I can accomplish the same as the for loop but then without a for loop itself? I understood that vectorization might help solve this problem. I have looked for an answer here on StackOverflow for hours but I could not find an answer. Any help is appreciated!

Answer 1

You can solve your problem using the match function.

variableMatchIndices <- match(df$variable,1:NROW(cheatsheat))

Now you can fill your df by accessing cheatsheat via those indices:

df$n <- cheatsheat[variableMatchIndices ,1]
df$p <- cheatsheat[variableMatchIndices ,2]