Where you sit — P1-P99 MPCE

Raw MPCE per Report 592 AppxB 3.5.0-3.5.3 · Section I (without free-item imputation) · transparent replication.

Visualisation

Weighted P1–P99 (log scale). Dashed grey = P50 median. Hover moves crosshair.

Loading…

Data used

Exact rows behind the viz — full width.

—

Raw SQL — exactly as executed

DuckDB over Parquet · copy-paste runnable in duckdb.

SQL — DuckDB · Parquetcopy-paste runnable

-- 1) Monthly expenditure per HH — AppxB 3.5.2 pB11  TE = E1 + (E2/P2)·P1 + (E3/P3)·P1
-- E1  Food 7-day → 30-day   §1.5 Cat I
SELECT  "FSU_Serial_No", "Sample_Household_No", "Sample_SU_No", "Second_Stage_Stratum_No",
        SUM("Total_Consumption_Value") * 30.0/7  AS E1
  FROM "level_05_sec_5_6"   -- + "level_06" if present
 GROUP BY 1,2,3,4;

-- E2  Consumables & Services 30-day  §1.5 Cat II
SELECT  "FSU_Serial_No", "Sample_Household_No", "Sample_SU_No", "Second_Stage_Stratum_No",
        SUM("VALUE") * 1.0              AS E2
  FROM "level_08" / "level_09" / "level_10"
 GROUP BY 1,2,3,4;

-- E3  Durables 365-day → 30-day  §1.5 Cat III
SELECT  "FSU_Serial_No", "Sample_Household_No", "Sample_SU_No", "Second_Stage_Stratum_No",
        SUM("VALUE") * 30.0/365         AS E3
  FROM "level_12" / "level_13" / "level_14"
 GROUP BY 1,2,3,4;

-- P1/P2/P3  Household size per visit — §2.2.0 p13 / AppxB 3.5.0
SELECT "FSU_Serial_No", "Sample_Household_No", "Sample_SU_No", "Second_Stage_Stratum_No",
       "HOUSEHOLD_SIZE" AS P1  FROM "level_15_section_1_1_a2_b2_c2" WHERE VISIT='1';  -- P2 VISIT='2', P3 VISIT='3'

-- MULT  Third-visit multiplier & sector — §2.2.0 p13
SELECT "FSU_Serial_No", "Sample_Household_No", "Sample_SU_No", "Second_Stage_Stratum_No",
       "MULTIPLIER", "Sector"  FROM "level_15_section_1_1_a2_b2_c2" WHERE VISIT='3';

TE=E₁+(E₂ / P₂) × P₁+(E₃ / P₃) × P₁AppxB 3.5.2 pB11

MPCE=TE / P₁3.5.3· keep only HH where P₁, P₂, P₃ all present (§2.2.0)

E₁: Food — 7-day recall → × 30/7 to monthly — from level_05/06 (§1.5 Cat I)
E₂: Consumables & Services — 30-day recall → × 1 — from level_08/09/10 (§1.5 Cat II)
E₃: Durables — 365-day recall → × 30/365 — from level_12/13/14 (§1.5 Cat III)
P₁ P₂ P₃: Household size at FDQ / CSQ / DGQ canvass — level_15 VISIT 1/2/3 → HOUSEHOLD_SIZE

TE=E1+(E2/P2)*P1+(E3/P3)*P1 (AppxB 3.5.2 pB11), MPCE=TE/P1 (3.5.3). Keep only HH where P1,P2,P3 all present (§2.2.0).

Processing & manipulations — full transparency

Every step after SQL. No hidden transforms.

WHAT WE DO — step by step

1 Python after SQL: for each HH, TE as above, mpce=TE/P1 (>0 only).
2 No KDE, no Census calibration, no CPI.
3 Then weighted percentiles per §3.6: sort by mpce, cum=ΣMULT, Pk = first mpce where cum ≥ k%×ΣMULT (k=1..99), weighted by raw MULTIPLIER (uncalibrated).
4 Report published 4122/6996 uses calibrated MULT (AppxB B13 N/n×D1×H/h → Census 2011, censusindia.gov.in) + §2.3 KDE free 061-075 + CPI(R/U) mospi.gov.in.
5 Our raw shows ~2793/5146 — delta is calibration+KDE+CPI, not a bug.

WHAT WE DO NOT DO — report does, we intentionally skip

Weight calibration N/n × D1 × H/h → Census 2011 — AppxB pB13 4.0 & B9–B10. Source: censusindia.gov.in projected Sector×State×SSS.
Free-item imputation (KDE) §2.3 p15–16 — modal Sector×State via Gaussian KDE, 061–075 + PDS 7 + durables 600–623. Would lift to Sec II 4247 / 7078.
CPI deflation Table 3.3 note(ii) p25 — CPI(R)/CPI(U) to 2011-12. Source: mospi.gov.in / RBI DBIE.

Hence raw ~2793 / 5146 vs published 4122 / 6996 is documented, not hidden. No outlier trimming, no re-weight; filters only HOUSEHOLD_SIZE > 0, MPCE > 0 and P1 & P2 & P3 all present (§2.2.0 — incomplete visits dropped).

How to replicate the published figures — sources

Exact pinpoints in NSS Report 592 (HCES 2023-24). Raw + external inputs.

Pinpoint	Defines	Data source
§1.5 p4–5	MMRP — 7d (Cat I) / 30d (Cat II) / 365d (Cat III) reference periods	survey
§1.9 p6–7	Home produce & gifts — ex-farm / retail valuation	field
§2.2 p13	All 3 visits required — `§2.2.0` drop if incomplete	survey
AppxB pB11	`TE = E1+(E2/P2)P1+(E3/P3)P1` & `MPCE = TE/P1` — `3.5.2–3.5.3`	survey
AppxB pB13	`4.0` Weight `N/n × D1 × H/h` then calibrated — gap to published	censusindia.gov.in
§2.3 p15–16	KDE Gaussian modal price — free `061–075` + PDS 7	survey + KDE
§3.2.5 p30	Table 3.5 — food / non-food share breakdown	derived
T3.3 p25	`note(ii)` CPI(R)/CPI(U) → 2011-12 price	mospi.gov.in

Amber rows = external inputs not in Parquet — add them to go from raw ~2793/5146 to published 4122/6996 (Sec I) and 4247/7078 (Sec II).