collapse
Replaces the data with one row per group, holding summaries.
What we have
- statistics:
count,first,last,max,mean,median,min,rawsum,sd,sum by(), and weightsset mincellblanks the statistics of a small group in the data itself
Coming later
- percentile statistics such as
(p25)
How you write it
collapse (stat) varlist [(stat) varlist ...] [weight], by(varlist)
Square brackets mean optional. A vertical bar means choose one.
What it does
collapse (mean) wage (sum) workers, by(state sector) turns a file of individuals into a file of state-and-sector averages and totals. The individual rows are gone; what you have now is the summary table, as a dataset you can save, chart or merge onto something else.
Available statistics include mean, sum, median, sd, min, max and count. Weights work, which matters for survey data.
Watch out. This replaces your data.
preserve first if you still need the rows.Try it
Every example below was run after sysuse vira.sample02, clear.
. collapse (mean) mpce (sum) hhsize, by(state sector)
The same thing in pandas and in R
pandas
df = df.groupby(['state', 'sector']).agg({'mpce': 'mean', 'hhsize': 'sum'}).reset_index()
R
library(dplyr)
df <- df %>% group_by(state, sector) %>% summarise(mpce = mean(mpce,
na.rm = TRUE), hhsize = sum(hhsize, na.rm = TRUE)) %>% ungroup()
# This R was run in a real R (4.6.1) and gave the same numbers.
. list in 1/5
state sector mpce hhsize
-----------------------------------
1. Bihar 1 3505.711 2336
2. Bihar 2 4375.272 809
3. Gujarat 1 3389.703 3010
4. Gujarat 2 4328.457 1415
5. Kerala 1 3328.933 3098
The same thing in pandas and in R
pandas
import numpy as np mask = (np.arange(1, len(df) + 1) >= 1) & (np.arange(1, len(df) + 1) <= 5) df[['state', 'sector', 'mpce', 'hhsize']].head()
R
library(dplyr) df %>% filter(row_number() >= 1 & row_number() <= 5) %>% head(10) # This R has NOT been run against a real R yet: read it as documentation.