/* parqit collapse - Make a dataset of summary statistics, frequencies, or a pivot table; convert between wide and long *! VERSION 0.1.37 07sep2026 User > parqit > Collapse, contract, pivot table, or reshape... Launch directly with: db parqit_pivot (menu installed by: parqit menu) Builds parqit collapse / pivot / contract / reshape. collapse and pivot share the statistic rows ((stat) [target=]source, exactly collapse's grammar); a pivot table additionally spreads one column per distinct value of the cols() variable, Excel-style. Populate fills the pickers from the current view. Lazy: the view's plan changes, not the data in memory — materialise with parqit collect or parqit save. */ VERSION 16.0 INCLUDE _std_wide DEFINE _dlght 460 INCLUDE header HELP hlp1, view("help parqit##verbs") RESET res1 SCRIPT PREINIT BEGIN create STRING dlgname dlgname.storeDialogClassName create DOUBLE pq_populate_error END PROGRAM POSTINIT_PROGRAM BEGIN call program main_mode call program main_context END DIALOG main, label("parqit collapse - Make a dataset of summary statistics, frequencies, or a pivot table; reshape") /// tabtitle("Main") BEGIN RADIO rb_collapse _lft _top 130 ., /// first /// onclickon(program main_mode) /// label("Collapse") RADIO rb_pivot 150 @ 130 ., /// onclickon(program main_mode) /// label("Pivot table") RADIO rb_contract 290 @ 130 ., /// onclickon(program main_mode) /// label("Contract") RADIO rb_reshape 430 @ 130 ., /// last /// onclickon(program main_mode) /// label("Reshape") GROUPBOX gb_stats _lft 40 _iwd _ht9, /// label("Statistics to compute (collapse and pivot table)") TEXT tx_stat _ilft 60 120 ., /// label("Statistic") TEXT tx_val 150 @ 200 ., /// label("Of variable") TEXT tx_tgt 360 @ 140 ., /// label("Result name (optional)") BUTTON bu_pop 510 @ 80 ., /// onpush(program main_populate) /// tooltip("Populate the comboboxes with the variable names of the current view") /// label("Populate") COMBOBOX cb_stat1 _ilft _ss 120 ., /// dropdownlist /// contents(stat_list) /// default("mean") /// label("Statistic 1") COMBOBOX cb_val1 150 @ 200 ., /// dropdown /// contents(vv_list) /// label("Variable 1") EDIT ed_tgt1 360 @ 140 ., /// max(32) /// label("Result name 1") COMBOBOX cb_stat2 _ilft _ms 120 ., /// dropdownlist /// contents(stat_list) /// default("count") /// label("Statistic 2") COMBOBOX cb_val2 150 @ 200 ., /// dropdown /// contents(vv_list) /// label("Variable 2") EDIT ed_tgt2 360 @ 140 ., /// max(32) /// label("Result name 2") TEXT tx_specs _ilft _ms 200 ., /// label("Additional specifications:") EDIT ed_specs 230 @ 330 ., /// label("(stat) [target=]source ...") TEXT tx_by _lft 170 _cwd1 ., /// label("Group by, option by() (collapse; optional):") TEXT tx_rows _lft2 @ _cwd1 ., /// label("Rows of the pivot table, option rows():") COMBOBOX cb_by _lft _ss _cwd1 ., /// append /// dropdown /// contents(vv_list) /// option(by) /// label("Group by") COMBOBOX cb_rows _lft2 @ _cwd1 ., /// append /// dropdown /// contents(vv_list) /// option(rows) /// error("Rows of the pivot table") /// label("Rows of the pivot table") TEXT tx_cols _lft _ls _iwd ., /// label("Columns of the pivot table, option cols() (one variable; one output column per distinct value):") COMBOBOX cb_cols _lft _ss _vnwd ., /// dropdown /// contents(vv_list) /// option(cols) /// error("Columns of the pivot table") /// label("Columns of the pivot table") TEXT tx_cvars _lft _ls _cwd1 ., /// label("Variables to contract:") TEXT tx_freq _lft2 @ _cwd1 ., /// label("Name of frequency variable (optional; default _freq):") COMBOBOX cb_cvars _lft _ss _cwd1 ., /// append /// dropdown /// contents(vv_list) /// error("Variables to contract") /// label("Variables to contract") EDIT ed_freq _lft2 @ _vnwd ., /// max(32) /// option(freq) /// label("Name of frequency variable") TEXT tx_dir _lft _ls 120 ., /// label("Reshape to:") TEXT tx_stubs 140 @ 220 ., /// label("Base (stub) names of X_ij variables:") TEXT tx_i 370 @ 110 ., /// label("ID variables, i():") TEXT tx_j 490 @ 100 ., /// label("j() variable:") COMBOBOX cb_dir _lft _ss 120 ., /// dropdownlist /// contents(dir_list) /// default("long") /// label("Reshape to") COMBOBOX cb_stubs 140 @ 220 ., /// append /// dropdown /// contents(vv_list) /// error("Base (stub) names") /// label("Base (stub) names") COMBOBOX cb_i 370 @ 110 ., /// append /// dropdown /// contents(vv_list) /// error("ID variables") /// label("ID variables") COMBOBOX cb_j 490 @ 100 ., /// dropdown /// contents(vv_list) /// error("j() variable") /// label("j() variable") TEXT tx_note _lft _ls _iwd ., /// label("All four operations are lazy; materialise the result with parqit collect or parqit save.") TEXT tx_context _lft 415 470 ., label("Current view: refresh to check") BUTTON bu_context _rj80 @ 80 ., label("Refresh") onpush(program main_populate) END PROGRAM main_context BEGIN put "parqit _dlgcontext " put dlgname stata hidden queue clear END LIST vv_list BEGIN END LIST dir_list BEGIN long wide END LIST stat_list BEGIN mean sum count min max sd median p10 p25 p50 p75 p90 first last firstnm lastnm END PROGRAM main_mode BEGIN call main.tx_stat.disable call main.tx_val.disable call main.tx_tgt.disable call main.cb_stat1.disable call main.cb_val1.disable call main.ed_tgt1.disable call main.cb_stat2.disable call main.cb_val2.disable call main.ed_tgt2.disable call main.tx_specs.disable call main.ed_specs.disable call main.tx_by.disable call main.cb_by.disable call main.tx_rows.disable call main.cb_rows.disable call main.tx_cols.disable call main.cb_cols.disable call main.tx_cvars.disable call main.cb_cvars.disable call main.tx_freq.disable call main.ed_freq.disable call main.tx_dir.disable call main.cb_dir.disable call main.tx_stubs.disable call main.cb_stubs.disable call main.tx_i.disable call main.cb_i.disable call main.tx_j.disable call main.cb_j.disable if main.rb_collapse | main.rb_pivot { call main.tx_stat.enable call main.tx_val.enable call main.tx_tgt.enable call main.cb_stat1.enable call main.cb_val1.enable call main.ed_tgt1.enable call main.cb_stat2.enable call main.cb_val2.enable call main.ed_tgt2.enable call main.tx_specs.enable call main.ed_specs.enable } if main.rb_collapse { call main.tx_by.enable call main.cb_by.enable } if main.rb_pivot { call main.tx_rows.enable call main.cb_rows.enable call main.tx_cols.enable call main.cb_cols.enable } if main.rb_contract { call main.tx_cvars.enable call main.cb_cvars.enable call main.tx_freq.enable call main.ed_freq.enable } if main.rb_reshape { call main.tx_dir.enable call main.cb_dir.enable call main.tx_stubs.enable call main.cb_stubs.enable call main.tx_i.enable call main.cb_i.enable call main.tx_j.enable call main.cb_j.enable } END PROGRAM main_populate BEGIN call program main_context call pq_populate_error.setvalue 0 put "parqit _dlgvars " put dlgname put " vv_list" stata hidden immediate if pq_populate_error { stopbox stop "Unable to get the variable names of the current view." /// "Open a view first (User > parqit > Read data...)." } call main.cb_val1.repopulate call main.cb_val2.repopulate call main.cb_by.repopulate call main.cb_rows.repopulate call main.cb_cols.repopulate call main.cb_cvars.repopulate call main.cb_stubs.repopulate call main.cb_i.repopulate call main.cb_j.repopulate END PROGRAM main_stats_output BEGIN if main.cb_val1 { put " (" main.cb_stat1 ") " if main.ed_tgt1 { put main.ed_tgt1 "=" } put main.cb_val1 } if main.cb_val2 { put " (" main.cb_stat2 ") " if main.ed_tgt2 { put main.ed_tgt2 "=" } put main.cb_val2 } if main.ed_specs { put " " main.ed_specs } END PROGRAM command BEGIN if main.rb_collapse | main.rb_pivot { if !main.cb_val1 & !main.cb_val2 & !main.ed_specs { stopbox stop "At least one statistic with a variable (or an additional specification) must be given." } } if main.rb_collapse { put "parqit collapse" put /program main_stats_output beginoptions optionarg main.cb_by endoptions } if main.rb_pivot { require main.cb_rows require main.cb_cols put "parqit pivot" put /program main_stats_output beginoptions optionarg main.cb_rows optionarg main.cb_cols endoptions } if main.rb_contract { require main.cb_cvars put "parqit contract " main.cb_cvars beginoptions optionarg main.ed_freq endoptions } if main.rb_reshape { require main.cb_stubs require main.cb_i require main.cb_j put "parqit reshape " main.cb_dir " " main.cb_stubs beginoptions put "i(" main.cb_i ") " put "j(" main.cb_j ")" endoptions } END