I have the first dataframe like:
df1<-structure(list(`Demand Per Section` = c(80, 125, 350, 100, 538,
75, 25, 138, 138, 75, 150, 37, 225, 35, 40, 125, 25, 25, 125,
50), `Element Name` = c("Naphthalene", "Nitric acid (concentrated)",
"Sulphuric acid(concentrated)", "2-hydroxybenzoic acid", "Acetic anhydride",
"2-Naphthol", "Sodium Hydroxide", "Phenyl hydrazine hydrochloride",
"Glucose", "Sodium acetate", "Aniline", "Zinc poweder", "2-amino-benzoic acid",
"1.3-dihydroxybenzene", "Ethyl acetate", "hydroxy benzene", "phenyl methanol",
"Sodium carbonate", "Potassium permanganate", "Sodium bisulfite."
), `Course Name` = c("Course 1", "Course 1", "Course 1", "Course 1",
"Course 1", "Course 1", "Course 1", "Course 1", "Course 1", "Course 1",
"Course 1", "Course 1", "Course 1", "Course 1", "Course 1", "Course 1",
"Course 1", "Course 1", "Course 1", "Course 1"), Department = c("Chemsitry",
"Chemsitry", "Chemsitry", "Chemsitry", "Chemsitry", "Chemsitry",
"Chemsitry", "Chemsitry", "Chemsitry", "Chemsitry", "Chemsitry",
"Chemsitry", "Chemsitry", "Chemsitry", "Chemsitry", "Chemsitry",
"Chemsitry", "Chemsitry", "Chemsitry", "Chemsitry")), row.names = c(NA,
-20L), class = c("tbl_df", "tbl", "data.frame"), na.action = structure(c(`81` = 81L,
`101` = 101L, `127` = 127L, `134` = 134L, `135` = 135L, `136` = 136L,
`174` = 174L, `183` = 183L, `220` = 220L, `225` = 225L, `245` = 245L,
`286` = 286L, `288` = 288L, `290` = 290L, `305` = 305L, `314` = 314L,
`324` = 324L, `329` = 329L), class = "omit"))
`Demand Per Section` `Element Name` `Course Name` Department
<dbl> <chr> <chr> <chr>
1 80 Naphthalene Course 1 Chemsitry
2 125 Nitric acid (concentrated) Course 1 Chemsitry
3 350 Sulphuric acid(concentrated) Course 1 Chemsitry
4 100 2-hydroxybenzoic acid Course 1 Chemsitry
5 538 Acetic anhydride Course 1 Chemsitry
6 75 2-Naphthol Course 1 Chemsitry
7 25 Sodium Hydroxide Course 1 Chemsitry
8 138 Phenyl hydrazine hydrochloride Course 1 Chemsitry
9 138 Glucose Course 1 Chemsitry
10 75 Sodium acetate Course 1 Chemsitry
11 150 Aniline Course 1 Chemsitry
12 37 Zinc poweder Course 1 Chemsitry
13 225 2-amino-benzoic acid Course 1 Chemsitry
14 35 1.3-dihydroxybenzene Course 1 Chemsitry
15 40 Ethyl acetate Course 1 Chemsitry
16 125 hydroxy benzene Course 1 Chemsitry
17 25 phenyl methanol Course 1 Chemsitry
18 25 Sodium carbonate Course 1 Chemsitry
and a second dataframe like:
df2<-structure(list(`Course name` = c("Course 1", "Course 2", "Course 3",
"Course 4", "Course 1", "Course 2", "Course 3", "Course 4", "Course 5",
"Course 1", "Course 2", "Course 3", "Course 4", "Course 5"),
`number of sections` = c(3, 5, 3, 4, 7, 2, 7, 10, 3, 4, 5,
6, 2, 2), Department = c("Chemsitry", "Chemsitry", "Chemsitry",
"Chemsitry", "Biology", "Biology", "Biology", "Biology",
"Biology", "Physics", "Physics", "Physics", "Physics", "Physics"
)), row.names = c(NA, -14L), class = c("tbl_df", "tbl", "data.frame"
))
`Course name` `number of sections` Department
<chr> <dbl> <chr>
1 Course 1 3 Chemsitry
2 Course 2 5 Chemsitry
3 Course 3 3 Chemsitry
4 Course 4 4 Chemsitry
5 Course 1 7 Biology
6 Course 2 2 Biology
7 Course 3 7 Biology
8 Course 4 10 Biology
9 Course 5 3 Biology
10 Course 1 4 Physics
11 Course 2 5 Physics
12 Course 3 6 Physics
13 Course 4 2 Physics
14 Course 5 2 Physics
What I want is to create a new column in df1 named DemandCourse which will look into df2 in the columns of Course Name and Department and when both will match with Course Name and Department of df1 it will multiply the Demand per Section of df1 with the number of sections of df2. So for example the first row of the new column will be 80*3 =240
CodePudding user response：
We may need to join (left_join) and then mutate to create the column
library(dplyr)
df1 <- left_join(df1, df2,
by = c("Course Name" = "Course name", "Department")) %>%
mutate(DemandCourse = `Demand Per Section` * `number of sections`,
.keep = "unused")
-output
> df1
# A tibble: 20 × 4
`Element Name` `Course Name` Department DemandCourse
<chr> <chr> <chr> <dbl>
1 Naphthalene Course 1 Chemsitry 240
2 Nitric acid (concentrated) Course 1 Chemsitry 375
3 Sulphuric acid(concentrated) Course 1 Chemsitry 1050
4 2-hydroxybenzoic acid Course 1 Chemsitry 300
5 Acetic anhydride Course 1 Chemsitry 1614
6 2-Naphthol Course 1 Chemsitry 225
7 Sodium Hydroxide Course 1 Chemsitry 75
8 Phenyl hydrazine hydrochloride Course 1 Chemsitry 414
9 Glucose Course 1 Chemsitry 414
10 Sodium acetate Course 1 Chemsitry 225
11 Aniline Course 1 Chemsitry 450
12 Zinc poweder Course 1 Chemsitry 111
13 2-amino-benzoic acid Course 1 Chemsitry 675
14 1.3-dihydroxybenzene Course 1 Chemsitry 105
15 Ethyl acetate Course 1 Chemsitry 120
16 hydroxy benzene Course 1 Chemsitry 375
17 phenyl methanol Course 1 Chemsitry 75
18 Sodium carbonate Course 1 Chemsitry 75
19 Potassium permanganate Course 1 Chemsitry 375
20 Sodium bisulfite. Course 1 Chemsitry 150