0

I have a dataset where the first column is the quarter month, the second is the year and then I have the some data (say prices) for all the countries as the other columns. It looks like this

Month   Year   Australia  Austria  New Zealand  USA   UK   Germany
03      2001   45.6       21.4     34.3         61.2  76.21   67
06      2001   47.8       22.4     34.4         51.2  76.32   67
09      2001   43.2       23.4     34.2         51.2  76.34   67
12      2001   45.6       24.4     34.6         31.2  76.43   67
03      2001   48.9       24.4     34.7         61.2  76.43   67
06      2001   42.4       22.4     34.7         41.2  76.43   67
09      2001   43.4       25.4     34.5         76.2  76.43   67
12      2001   43.4       26.4     34.4         64.2  76.21   67

I have this data for many years and many countries

I want to create a dataset which gives the average of the prices for each of the country for every year.

It should look like this with the data for the average values of each country in the year

  Year   Australia  Austria  New Zealand  USA   UK   Germany  
  2001   
  2002

How should I efficiently do this? I'd really appreciate the help

Sankalp Mathur
  • 119
  • 1
  • 5
  • Does this answer your question? [Mean per group in a data.frame](https://stackoverflow.com/questions/21982987/mean-per-group-in-a-data-frame) – camille Dec 12 '19 at 20:49

1 Answers1

2

Using dplyr, you can do something simple as:

df <- df %>% group_by(year) %>% summarise_at(vars(-month), ~mean(.) )

Which first groups the dataframe by year and than calculates the mean by group (which is year) for all variables excluding month.

Or you can drop the columns you do not want to summarise first and than use summarise_all.

df <- df %>% select(-month) %>% group_by(year) %>% summarise_all(~mean(.) )

Annet
  • 846
  • 3
  • 14
  • If all you're doing is passing a function, you don't need the formula notation, you can just use `summarise_all(mean)` – camille Dec 12 '19 at 20:36