# Charting and data analysis (XLSX)

Use the **openpyxl chart API** for all embedded Excel charts. Never hand-craft chart XML — it is fragile and underdocumented.

## Chart skeleton

Every chart follows the same pattern: create chart object → define data and category references → add data → set categories → anchor to a cell.

```python
from openpyxl.chart import BarChart, Reference

chart = BarChart()
chart.title = "Revenue by Region"
chart.y_axis.title = "Revenue ($)"
chart.x_axis.title = "Region"

data = Reference(ws, min_col=2, min_row=1, max_col=2, max_row=10)
cats = Reference(ws, min_col=1, min_row=2, max_row=10)

chart.add_data(data, titles_from_data=True)
chart.set_categories(cats)

chart.width = 18   # cm
chart.height = 12  # cm

ws.add_chart(chart, "D2")   # anchor cell
```

## Chart types

### Bar / Column

```python
from openpyxl.chart import BarChart

chart = BarChart()
chart.type = "col"       # vertical bars (default)
# chart.type = "bar"     # horizontal bars
chart.grouping = "clustered"   # or "stacked", "percentStacked"
```

### Line

```python
from openpyxl.chart import LineChart

chart = LineChart()
chart.style = 10   # built-in style number

# Add markers
series = chart.series[0]
series.graphicalProperties.line.width = 20000   # EMUs
series.marker.symbol = "circle"
series.marker.size = 5
```

### Pie

```python
from openpyxl.chart import PieChart
from openpyxl.chart.label import DataLabelList

chart = PieChart()
data = Reference(ws, min_col=2, min_row=1, max_row=6)
cats = Reference(ws, min_col=1, min_row=2, max_row=6)
chart.add_data(data, titles_from_data=True)
chart.set_categories(cats)

# Data labels with percentages
chart.dataLabels = DataLabelList()
chart.dataLabels.showPercent = True
chart.dataLabels.showCatName = True
chart.dataLabels.showVal = False
```

### Scatter

```python
from openpyxl.chart import ScatterChart
from openpyxl.chart.series import DataPoint

chart = ScatterChart()
chart.x_axis.title = "X"
chart.y_axis.title = "Y"

x_values = Reference(ws, min_col=1, min_row=2, max_row=50)
y_values = Reference(ws, min_col=2, min_row=2, max_row=50)

series = chart.series[0] if chart.series else None
# For scatter, use Series directly:
from openpyxl.chart import Series
s = Series(y_values, x_values, title="Data Points")
chart.series.append(s)
```

### Area

```python
from openpyxl.chart import AreaChart

chart = AreaChart()
chart.grouping = "stacked"    # or "standard", "percentStacked"
```

### Combo chart (two axes)

Overlay two chart types by creating a second chart and appending it:

```python
from openpyxl.chart import BarChart, LineChart, Reference

bar = BarChart()
bar.title = "Revenue and Growth Rate"
bar_data = Reference(ws, min_col=2, min_row=1, max_row=13)
bar.add_data(bar_data, titles_from_data=True)
bar.y_axis.title = "Revenue ($)"

line = LineChart()
line_data = Reference(ws, min_col=3, min_row=1, max_row=13)
line.add_data(line_data, titles_from_data=True)
line.y_axis.axId = 200      # secondary axis ID
line.y_axis.title = "Growth (%)"
line.y_axis.crosses = "max"  # right side

# Overlay line on bar
bar += line

cats = Reference(ws, min_col=1, min_row=2, max_row=13)
bar.set_categories(cats)
ws.add_chart(bar, "E2")
```

## Multi-series

Add multiple data columns in one call or loop:

```python
# All columns 2-5 as series, row 1 has titles
data = Reference(ws, min_col=2, max_col=5, min_row=1, max_row=20)
chart.add_data(data, titles_from_data=True)
```

Or add series individually for finer control:

```python
for col in range(2, 6):
    data = Reference(ws, min_col=col, min_row=1, max_row=20)
    chart.add_data(data, titles_from_data=True)
```

## Chart styling

### Series colors

```python
from openpyxl.chart.series import DataPoint
from openpyxl.drawing.fill import PatternFillProperties, ColorChoice

series = chart.series[0]
series.graphicalProperties.solidFill = "4472C4"   # hex color
```

### Axis formatting

```python
# Number format on Y axis
chart.y_axis.numFmt = '"$"#,##0'

# Min / max
chart.y_axis.scaling.min = 0
chart.y_axis.scaling.max = 100000

# Hide gridlines
chart.y_axis.majorGridlines = None

# Axis title
chart.y_axis.title = "Amount ($)"
```

### Legend

```python
from openpyxl.chart.layout import Layout, ManualLayout
from openpyxl.chart.legend import Legend

chart.legend.position = "b"   # "b" bottom, "t" top, "l" left, "r" right, "tr" top-right
# chart.legend = None          # hide legend entirely
```

### Data labels

```python
from openpyxl.chart.label import DataLabelList

chart.dataLabels = DataLabelList()
chart.dataLabels.showVal = True
chart.dataLabels.numFmt = '#,##0'
# chart.dataLabels.showPercent = True   # for pie charts
```

### Chart size

```python
chart.width = 20    # cm (default ~15)
chart.height = 14   # cm (default ~7.5)
```

## Failure modes

These are the most common causes of corrupt or broken charts:

1. **Data must be in contiguous cell ranges.** `Reference()` defines a rectangle. If your data is scattered, consolidate it into a contiguous block first, then chart that block.

2. **`titles_from_data=True`** means the **first row** of the data reference is treated as the series name, not as data. If your data range starts at row 1 and row 1 is the header, set this to `True`. If your data range starts at row 2 (after the header), set it to `False`.

3. **Deleting rows/columns that a chart references corrupts the file.** Always update or recreate charts after structural changes to the data range.

4. **Charts are anchored to cells.** Inserting rows above the anchor shifts the chart down. If you build data then chart, add the chart **after** all data is written.

5. **Category reference should NOT include the header row.** Categories start at row 2 (the first data row), while data starts at row 1 (including the header for the series title).

6. **One chart per `add_chart()` call.** You cannot reuse a chart object on multiple sheets — create a new chart for each location.

7. **Combo chart axis IDs must differ.** When overlaying line on bar, set `line.y_axis.axId = 200` (any number different from the primary axis default).

## Data analysis patterns

### Summary statistics sheet

```python
import pandas as pd
from openpyxl.utils.dataframe import dataframe_to_rows

df = pd.read_excel("data.xlsx")
stats = df.describe().T   # transpose: columns become rows
stats["missing"] = df.isnull().sum()

ws = wb.create_sheet("Summary Stats")
for r in dataframe_to_rows(stats, index=True, header=True):
    ws.append(r)
```

### Frequency distribution

```python
counts = df["category"].value_counts()
ws = wb.create_sheet("Frequency")
ws.append(["Category", "Count"])
for cat, count in counts.items():
    ws.append([cat, count])

# Add a bar chart of the distribution
chart = BarChart()
chart.title = "Category Distribution"
data = Reference(ws, min_col=2, min_row=1, max_row=len(counts)+1)
cats = Reference(ws, min_col=1, min_row=2, max_row=len(counts)+1)
chart.add_data(data, titles_from_data=True)
chart.set_categories(cats)
ws.add_chart(chart, "D2")
```

### Top-N / Bottom-N extraction

```python
top10 = df.nlargest(10, "revenue")
ws = wb.create_sheet("Top 10")
for r in dataframe_to_rows(top10, index=False, header=True):
    ws.append(r)
```

### Correlation matrix

```python
corr = df.select_dtypes(include="number").corr()
ws = wb.create_sheet("Correlations")
for r in dataframe_to_rows(corr, index=True, header=True):
    ws.append(r)

# Apply color scale conditional formatting
from openpyxl.formatting.rule import ColorScaleRule
data_range = f"B2:{ws.cell(row=len(corr)+1, column=len(corr.columns)+1).coordinate}"
ws.conditional_formatting.add(
    data_range,
    ColorScaleRule(
        start_type="num", start_value=-1, start_color="F8696B",
        mid_type="num", mid_value=0, mid_color="FFFFFF",
        end_type="num", end_value=1, end_color="63BE7B",
    ),
)
```

---

For chart data best practices (formulas vs static), see `spreadsheet_practices.md`.
