Boris - 1 year ago 162
R Question

# Summarizing data in R

I have a data on countries and want to summarize it and create a table.

``````> head(data)
country year score members
A 1989     0       7
A 1990     0       7
A 1991     0       7
A 1992     0       7
A 1993     0       7
A 1994     0       7
``````

The table should show the relationship between country "score" and the number of "members" – put differently, I want to see how many states with score 0,1 or 2 have "members"(ranging from 1 to 7).

I want to set it like this:

``````score members==1 members==2 members==3 members==4 members==5 members==6 members==7
0        1          0
1        2          0
2        0          1  and so on..
``````

To do this I run the following:

``````library(dplyr)
table <- data %>%
group_by(score) %>%
summarise(
m1    = sum(members==1, na.rm=TRUE),
m2    = sum(members==2, na.rm=TRUE),
m3    = sum(members==3, na.rm=TRUE),
m4    = sum(members==4, na.rm=TRUE),
m5    = sum(members==5, na.rm=TRUE),
m6    = sum(members==6, na.rm=TRUE),
m7    = sum(members==7, na.rm=TRUE)

)
``````

This gives:

``````    score    m1    m2    m3    m4    m5    m6    m7
0     0     2     0     0     0     3    30
1    15     3    11    11     3    18     3
2     3     0     2     2     0     6     9
.
.
``````

I need a little help here..as you see it has calculated the total number of observations and not per country – I am sure that this is relatively easy to correct but it seems (right now, at least) quite complicated to me.

So how do I summarize this data so that I have the total number of countries for each members-level?

Reproducible sample :

``````> dput(data)
structure(list(country = structure(c(1L, 1L, 1L, 1L, 1L, 1L,
1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 2L,
2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L,
2L, 2L, 2L, 2L, 2L, 2L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L,
3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 4L, 4L, 4L, 4L, 4L, 4L, 4L, 4L,
4L, 4L, 4L, 4L, 4L, 4L, 4L, 4L, 4L, 4L, 4L, 4L, 4L, 4L, 4L, 5L,
5L, 5L, 5L, 5L, 5L, 5L, 5L, 5L, 5L, 5L, 5L, 5L, 5L, 5L, 5L, 5L,
5L, 6L, 6L, 6L, 6L, 6L, 6L, 6L, 6L, 6L, 6L, 6L, 6L, 6L, 6L, 6L,
6L, 6L, 6L), .Label = c("A", "B", "C", "D", "E", "F"), class = "factor"),
year = c(1989L, 1990L, 1991L, 1992L, 1993L, 1994L, 1995L,
1996L, 1997L, 1998L, 1999L, 2000L, 2001L, 2002L, 2003L, 2004L,
2005L, 2006L, 2007L, 2008L, 2010L, 1989L, 1990L, 1991L, 1992L,
1993L, 1994L, 1995L, 1996L, 1997L, 1998L, 1999L, 2000L, 2001L,
2002L, 2003L, 2004L, 2005L, 2006L, 2007L, 2008L, 2009L, 2010L,
2011L, 1989L, 1991L, 1993L, 1994L, 1995L, 1996L, 1997L, 1999L,
2000L, 2001L, 2002L, 2003L, 2004L, 2005L, 2006L, 2007L, 2008L,
2010L, 1989L, 1990L, 1991L, 1992L, 1993L, 1994L, 1995L, 1996L,
1997L, 1998L, 1999L, 2000L, 2001L, 2002L, 2003L, 2004L, 2005L,
2006L, 2007L, 2008L, 2009L, 2010L, 2011L, 1991L, 1992L, 1993L,
1994L, 1995L, 1997L, 1998L, 1999L, 2000L, 2001L, 2002L, 2003L,
2004L, 2005L, 2006L, 2007L, 2008L, 2010L, 1991L, 1992L, 1993L,
1994L, 1995L, 1997L, 1998L, 1999L, 2000L, 2001L, 2002L, 2003L,
2004L, 2005L, 2006L, 2007L, 2008L, 2010L), score = c(0L,
0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L,
0L, 0L, 0L, 0L, 0L, 1L, 0L, 1L, 1L, 0L, 1L, 1L, 0L, 1L, 1L,
1L, 1L, 1L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 1L, 2L, 2L,
2L, 1L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 1L, 1L,
2L, 0L, 1L, 1L, 1L, 0L, 1L, 1L, 1L, 1L, 1L, 2L, 1L, 1L, 1L,
1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 2L, 2L,
1L, 1L, 1L, 2L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L,
1L, 2L, 2L, 1L, 1L, 1L, 2L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L
), members = c(7L, 7L, 7L, 7L, 7L, 7L, 7L, 7L, 7L, 7L, 7L,
7L, 7L, 7L, 7L, 7L, 7L, 7L, 7L, 7L, 7L, 6L, 6L, 6L, 6L, 6L,
6L, 6L, 6L, 6L, 6L, 6L, 6L, 6L, 7L, 7L, 7L, 7L, 7L, 7L, 7L,
7L, 7L, 7L, 6L, 6L, 6L, 6L, 6L, 6L, 6L, 7L, 7L, 7L, 7L, 7L,
7L, 7L, 7L, 7L, 7L, 7L, 2L, 2L, 2L, 2L, 2L, 3L, 3L, 3L, 3L,
4L, 4L, 4L, 4L, 5L, 5L, 5L, 6L, 6L, 6L, 6L, 6L, 6L, 6L, 3L,
3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 4L, 4L, 4L, 4L, 4L, 4L, 4L,
4L, 4L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L,
1L, 1L, 1L, 1L, 1L)), .Names = c("country", "year", "score",
"members"), class = "data.frame", row.names = c(NA, -121L))
``````

As the OP is using `dplyr` methods, we can do this by grouping with 'score', 'members' to get the number of elements (`n()`), and then `spread` (from `tidyr`) to reshape it to 'wide' format.

``````library(dplyr)
library(tidyr)
data %>%
group_by(score, members) %>%
summarise(n = n()) %>%
mutate(members = paste0("m", members)) %>%
#  score    m1    m2    m3    m4    m5    m6    m7
#  <int> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
#1     0     0     2     0     0     0     3    30
#2     1    15     3    11    11     3    18     3
#3     2     3     0     2     2     0     6     9
``````

If we need to also get the counts by 'country', just add 'country' in the `group_by`

``````data %>%
group_by(country, score, members) %>%
summarise(n = n()) %>%
mutate(members = paste0("m", members)) %>%
If the expected output is the one showed in the other posts, an option using `data.table` would be to convert the 'data.frame' to 'data.table' (`setDT(data`), and `dcast` from 'long' to 'wide' specifying the `fun.aggregate` as `uniqueN` of the 'value.var' variable i.e. 'country' where `uniqueN` returns the `length` of `unique` elements in the 'country' column. The `fill=0` specifies to occupy 0 for those combinations that are not available. By default, it returns as NA.
``````library(data.table)