Analysis of Form N-CEN - Annual Reports by Investment Companies

%pip install -q sec_api # use %pip for reliable install in current environment

from sec_api import FormNcenApi
import json

formNcenApi = FormNcenApi(api_key=API_KEY_SEC_API)

search_params = {
    "query": "entities.cik:311635",
    "from": "0",
    "size": "1",
    "sort": [{"filedAt": {"order": "desc"}}],
}

# get proposed sales information, sales history, and other details
response = formNcenApi.get_data(search_params)
filing = response["data"]

print(json.dumps(filing, indent=2))

import sys
import os
import time
import random

# from multiprocessing import Pool # use in .py files only
from concurrent.futures import ThreadPoolExecutor

YEARS = range(2025, 2017, -1)  # from 2025 to 2018
TEMP_FILE_TEMPLATE = "./temp_file_form_ncen_{}.jsonl"
TARGET_FILE = "./form-ncen-dataset.jsonl.gz"


def process_year(year):
    backoff_time = random.randint(10, 800) / 1000
    print(f"Starting year {year} with backoff time {backoff_time:,}s")
    sys.stdout.flush()
    time.sleep(backoff_time)

    tmp_filename = TEMP_FILE_TEMPLATE.format(year)
    tmp_file = open(tmp_filename, "a")

    for month in range(12, 0, -1):
        search_from = 0
        month_counter = 0

        while True:
            query = f"filedAt:[{year}-{month:02d}-01 TO {year}-{month:02d}-31]"
            searchRequest = {
                "query": query,
                "from": search_from,
                "size": "50",
                "sort": [{"filedAt": {"order": "desc"}}],
            }

            response = None
            try:
                response = formNcenApi.get_data(searchRequest)
            except Exception as e:
                print(f"{year}-{month:02d} error: {e}")
                sys.stdout.flush()
                continue

            if response == None or len(response["data"]) == 0:
                break

            search_from += 50
            month_counter += len(response["data"])
            jsonl_data = "\n".join([json.dumps(entry) for entry in response["data"]])
            tmp_file.write(jsonl_data + "\n")

        print(f"Finished loading {month_counter} filings for {year}-{month:02d}")
        sys.stdout.flush()

    tmp_file.close()

    return year


if not os.path.exists(TARGET_FILE):
    with ThreadPoolExecutor(max_workers=4) as pool:
        processed_years = list(pool.map(process_year, YEARS))
    print("Finished processing all years.", processed_years)

    import gzip

    # Merge the temporary files into one final compressed file
    with gzip.open(TARGET_FILE, "wt", encoding="utf-8") as outfile:
        for year in YEARS:
            temp_file = TEMP_FILE_TEMPLATE.format(year)
            if os.path.exists(temp_file):
                with open(temp_file, "r", encoding="utf-8") as infile:
                    for line in infile:
                        outfile.write(line)  # Preserve JSONL format
else:
    print("File already exists. Skipping download.")

# install all dependencies required for the notebook
%pip install -q pandas numpy matplotlib seaborn scipy

structured_data = pd.read_json(TARGET_FILE, lines=True)
structured_data = pd.json_normalize(structured_data.to_dict(orient="records"))

structured_data["filedAt"] = pd.to_datetime(structured_data["filedAt"], utc=True)
structured_data["filedAt"] = structured_data["filedAt"].dt.tz_convert("US/Eastern")
structured_data = structured_data.sort_values("filedAt", ascending=True).reset_index(
    drop=True
)

structured_data.drop_duplicates("accessionNo", keep="first", inplace=True)
structured_data["year"] = structured_data["filedAt"].dt.year
structured_data["month"] = structured_data["filedAt"].dt.month
structured_data["qtr"] = structured_data["month"].apply(lambda x: (x - 1) // 3 + 1)
structured_data["dayOfWeek"] = structured_data["filedAt"].dt.day_name()
# filedAtClass: preMarket (4:00AM-9:30AM), regularMarket (9:30AM-4:00PM), afterMarket (4:00PM-8:00PM)
structured_data["filedAtClass"] = structured_data["filedAt"].apply(
    lambda x: (
        "preMarket"
        if x.hour < 9 or (x.hour == 9 and x.minute < 30)
        else (
            "regularMarket"
            if x.hour < 16
            else "afterMarket" if x.hour < 20 else "other"
        )
    )
)
structured_data["periodOfReportYear"] = structured_data["periodOfReport"].apply(
    lambda x: x.split("-")[0] if isinstance(x, str) else np.nan
)
structured_data["periodOfReportMonth"] = structured_data["periodOfReport"].apply(
    lambda x: x.split("-")[1] if isinstance(x, str) else np.nan
)

structured_data.head()

# NCEN filings have only one entity related to the filing,
# meaning the investment company files the form themselves
# we can therefore extract the cik, company name and other information to the dataset
entity_count = (
    structured_data["entities"]
    .apply(lambda x: len(x) if isinstance(x, list) else 0)
    .value_counts()
    .keys()
)
if len(entity_count) > 1:
    print("Warning: Some filings have more than one entity related to the filing. ")
    print("Please check the data.")
else:
    print("All filings have only one entity related to the filing. ")
    print("Extracting the cik, company name and other information to the dataset.")
    flattened_entity = pd.json_normalize(
        structured_data["entities"].apply(
            lambda x: x[0] if isinstance(x, list) and len(x) > 0 else {}
        ),
    )
    flattened_entity.columns = ["filer." + col for col in flattened_entity.columns]

    structured_data = pd.concat(
        [structured_data.drop(columns=["entities"]), flattened_entity],
        axis=1,
    )

unique_years = structured_data["year"].nunique()
unique_ciks = structured_data["filer.cik"].nunique()
unique_filings = structured_data["accessionNo"].nunique()
min_year = structured_data["year"].min()
max_year = structured_data["year"].max()
max_year_full = max_year - 1  # to avoid incomplete data for the current year
print(f"Loaded dataframe with main documents of {form_name} filings")
print(f"Number of filings: {unique_filings:,}")
print(f"Number of records: {len(structured_data):,}")
print(f"Number of years: {unique_years:,} ({min_year}-{max_year})")
# print(f"Number of unique companies: {unique_companies:,}")
print(f"Number of unique ciks: {unique_ciks:,}")

structured_data.info()

def plot_timeseries(
    ts,
    title,
    keys=["count"],
    y_label=None,
    ylog_scale=False,
    yvalue_multiplier=1,
    yvalue_multiplier_name=None,
    ax=None,
):
    fig = None
    if ax is None:
        fig, ax = plt.subplots(figsize=(4, 2.5))
    else:
        fig = ax.figure

    # Auto y-label with unit
    if y_label is None:
        y_label = keys[0] if len(keys) == 1 else "Value"
    if yvalue_multiplier == "auto":
        yvalue_multiplier = 1
        max_value = ts[keys].max().max()
        if max_value > 1_000_000_000_000:
            yvalue_multiplier = 1_000_000_000_000
        elif max_value > 1_000_000_000:
            yvalue_multiplier = 1_000_000_000
        elif max_value > 1_000_000:
            yvalue_multiplier = 1_000_000
        elif max_value > 1_000:
            yvalue_multiplier = 1_000

    if yvalue_multiplier != 1:
        if yvalue_multiplier_name is not None:
            y_label = f"{y_label} ({yvalue_multiplier_name})"
        elif yvalue_multiplier == 1_000:
            y_label += " (Thousands)"
        elif yvalue_multiplier == 1_000_000:
            y_label += " (Millions)"
        elif yvalue_multiplier == 1_000_000_000:
            y_label += " (Billions)"
        elif yvalue_multiplier == 1_000_000_000_000:
            y_label += " (Trillions)"
        else:
            y_label += f" (÷{yvalue_multiplier:,})"

    # Apply multiplier
    ts_plot = ts.copy()
    ts_plot[keys] = ts_plot[keys] / yvalue_multiplier

    # Plot each key
    for key in keys:
        ts_plot[key].plot(ax=ax, label=key.capitalize())

    if ylog_scale:
        ax.set_yscale("log")

    ax.set_title(title)
    ax.set_xlabel("Year")
    ax.set_ylabel(y_label)
    ax.set_xticks(np.arange(min_year, max_year + 1, 1))
    # ax.set_xlim(min_year - 1, max_year + 1)
    ax.grid(axis="x")
    ax.set_axisbelow(True)
    # ax.yaxis.set_major_formatter(mtick.StrMethodFormatter("{x:,.0f}"))

    # Add vertical lines at each year, height = max across all keys
    max_vals = ts_plot[keys].max(axis=1)
    for year in ts_plot.index:
        val = max_vals.get(year, 0)
        if not pd.isna(val):
            ax.vlines(year, 0, val, linestyles=":", colors="grey", alpha=0.5, lw=1)

    plt.xticks(rotation=45, ha="right")
    if len(keys) > 1:
        ax.legend()

    plt.tight_layout()
    return fig, ax


filing_counts = (
    structured_data.drop_duplicates(subset=["accessionNo"])
    .groupby(["year"])
    .size()
    .to_frame(name="count")
)

plot_timeseries(
    filing_counts,
    title=f"{form_name} Filings as XML submission per Year ({min_year} - {max_year})",
)
plt.show()

count_formType = (
    structured_data.drop_duplicates(subset=["accessionNo"])
    .groupby(["formType"])
    .size()
    .sort_values(ascending=False)
    .to_frame(name="Count")
).rename_axis("Submission Type")
count_formType["Pct"] = (
    count_formType["Count"].astype(int) / count_formType["Count"].astype(int).sum()
).map("{:.0%}".format)
count_formType["Count"] = count_formType["Count"].map(lambda x: f"{x:,}")

print(f"{form_name} Disclosures by Submission Type ({min_year} - {max_year})")
count_formType

form_counts_by_type_and_year = (
    structured_data.drop_duplicates(subset=["accessionNo"])
    .groupby(["year", "formType"])
    .size()
    .to_frame(name="count")
    .unstack(fill_value=0)
)

form_counts_by_type_and_year.loc["Total"] = form_counts_by_type_and_year.sum()
form_counts_by_type_and_year["Total"] = form_counts_by_type_and_year.sum(axis=1)


print(f"{form_name} counts from {min_year} to {max_year}.")
form_counts_by_type_and_year

fig, ax = plt.subplots(figsize=(4, 2.5))
form_counts_by_type_and_year["count"].drop("Total").plot(
    kind="bar", stacked=True, ax=ax
)
ax.set_xlabel("Year")
ax.set_ylabel("Number of Filings")
ax.yaxis.set_major_formatter(mtick.StrMethodFormatter("{x:,.0f}"))
ax.grid(axis="x")
ax.set_axisbelow(True)
handles, labels = ax.get_legend_handles_labels()
ax.legend(
    list(reversed(handles)),
    list(reversed(labels)),
    title="Form Type",
    labelspacing=0.15,
)
ax.set_title(f"{form_name} Filings by Form Type per Year ({min_year} - {max_year})")
plt.show()

counts_qtr_yr_piv = (
    structured_data.groupby(["year", "qtr"]).size().unstack().fillna(0)
).astype(int)

print(f"{form_name} counts by quarter from {min_year} to {max_year}.")
counts_qtr_yr_piv.T

plt.figure(figsize=(4, 2))
sns.heatmap(
    counts_qtr_yr_piv.T,
    annot=True,  # Display the cell values
    fmt="d",  # Integer formatting
    cmap="magma",  # Color map
    cbar_kws={"label": "Count"},  # Colorbar label
    mask=counts_qtr_yr_piv.T == 0,  # Mask the cells with value 0
    cbar=False,
    annot_kws={"fontsize": 7},
)
plt.grid(False)
plt.title(f"{form_name} Counts by Quarter {min_year} to {max_year_full}")
plt.xlabel("Year")
plt.ylabel("Quarter")
plt.tight_layout()
plt.show()

form_types = count_formType.index.tolist()

fig, axes = plt.subplots(1, 2, figsize=(6, 2))

cnt = 0
for formType in form_types:
    data = (
        structured_data[structured_data["formType"] == formType]
        .groupby(["year", "qtr"])
        .size()
        .unstack()
        .fillna(0)
        .astype(int)
        .reindex(columns=range(1, 5), fill_value=0)  # ensure all month are included
    )

    filing_name = formType
    # if data.sum().sum() < 100:
    #     continue

    ax = axes.flatten()[cnt]

    sns.heatmap(
        data.T,
        ax=ax,
        annot=True,  # Display the cell values
        fmt="d",  # Integer formatting
        cmap="magma",  # Color map
        cbar_kws={"label": "Count"},  # Colorbar label
        mask=data.T == 0,  # Mask the cells with value 0
        cbar=False,
        annot_kws={"fontsize": 7},
    )
    ax.grid(False)
    ax.set_title(f"{filing_name} Counts")
    ax.set_xlabel("Year")
    ax.set_ylabel("Quarter")

    cnt += 1

fig.suptitle(f"{form_name} Filing Subtype Counts by Quarter {min_year} to {max_year}")
plt.tight_layout()

counts_qtr_yr = counts_qtr_yr_piv.stack().reset_index(name="count")

fig, ax = plt.subplots(figsize=(4, 2.5))
counts_qtr_yr_piv.plot(kind="bar", ax=ax, legend=True)
ax.legend(title="Quarter", loc="upper right", bbox_to_anchor=(1.15, 1))
ax.set_title(f"Number of {form_name} Filings per Quarter\n({min_year}-{max_year})")
ax.set_xlabel("Year")
ax.set_ylabel(f"Number of\n{form_name} Filings")
ax.yaxis.set_major_formatter(mtick.StrMethodFormatter("{x:,.0f}"))
ax.grid(axis="x")
ax.set_axisbelow(True)
plt.tight_layout()
plt.show()

counts_month_yr_piv = (
    structured_data.groupby(["year", "month"]).size().unstack().fillna(0)
).astype(int)

plt.figure(figsize=(5, 2))
sns.heatmap(
    counts_month_yr_piv,
    annot=True,
    fmt="d",
    cmap="magma",
    cbar_kws={"label": "Count"},
    mask=counts_month_yr_piv == 0,
    cbar=False,
    annot_kws={"size": 7},
)
# convert x-labels to month names: 1 => Jan, 2 => Feb, etc.
plt.xticks(
    ticks=np.arange(0.5, 12.5, 1),
    labels=[pd.to_datetime(str(i), format="%m").strftime("%b") for i in range(1, 13)],
)
plt.grid(False)
plt.title(f"{form_name} Counts by Month ({min_year} - {max_year_full})")
plt.xlabel("")
plt.ylabel("Year")
plt.tight_layout()
plt.show()

print(
    f"Descriptive statistics for {form_name} filing counts by month from {min_year} to {max_year}."
)
month_stats = (
    counts_month_yr_piv.loc[2004:]
    .describe(percentiles=[0.025, 0.975])
    .round(0)
    .astype(int)
)
month_stats

def plot_box_plot_as_line(
    data: pd.DataFrame,
    x_months=True,
    title="",
    x_label="",
    x_pos_mean_label=2,
    pos_labels=None,
    pos_high_low=None,
    y_label="",
    y_formatter=lambda x, p: "{:.0f}".format(int(x) / 1000),
    show_high_low_labels=True,
    show_inline_labels=True,
    show_bands=True,
    figsize=(4, 2.5),
    line_source="mean",
):
    fig, ax = plt.subplots(figsize=figsize)

    line_to_plot = data[line_source]
    lower_label = "2.5%"
    upper_label = "97.5%"
    lower = data[lower_label]
    upper = data[upper_label]

    line_to_plot.plot(ax=ax)

    if show_bands:
        ax.fill_between(line_to_plot.index, lower, upper, alpha=0.2)

    if x_months:
        ax.set_xlim(0.5, 12.5)
        ax.set_xticks(range(1, 13))
        ax.set_xticklabels(["J", "F", "M", "A", "M", "J", "J", "A", "S", "O", "N", "D"])

    ax.yaxis.set_major_formatter(mtick.FuncFormatter(y_formatter))
    ax.set_ylabel(y_label)
    ax.set_xlabel(x_label)
    ax.set_title(title)

    ymin, ymax = ax.get_ylim()
    y_scale = ymax - ymin

    max_x = int(line_to_plot.idxmax())
    max_y = line_to_plot.max()
    min_x = int(line_to_plot.idxmin())
    min_y = line_to_plot.min()

    ax.axvline(
        max_x,
        ymin=0,
        ymax=((max_y - ymin) / (ymax - ymin)),
        linestyle="dashed",
        color="tab:blue",
        alpha=0.5,
    )
    ax.scatter(max_x, max_y, color="tab:blue", s=10)
    ax.axvline(
        min_x,
        ymin=0,
        ymax=((min_y - ymin) / (ymax - ymin)),
        linestyle="dashed",
        color="tab:blue",
        alpha=0.5,
    )
    ax.scatter(min_x, min_y, color="tab:blue", s=10)

    x_pos_mean_label_int = int(x_pos_mean_label)
    if show_inline_labels:
        mean_x = x_pos_mean_label
        mean_y = line_to_plot.iloc[x_pos_mean_label_int] * 1.02
        upper_x = x_pos_mean_label
        upper_y = upper.iloc[x_pos_mean_label_int]
        lower_x = x_pos_mean_label
        lower_y = lower.iloc[x_pos_mean_label_int] * 0.95

        if pos_labels:
            mean_x = pos_labels["mean"]["x"]
            mean_y = pos_labels["mean"]["y"]
            upper_x = pos_labels["upper"]["x"]
            upper_y = pos_labels["upper"]["y"]
            lower_x = pos_labels["lower"]["x"]
            lower_y = pos_labels["lower"]["y"]

        ax.text(mean_x, mean_y, "Mean", color="tab:blue", fontsize=8)
        ax.text(upper_x, upper_y, upper_label, color="tab:blue", fontsize=8)
        ax.text(lower_x, lower_y, lower_label, color="tab:blue", fontsize=8)

    if show_high_low_labels:
        high_x_origin = max_x
        high_y_origin = max_y
        high_x_label = high_x_origin + 0.5
        high_y_label = high_y_origin + 0.1 * y_scale
        if pos_high_low:
            high_x_label = pos_high_low["high"]["x"]
            high_y_label = pos_high_low["high"]["y"]
        ax.annotate(
            "High",
            (high_x_origin, high_y_origin),
            xytext=(high_x_label, high_y_label),
            arrowprops=dict(facecolor="black", arrowstyle="->"),
        )

        low_x_origin = min_x * 1.01
        low_y_origin = min_y
        low_x_label = low_x_origin + 1.5
        low_y_label = low_y_origin - 0.1 * y_scale
        if pos_high_low:
            low_x_label = pos_high_low["low"]["x"]
            low_y_label = pos_high_low["low"]["y"]
        ax.annotate(
            "Low",
            (low_x_origin, low_y_origin),
            xytext=(low_x_label, low_y_label),
            arrowprops=dict(facecolor="black", arrowstyle="->"),
        )

    ax.grid(axis="x")
    ax.set_axisbelow(True)

    plt.tight_layout()
    plt.show()


plot_box_plot_as_line(
    data=month_stats.T,
    title=f"Descriptive Statistics for {form_name} Filings by Month\n({min_year} - {max_year_full})",
    x_label="Month",
    y_label="Number of\n{form_name} Filings",
    y_formatter=lambda x, p: "{:.0f}".format(int(x)),
    x_pos_mean_label=5,
)

form_types = count_formType.index.tolist()

fig, axes = plt.subplots(1, 2, figsize=(5.5, 2))

cnt = 0
for formType in form_types:

    data = (
        structured_data[structured_data["formType"] == formType]
        .groupby(["year", "month"])
        .size()
        .unstack()
        .fillna(0)
        .reindex(columns=range(1, 13), fill_value=0)  # ensure all month are included
    )

    # if data.sum().sum() < 100:
    #     continue

    ax = axes.flatten()[cnt]
    cnt += 1
    try:
        data.boxplot(
            ax=ax,
            grid=False,
            showfliers=False,
            flierprops=dict(marker="o", markersize=3),
            patch_artist=True,
            boxprops=dict(facecolor="white", color="tab:blue"),
            showmeans=True,
            meanline=True,
            meanprops={"color": "tab:blue", "linestyle": ":"},
            medianprops={"color": "black"},
            capprops={"color": "none"},
        )

        ax.set_title(f"Form {formType}")
        ax.set_xlabel("")
        ax.set_ylabel(f"Form {formType} Count")
        xticklabels = [
            pd.to_datetime(str(x), format="%m").strftime("%b") for x in range(1, 13)
        ]
        ax.set_xticklabels(xticklabels)
        ax.tick_params(axis="x", rotation=45)
    except Exception as e:
        print(f"Error: {e}")

# disable the empty subplots
for i in range(cnt, len(axes.flatten())):
    axes.flatten()[i].axis("off")

fig.suptitle(f"{form_name} Filings by Month\n({min_year} - {max_year_full})")
plt.tight_layout()
plt.show()

counts_per_month_by_formType = (
    structured_data[["year", "month", "accessionNo", "formType"]]
    .groupby(["year", "month", "formType"])
    .count()
    .rename(columns={"accessionNo": "count"})
    .pivot_table(
        index=["year", "month"],  # Rows
        columns="formType",  # Columns
        values="count",  # Values to fill
        fill_value=0,  # Replace NaN with 0
    )
    .astype(int)
    .reset_index()  # Make year and month normal columns
)

counts_per_month_by_formType

fix, ax = plt.subplots(figsize=(4, 2))

ax.stackplot(
    counts_per_month_by_formType["year"].astype(str)
    + "-"
    + counts_per_month_by_formType["month"].astype(str),
    *[counts_per_month_by_formType[ft] for ft in form_types],
    labels=[f"{ft}" for ft in form_types],
    alpha=0.8,
)
handles, labels = ax.get_legend_handles_labels()
ax.legend(
    list(reversed(handles)),
    list(reversed(labels)),
    title="Form Type",
    labelspacing=0.15,
)

ax.set_title(f"{form_name} Filings per Month")
ax.set_ylabel("Filings per Month")
xticks = (
    counts_per_month_by_formType["year"].astype(str)
    + "-"
    + counts_per_month_by_formType["month"].astype(str)
)
ax.set_xticks([i for i, x in enumerate(xticks) if x.endswith("-1")])
ax.set_xticklabels(
    [label.get_text()[:4] for label in ax.get_xticklabels()], rotation=90, ha="left"
)

ax.grid(axis="y", linestyle=":", alpha=0.5)
ax.spines["top"].set_visible(False)
ax.spines["right"].set_visible(False)
# draw vertical lines for each first month of the year, dotted, transparency 0.5,
# with height of the y value for the respective month
for year, month in counts_per_month_by_formType[["year", "month"]].values:
    if month == 1:
        ax.vlines(
            f"{year}-{month}",
            ymin=0,
            ymax=counts_per_month_by_formType[
                (counts_per_month_by_formType["year"] == year)
                & (counts_per_month_by_formType["month"] == month)
            ]
            .drop(columns=["year", "month"])
            .sum(axis=1),
            linestyle=":",
            alpha=0.5,
            color="grey",
        )

plt.show()

counts_filedAtClass = (
    (
        structured_data.drop_duplicates(subset=["accessionNo"])
        .groupby(["filedAtClass"])
        .size()
        .sort_values(ascending=False)
        .to_frame(name="Count")
    )
    .rename_axis("Publication Time")
    .sort_values("Count", ascending=True)
)
counts_filedAtClass["Pct"] = (
    counts_filedAtClass["Count"].astype(int)
    / counts_filedAtClass["Count"].astype(int).sum()
).map("{:.0%}".format)
counts_filedAtClass["Count"] = counts_filedAtClass["Count"].map(lambda x: f"{x:,}")
counts_filedAtClass.index = (
    counts_filedAtClass.index.str.replace("preMarket", "Pre-Market (4:00 - 9:30 AM)")
    .str.replace("marketHours", "Market Hours (9:30 AM - 4:00 PM)")
    .str.replace("afterMarket", "After Market (4:00 - 8:00 PM)")
)
counts_filedAtClass = counts_filedAtClass.reindex(counts_filedAtClass.index[::-1])

print(
    f"{form_name} filing counts by pre-market, regular market hours,\n"
    f"and after-market publication time ({min_year} - {max_year_full})."
)
counts_filedAtClass

counts_dayOfWeek = (
    structured_data.drop_duplicates(subset=["accessionNo"])
    .groupby(["dayOfWeek"])
    .size()
    .to_frame(name="Count")
).rename_axis("Day of the Week")
counts_dayOfWeek["Pct"] = (
    counts_dayOfWeek["Count"].astype(int) / counts_dayOfWeek["Count"].astype(int).sum()
).map("{:.0%}".format)
counts_dayOfWeek["Count"] = counts_dayOfWeek["Count"].map(lambda x: f"{x:,}")

print(f"{form_name} filing counts by day of the week ({min_year} - {max_year}).")
counts_dayOfWeek.loc[["Monday", "Tuesday", "Wednesday", "Thursday", "Friday"]]

# first reduce the following to registrantInfo.isAnyDirectorInterestedPerson
# "registrantInfo.directors.isDirectorInterestedPerson":
#   "Indicator showing if the director is considered an interested person.",
structured_data["noDirectors"] = structured_data["registrantInfo.directors"].apply(
    lambda x: (
        len(list(set([d["directorName"] for d in x])))
        if isinstance(x, list)
        else np.nan
    )
)  # number of directors
print(structured_data["noDirectors"].value_counts())

def plot_hist(
    data,
    title=None,
    x_label=None,
    y_label=None,
    log_scale=False,
    xlog_scale=False,
    ylog_scale=False,
    bins=None,
    xticks=None,
    use_xticks_formatter=False,
    use_yticks_formatter=False,
    value_multiplier=1,
    value_range=None,
    ax=None,
    integer_bins=False,
):
    if log_scale:
        xlog_scale = True
        ylog_scale = True

    data = np.array(data)

    # Filter
    if value_range is not None:
        min_val, max_val = value_range
        data = data[(data >= min_val) & (data <= max_val)]

    # Auto xvalue_multiplier
    if value_multiplier == "auto":
        value_multiplier = 1
        max_value = np.max(data)
        if max_value > 1_000_000_000_000:
            value_multiplier = 1_000_000_000_000
        elif max_value > 1_000_000_000:
            value_multiplier = 1_000_000_000
        elif max_value > 1_000_000:
            value_multiplier = 1_000_000
        elif max_value > 1_000:
            value_multiplier = 1_000

    # Apply multiplier
    data = data / value_multiplier

    # Label unit
    if value_multiplier != 1:
        if x_label is None:
            x_label = "Value"
        if value_multiplier == 1_000:
            unit = " (Thousands)"
        elif value_multiplier == 1_000_000:
            unit = " (Millions)"
        elif value_multiplier == 1_000_000_000:
            unit = " (Billions)"
        elif value_multiplier == 1_000_000_000_000:
            unit = " (Trillion)"
        else:
            unit = f" (÷{value_multiplier:,})"
        x_label += unit

    # Create figure/ax if not given
    fig = None
    if ax is None:
        fig, ax = plt.subplots(figsize=(3, 2))

    # Bins
    if bins is not None and isinstance(bins, int):
        data_range = data.max() - data.min()
        if data_range < bins and integer_bins:
            bins = np.arange(data.min() - 0.5, data.max() + 1.5, 1)
    if bins is None:
        if integer_bins:
            min_val = np.floor(data.min())
            max_val = np.ceil(data.max())
            bin_edges = np.arange(min_val - 0.5, max_val + 1.5, 1)
        elif xlog_scale:
            bin_edges = np.logspace(np.log10(data.min()), np.log10(data.max()), num=20)
        else:
            bin_edges = 20
    else:
        bin_edges = bins

    if value_multiplier != 1 and isinstance(bin_edges, np.ndarray):
        bin_edges = bin_edges / value_multiplier

    ax.hist(
        data,
        bins=bin_edges,
        color="steelblue",
        edgecolor="black",
        linewidth=0.5,
    )

    if xticks is not None:
        ax.set_xticks(xticks)
    if xlog_scale:
        ax.set_xscale("log")
        use_xticks_formatter = True
    if ylog_scale:
        ax.set_yscale("log")
        use_yticks_formatter = True
    if use_xticks_formatter:
        ax.xaxis.set_major_formatter(mtick.StrMethodFormatter("{x:,.0f}"))
        ax.tick_params(axis="x", rotation=45)
    if use_yticks_formatter:
        ax.yaxis.set_major_formatter(mtick.StrMethodFormatter("{x:,.0f}"))

    ax.set_title(title)
    ax.set_xlabel(x_label)
    ax.set_ylabel(y_label)

    return fig, ax


def basic_stats_table(
    data, columns: list[str] = None, number_formatter: str = "{x:.2f}"
) -> pd.DataFrame:
    # Handle Series input by converting to single-column DataFrame
    if isinstance(data, pd.Series):
        data = data.to_frame()
        columns = [data.columns[0]]
    elif isinstance(data, pd.DataFrame):
        if columns is None:
            raise ValueError(
                "For DataFrame input, please provide a list of column names."
            )
    else:
        raise TypeError("Input must be a pandas DataFrame or Series.")

    summary_data = {}

    for col in columns:
        series = data[col].dropna()
        summary_data[col] = {
            "Samples": len(series),
            "Mean": number_formatter.format(x=series.mean()),
            "Median": number_formatter.format(x=series.median()),
            "Std.": number_formatter.format(x=series.std()),
            "25%": number_formatter.format(x=series.quantile(0.25)),
            "75%": number_formatter.format(x=series.quantile(0.75)),
            "Min": number_formatter.format(x=series.min()),
            "Max": number_formatter.format(x=series.max()),
            "Skewness": round(skew(series), 2),
            "Kurtosis": round(kurtosis(series, fisher=False), 2),
        }

    return pd.DataFrame(summary_data).T


plot_hist(
    structured_data["noDirectors"],
    title=f"Number of directors reported\nin {form_name} Filings ({min_year} - {max_year})",
    x_label="Number of Directors",
    y_label="Count",
    bins=np.arange(0, np.max(structured_data["noDirectors"]) + 1) - 0.5,
)
plt.show()


basic_stats_table(
    structured_data,
    columns=["noDirectors"],
).T