# combining rows to get average relative frequencies

Hi everyone

I have a dataset with multiple rows per "id" and would like to combine these rows.
Some columns (in the example dataset W1-W3) include relative frequencies. I would like a dataset with one row per id and the values of the average relative frequencies in the columns W1:W3.

Example dataset:

``````df_mp <- data.frame(id = c(1, 1, 1, 2, 2),
W1 = c(3.4, 2.5, 1.4, 0, 1.1),
W2 = c(1.6, 2.3, 0.7, 1.1, 8.1),
W3 = c(0, 0.1, 0.3, 2, 3.1))

id  W1  W2  W3
1  1 3.4 1.6 0.0
2  1 2.5 2.3 0.1
3  1 1.4 0.7 0.3
4  2 0.0 1.1 2.0
5  2 1.1 8.1 3.1
``````

The new dataset should look as follows:

``````  id       W1       W2        W3
1  1 2.433333 1.533333 0.1333333
2  2 0.550000 4.600000 2.5500000
``````

The values in W1:W3 are the "average relative frequencies": all the relative frequencies of each "id" summed up and divided by the number of rows that were combined for that "id".

See section 5.6 in R for Data Science

``````library(tidyverse)
df_mp <- data.frame(id = c(1, 1, 1, 2, 2),
W1 = c(3.4, 2.5, 1.4, 0, 1.1),
W2 = c(1.6, 2.3, 0.7, 1.1, 8.1),
W3 = c(0, 0.1, 0.3, 2, 3.1))
df_mp |>
group_by(id) |>
summarise(across(W1:W3, mean))
#> # A tibble: 2 × 4
#>      id    W1    W2    W3
#>   <dbl> <dbl> <dbl> <dbl>
#> 1     1  2.43  1.53 0.133
#> 2     2  0.55  4.6  2.55
``````

Created on 2022-05-28 by the reprex package (v2.0.1)

Thank you very much and sorry for asking a basic question like that.

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

If you have a query related to it or one of the replies, start a new topic and refer back with a link.