What the poor vs rich eat — food basket by quintile

175 Item_Codes → 10 groups (sub-totals excluded) · quintile from raw MPCE · Report §3.2.5 Table 3.5 p30.

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

-- Per-HH MPCE: same CTE AS P99 above  (AppxB 3.5)
-- Quintile cut-offs: weighted P20 / P40 / P60 / P80 of MPCE — AppxB 3.6
-- Food mapping — sub-totals 129,159,169,179,189,199,239,269,309 excluded (§1.9)
--   001-068,101-139 → Cereals, 140-159 → Pulses, … (10 groups)
SELECT "Item_Code",
       SUM("Total_Consumption_Value")  AS value
  FROM "level_05_sec_5_6"
 GROUP BY "FSU_Serial_No", "Sample_Household_No", "Sample_SU_No",
          "Second_Stage_Stratum_No", "Item_Code";

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

Quintile = weighted P20/40/60/80 of raw mpce. Share per quintile = Σ(Value group)/Σ(Value all food)×100, no other scaling.

Processing & manipulations — full transparency

Every step after SQL. No hidden transforms.

WHAT WE DO — step by step

1 Python: map each HH to quintile, for each (quintile, group) Σ(Value×? no MULT — food value already household-level).
2 Weighted share = Σ(Value)/Σ(Value in quintile).
3 Level_05 values are field-imputed ex-farm/retail (§1.9), no modal KDE added.
4 Excludes sub-total Item_Codes.

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).