State renewable capacity shares from CEA's installed capacity file

The source

CEA's installed capacity report page lists a monthly PDF and an Excel workbook. We used IC_allocation_as_on_31.08.2026.xlsx, capacity as on 31 August 2026. It has two sheets:

• Summary: all-India capacity by fuel, with a percentage share. On 31 August 2026 the total was 554,544.23 MW. Solar was 168,040.05 MW (30.3%), and total non-fossil capacity was 304,334.36 MW (54.9%).
• IC: region tables, then state tables. Each state has four rows (State, Private, Central, Sub-Total) and columns Coal, Lignite, Gas, Diesel, Thermal Total, Nuclear, Hydro, RES*(MNRE), Renewable Total and Grand Total, all in MW.

The state tables carry the heading "including allocated shares in joint & central sector utilities". The notes say central allocations were updated to 31 August 2026. They also say that some long-outage coal, gas, diesel and nuclear capacity was "removed temporarily", with dates, and would be added back if those units generate again.

Some real values for NCR states, from that file:

Simple arithmetic on those rows gives Delhi a renewable share of 23.2% including hydro and 7.4% on RES*(MNRE) alone. Central allocations make up 57.9% of Delhi's total, against 25.6% for Haryana and 29.9% for UP.

Why an employer would care

Energy consultancies, renewable developers, discom planning teams and ESG analysts all compare states on capacity mix. A common error is calling a state "23% renewable" when most of that is its share of hydro stations elsewhere. An analyst who separates ownership from location, and states the renewable definition, produces a comparison that survives a client's engineer reading it.

Cleaning issues

1. 1Merged header cells. The header spans three rows (a mode group, Thermal/Renewable, then the fuel). Build column names by hand once and assert them.
2. 2Blank state name on continuation rows. Only the State row carries the name. Forward-fill it.
3. 3Region and state tables share the same sheet. Locate the state section by its heading text, not by a fixed row number, because rows move between months.
4. 4Long decimals. Values like 1178.01436 come from allocation arithmetic. Round for display only.
5. 5Temporary removals. A month-on-month drop can be an administrative removal, not a retirement. Read the notes before charting changes.

Code on a synthetic sheet

The block builds a synthetic block laid out like the IC sheet. States are placeholders and the MW values are invented, so none of this output is a CEA figure.

import numpy as np
import pandas as pd

cols = ["state", "owner", "coal", "lignite", "gas", "diesel", "thermal_total",
        "nuclear", "hydro", "res_mnre", "renewable_total", "grand_total"]
raw = pd.DataFrame([
    ["State P", "State",     1200, 0, 300, 0, 1500,   0,  100,  200,  300, 1800],
    [np.nan,    "Private",    500, 0,   0, 0,  500,   0,    0, 1400, 1400, 1900],
    [np.nan,    "Central",   2000, 0, 150, 0, 2150, 120,  900,   10,  910, 3180],
    [np.nan,    "Sub-Total", 3700, 0, 450, 0, 4150, 120, 1000, 1610, 2610, 6880],
    ["State Q", "State",     4000, 0,   0, 0, 4000,   0,  300,   50,  350, 4350],
    [np.nan,    "Private",   3000, 0,   0, 0, 3000,   0,  200, 2500, 2700, 5700],
    [np.nan,    "Central",   1500, 0, 200, 0, 1700, 200,  600,  100,  700, 2600],
    [np.nan,    "Sub-Total", 8500, 0, 200, 0, 8700, 200, 1100, 2650, 3750, 12650],
], columns=cols)
raw["state"] = raw["state"].ffill()

# Integrity: owner rows must add to Sub-Total, and components to Grand Total
parts = raw[raw["owner"] != "Sub-Total"].groupby("state")[cols[2:]].sum()
sub = raw[raw["owner"] == "Sub-Total"].set_index("state")[cols[2:]]
assert (parts - sub).abs().max().max() < 0.01
assert ((sub["thermal_total"] + sub["nuclear"] + sub["renewable_total"]) - sub["grand_total"]).abs().max() < 0.01

out = pd.DataFrame({
    "re_share_incl_hydro_pct": 100 * sub["renewable_total"] / sub["grand_total"],
    "re_share_mnre_only_pct": 100 * sub["res_mnre"] / sub["grand_total"],
    "central_allocation_pct": 100 * raw[raw["owner"] == "Central"].set_index("state")["grand_total"] / sub["grand_total"],
})
# Renewable share of capacity the state or private developers own (excludes central allocation)
own = raw[raw["owner"].isin(["State", "Private"])].groupby("state")[["renewable_total", "grand_total"]].sum()
out["re_share_own_capacity_pct"] = 100 * own["renewable_total"] / own["grand_total"]
print(out.round(1).to_string())

The two assertions do real work on the actual file: if a parsed column is shifted by one, they fail at once. The last column answers a different question from the first. It asks how green the capacity is that the state or private developers own, leaving out what the state buys by allocation.

Analysis plan

• Parse the state section for every state, forward-fill names and assert both integrity checks.
• Compute the four measures in the code for all states, then highlight Delhi, Haryana and UP.
• Download the same workbook for the previous twelve months. Chart each NCR state's RES*(MNRE) capacity over time and annotate months with removals or additions listed in the notes.
• Compare the all-India Summary sheet with the sum of the region tables and record any gap.

Deliverables

• Stacked horizontal bars: each state's capacity by owner (State, Private, Central), with the renewable part shaded.
• Dot plot: renewable share under both definitions for every state, sorted.
• Monthly line: RES*(MNRE) MW for the three NCR states, with note markers.
• README outline: file name and "as on" date, sheet and section located by heading, column mapping, the two renewable definitions, the allocation caveat, removal notes, integrity check results.

Interview questions

• Why does a state's Central row not tell you where power stations are located?
• Which renewable definition would you use for a policy brief, and why?
• Capacity fell in a month. How do you tell a retirement from a temporary removal?
• Installed capacity is not generation. What data would you add to talk about renewable energy supplied?

Structured parsing of official workbooks is core to our Data Analyst programme. For a related NCR power project, see Delhi's peak load by discom.