Calculate maximum and minimum for groups by monthly basis-CodePudding

I have a following data frame and I would like to calculate maximum and minimum for each country for each month.

Date <- c("2004-01-01","2004-01-01", "2004-01-15","2004-01-26","2004-02-01","2004-02-03","2004-02-03","2004-02-03","2004-03-01","2004-03-02","2004-03-02","2004-03-06","2004-03-06","2004-03-10", "2004-03-11","2004-03-11","2004-03-12","2004-03-12", "2004-01-01","2004-01-01", "2004-01-15","2004-01-26","2004-02-01","2004-02-03","2004-02-03","2004-02-03","2004-03-01","2004-03-02","2004-03-02","2004-03-06","2004-03-06","2004-03-10",
"2004-03-11","2004-03-11","2004-03-12","2004-03-12", "2004-01-01","2004-01-01", "2004-01-15","2004-01-26","2004-02-01","2004-02-03","2004-02-03","2004-02-03","2004-03-01","2004-03-02","2004-03-02","2004-03-06","2004-03-06","2004-03-10","2004-03-11","2004-03-11","2004-03-12","2004-03-12")
Country <- c("Netherlands", "Netherlands", "Netherlands", "Netherlands", "Netherlands", "Netherlands", "Netherlands", "Netherlands", "Netherlands", "Netherlands", "Netherlands", "Netherlands", "Netherlands", "Netherlands", "Netherlands", "Netherlands", "Netherlands", "Netherlands","Belgium", "Belgium", "Belgium", "Belgium", "Belgium", "Belgium", 
"Belgium", "Belgium", "Belgium", "Belgium", "Belgium", "Belgium", "Belgium", "Belgium", "Belgium", "Belgium", "Belgium", "Belgium", "France", "France", "France","France", "France", "France","France", "France", "France","France", "France", "France","France", "France", "France","France", "France", "France")
Year <- c(2004,2004,2004,2004,2004,2004,2004,2004,2004,2004,2004,2004,2004, 2004,2004,2004,2004,2004,2004,2004,2004,2004,2004,2004,2004,2004,2004,2004,2004,2004,2004,2004,2004,2004,2004,2004,2004,2004,2004,2004,2004,2004,2004,2004,2004,2004,2004,2004,2004,2004,2004,2004,2004,2004)
Month <- c(1,1,1,1,2,2,2,2,3,3,3,3,3,3,3,3,3,3,1,1,1,1,2,2,2,2,3,3,3,3,3,3,3,3,3,3,1, 1,1,1,2,2,2,2,3,3,3,3,3,3,3,3,3,3)
Amount <- as.numeric(c(100,50,40,3.50,14.60,11.60,140.20,140.30,147.30,151.20,159.60, 165.60,173.20,177.30,185.30,199.30,217.10,234.90,10.20,3.10,16.00,4.90,17.60,5.30,10.90,12.80,8.40,140.20,140.30,147.30,151.20,159.60,165.60,173.20,177,40,50,.30,185.30,199.30,217.10,234.9,500,300,0,8.40,140.20,140.30,3,147.30,151.20,8,40,30))
df <- data.frame(as.Date(Date),Country,Year, Month, Amount)

I would like to get this kind of table:

Country	Year	Month	Max	Min
Netherlands	2014	1	100	3.5
Netherlands	2014	2	11.6	140.3
Netherlands	2014	3	147.3	234.9
Belgium	2014	1	16.0	3.1
Belgium	2014	2	17.6	5.3
Belgium	2014	3	177.0	8.4
France	2014	1	199.3	0.3
France	2014	2	500.0	217.1
France	2014	3	151.2	0.0

I tried a code using group_by but I always get max/min for the whole dataset.

df %>%
  group_by(Country,Month) %>%
  summarise(MaxAmount = max(Amount, na.rm = T), MinAmount = min(Amount, na.rm = T))

Can you please help. Thank you.

CodePudding user response：

Try ,

library(dplyr)
df %>%
     group_by(Country,Year,Month) %>%
     summarise(MaxAmount = max(Amount, na.rm = T), 
     MinAmount = min(Amount, na.rm = T))

you will get :

# A tibble: 9 × 4
# Groups:   Country [3]
  Country     Month MaxAmount MinAmount
  <chr>       <dbl>     <dbl>     <dbl>
1 Belgium         1      16         3.1
2 Belgium         2      17.6       5.3
3 Belgium         3     177         8.4
4 France          1     199.        0.3
5 France          2     500       217. 
6 France          3     151.        0  
7 Netherlands     1     100         3.5
8 Netherlands     2     140.       11.6
9 Netherlands     3     235.      147.