New column in original df using another df

Hi all,

I have two dummy data frames and they are shown below. I would like to create a new column in df1 with the calculation "conc*height" if the month corresponding to the concentration in df1 matches the month in df2. It seems relatively straight-forward, but I'm afraid my R skills are not up to par yet.

Thanks so much in advance!

df1:
Year	Month	conc
2000	1	4
2000	2	3
2000	3	7
2000	4	4
2000	5	1
2000	6	4
2000	7	6
2000	8	8
2000	9	5
2000	10	3
2000	11	4
2000	12	3
2001	1	7
2001	2	3
2001	3	45
2001	4	2
2001	5	3
2001	6	7
2001	7	9
2001	8	7
2001	9	4
2001	10	2
2001	11	45
2001	12	7

df2:
Month	height
1	250
2	300
3	350
4	400
5	450
6	500
7	450
8	400
9	350
10	300
11	250
12    200

Probably a join:

1 Like

This is how to do it

library(dplyr)

df1 <- data.frame(
        Year = c(2000,2000,2000,2000,2000,2000,2000,
                 2000,2000,2000,2000,2000,2001,2001,2001,2001,2001,2001,
                 2001,2001,2001,2001,2001,2001),
       Month = c(1,2,3,4,5,6,7,8,9,10,11,12,1,2,
                 3,4,5,6,7,8,9,10,11,12),
        conc = c(4,3,7,4,1,4,6,8,5,3,4,3,7,3,
                 45,2,3,7,9,7,4,2,45,7)
)

df2 <- data.frame(
       Month = c(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12),
      height = c(250, 300, 350, 400, 450, 500, 450, 400, 350, 300, 250, 200)
)

df1 %>% 
    left_join(df2, by = "Month") %>% 
    mutate(new_col = conc * height)
#>    Year Month conc height new_col
#> 1  2000     1    4    250    1000
#> 2  2000     2    3    300     900
#> 3  2000     3    7    350    2450
#> 4  2000     4    4    400    1600
#> 5  2000     5    1    450     450
#> 6  2000     6    4    500    2000
#> 7  2000     7    6    450    2700
#> 8  2000     8    8    400    3200
#> 9  2000     9    5    350    1750
#> 10 2000    10    3    300     900
#> 11 2000    11    4    250    1000
#> 12 2000    12    3    200     600
#> 13 2001     1    7    250    1750
#> 14 2001     2    3    300     900
#> 15 2001     3   45    350   15750
#> 16 2001     4    2    400     800
#> 17 2001     5    3    450    1350
#> 18 2001     6    7    500    3500
#> 19 2001     7    9    450    4050
#> 20 2001     8    7    400    2800
#> 21 2001     9    4    350    1400
#> 22 2001    10    2    300     600
#> 23 2001    11   45    250   11250
#> 24 2001    12    7    200    1400

Created on 2020-04-19 by the reprex package (v0.3.0.9001)

Please notice the way I'm posting the answer, that would be a proper reproducible example, next time please try to make your questions providing one. Here is how to do it:

2 Likes

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.