Kelsey Kelsey - 15 days ago 8
R Question

Using a loop, merge two dataframes and subset by group

I have 2 data frames with 85 different IDs, and 40 different groups, so I'd like to use a loop as it is too much data to go through by hand.

Here's what I need to do:

I want to merge the two data frames by ID for each group, make a new column that multiplies Data X Area, then extract separate csvs by each group. Each ID in each group has a different area, but number of IDs is the same per group.

Here's the dummy data:

Group <- c(100,100,100,103,103,103,110,110,110)
ID <- c(1,2,3,1,2,3,1,2,3)
Area <- c(23,4,3,23,0,.5,7,2,33)

x <- data.frame(Group, ID, Area)

ID <- c(1,2,3)
Data <- c(.002,.4,1)

y <- data.frame(ID, Data)


Desired Output (different csv for each group):

Group ID Area Data A.D
100 1 23 .002 .046
100 2 4 .4 1.6
100 3 3 1 3

Jan Jan
Answer

If I got your question right, you might be able to do sth. like:

library(dplyr)
merge(x, y, by = c("ID")) %>% 
  mutate(A.D = Area * Data) %>%
  arrange(Group)

Which yields:

  ID Group Area  Data    A.D
1  1   100 23.0 0.002  0.046
2  2   100  4.0 0.400  1.600
3  3   100  3.0 1.000  3.000
4  1   103 23.0 0.002  0.046
5  2   103  0.0 0.400  0.000
6  3   103  0.5 1.000  0.500
7  1   110  7.0 0.002  0.014
8  2   110  2.0 0.400  0.800
9  3   110 33.0 1.000 33.000
Comments