user6644063 user6644063 - 1 year ago 162
R Question

Divide each data.frame inside of a list by another data.frame

I have got a list of 1000 data.frames and I need to divide each of them by a 'single' data.frame.

The point is that each observation in the data.frames (contained in the list) has specific IDs which have to match with the 'single' data.frame ones and the IDs not contained into the latter have to be ignored (because no division is possible).

Here an example of one of my data.frames contained in the list:

df = read.table(text = 'ID Num
D 34
W 45
Q 12
Y 45
B 11
O 2', header = TRUE)

The 'single' data.frame is the following:

sing_df = read.table(text = 'ID Num
D 14
Q 11
B 9', header = TRUE)

By diving
my output should be:

ID Num
D 2.428
Q 1.09
B 1.22

Any suggestion?

Answer Source

We can merge the datasets by 'ID' and create the 'Num' by dividing the 'Num.x' with 'Num.y'.

transform(merge(df, sing_df, by = "ID"), Num = Num.x/Num.y)[-(2:3)]
#  ID      Num
#1  B 1.222222
#2  D 2.428571
#3  Q 1.090909

In case we need some packages, then inner_join from dplyr can be helpful

inner_join(df, sing_df, by = "ID") %>%
        mutate(Num = Num.x/Num.y) %>%
        select(-Num.x, -Num.y)

As the OP mentioned about 1000s of datasets, it is better to keep it in a list, loop through the list elements and apply the same step as above

lst1 <- lapply(lst, function(dat) transform(merge(dat, sing_df, by = "ID"), 
             Num = Num.x/Num.y)[-(2:3)])
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download