I have a large dataset, which I splitted up into subsets. For each subsets, I have to do the same calculations but with different numbers. Example:
Main Table
x a b c d
A 1 2 4 5
A 4 5 1 7
A 3 5 6 2
B 4 5 2 9
B 3 5 2 8
C 4 2 5 2
C 1 9 6 9
C 1 2 3 4
C 6 3 6 2
Additional Table for A
a b c d
A 5 1 6 1
Additional Table for B
a b c d
B 1 5 2 6
Additional Table for C
a c c d
C 8 2 4 1
I need to multiply all rows A in the Main Table with the values from Additional Table for A, all rows B in the Main table with the values from B and all rows B in the main table with values from C. It is completely fine to merge the additional tables into a combined one, if this makes the solution easier.
I thought about a for-loop but I am not able to put the different multiplicators (from the Additional Tables) into the code. Since there is a large number of subgroups, coding each multiplication manually should be avoided. How do I do this multiplications?
If we start with the addition table as addDf and main table as df:
addDf
x a b c d
A A 5 1 6 1
B B 1 5 2 6
C C 8 2 4 1
We can use a merge and the by-element multiplication of matrix as,
df[-1] <- merge(addDf, data.frame(x = df[1]), by = "x")[-1] * df[order(df[1]), -1]
df
x a b c d
1 A 5 2 24 5
2 A 20 5 6 7
3 A 15 5 36 2
4 B 4 25 4 54
5 B 3 25 4 48
6 C 32 4 20 2
7 C 8 18 24 9
8 C 8 4 12 4
9 C 48 6 24 2
Note: Borrowed a little syntax sugar from @akrun as df[-1] assignment.