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

Question

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

df

by

sing_df

my output should be:

ID   Num

D    2.428

Q    1.09

B    1.22

Any suggestion?

Recommended for you: Get network issues from WhatsUp Gold. Not end users.

Answer 1

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

library(dplyr)
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)])

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

Sign up