Home > OS >  Subset a new dataframe that changes minuses the summed values of three dataframes from a fourth
Subset a new dataframe that changes minuses the summed values of three dataframes from a fourth

Time:07-21

I have a four dataframes of the following structure:

df1 <- data.frame(c("2021-01-01", "2021-01-02", "2021-01-03", "2021-01-04", "2021-01-05", "2021-01-06"), c(1002, 1200, 1111, 2211, 1112, 1621), c("A", "B", "C", "D", "E", "F"))
names(df1) <- c('date', 'value', 'type')
df2 <- data.frame(c("2021-01-01", "2021-01-02", "2021-01-03", "2021-01-04", "2021-01-05", "2021-01-06"), c(401, 222, 967, 111, 982, 120), c("A", "B", "C", "D", "E", "F"))
names(df2) <- c('date', 'value', 'type')
df3 <- data.frame(c("2021-01-01", "2021-01-02", "2021-01-03", "2021-01-04", "2021-01-05", "2021-01-06"), c(214, 151, 272, 295, 102, 211), c("A", "B", "C", "D", "E", "F"))
names(df3) <- c('date', 'value', 'type')
df4 <- data.frame(c("2021-01-01", "2021-01-02", "2021-01-03", "2021-01-04", "2021-01-05", "2021-01-06"), c(107, 751, 614, 527, 125, 111), c("A", "B", "C", "D", "E", "F"))
names(df4) <- c('date', 'value', 'type')

I'd like to create a subset of df1 (that keeps the date, but replaces the type with "Z") and for values minuses the summed values (per day) in df2, df3, and df4 from the daily value in df1. So the value for 2021-01-01 would be: 280.

The code I've tried is:

new_df <- df1 %>% df1&values-sum(df2$values, df3$values, df4$values)

However this sadly returns a "3 arguments passed to '$' which requires 2" error and therefore can't move on to renaming the type for the new df.

Any assistance would be most appreciated.

CodePudding user response:

You could do:

new_df <- df1
new_df$type <- "Z"
new_df$value <- df1$value - df2$value - df3$value - df4$value

Output:

        date value type
1 2021-01-01   280    Z
2 2021-01-02    76    Z
3 2021-01-03  -742    Z
4 2021-01-04  1278    Z
5 2021-01-05   -97    Z
6 2021-01-06  1179    Z

CodePudding user response:

If we have many datasets with the object names having the same pattern (df followed by one or more digits (\\d ), load them into a list with mget, extract the 'value' column and use - with Reduce

new_df <- df1
new_df$value <- Reduce(`-`, lapply(mget(ls(pattern = "^df\\d $")), "[[", "value"))

-output

> new_df
        date value type
1 2021-01-01   280    A
2 2021-01-02    76    B
3 2021-01-03  -742    C
4 2021-01-04  1278    D
5 2021-01-05   -97    E
6 2021-01-06  1179    F
  • Related