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.
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.