Data Filtering Helper

This project filters multiple datasets to keep companies present across them with sufficient year coverage (Dec 31 rows for firm-level data), merges them into one wide file collapsed to one row per company-year (averaging numeric duplicates), computes Altman Z and related financial ratios, and optionally classifies into parent/consolidated sales and product diversification outputs with company metadata.

Files expected (in --data-dir)

• CG_Co.(xlsx|xls)
• CG_Ybasic.(xlsx|xls) — employees per firm
• FS_Combas.(xlsx|xls)
• FS_Comins.(xlsx|xls)
• FS_Comscfd.(xlsx|xls)
• FS_Comscfi.(xlsx|xls)
• FN_FN046.(xlsx|xls)
• MC_DiverOperationsDegree.(csv|xlsx|xls)
• MC_DiverOperationsPro.(csv|xlsx|xls)
• BDT_FinDistMertonDD.(xlsx|xls)
• OFDI_FININDEX.(xlsx|xls)
• IFS_IndRegMSELE.(xlsx|xls) — industry-level employees (joins by industry code + year; excluded from coverage intersection)
• ocscore.(xlsx|xls|csv) — O-score inputs; passthrough (no filtering), merged later in analytics

What clean_data.py does

1. Load each file (CSV or Excel).
2. Filter to parent statements by default:

• FS_Combas, FS_Comins, FS_Comscfd, FS_Comscfi, FN_FN046: Typrep = B (add consolidated with --allow-consolidated).
• MC_*: StateTypeCode = 2 (add consolidated with --allow-consolidated).

3. Keep only year-end rows (Dec 31) for dated firm-level files; industry-level IFS data keeps all years (only filtered by target years).
4. Normalize company IDs (strip, drop trailing .0).
5. For coverage-participating dated files, keep companies that appear in at least min_years of the target years (default 3). Coverage calculation uses only CGCo, CG_Ybasic, FS_Combas, FS_Comins, MC*, and BDT_FinDistMertonDD.
6. Intersect companies across those coverage-participating datasets. Firm-level files excluded from the coverage calculation (FS_Comscfd, FS_Comscfi, FN_FN046, OFDI_FININDEX) are still trimmed to that common company set, filtered by target years when dated, and must also meet --min-years; IFS_IndRegMSELE is excluded from coverage and filtered by target years only.
7. Write filtered outputs to filtered/ under the data dir. ocscore is passthrough (not filtered or trimmed).

Run commands

From the repo root (default: years 2018-2024, min 3 years coverage, parent-only, year-end only):

/Users/air/Documents/statadata/.venv/bin/python clean_data.py --data-dir /Users/air/Documents/statadata/data --debug

Recommended sequence (clean → merge → metrics → summary; add classify if needed):

# 1) Filter raw sources (parent-only)
/Users/air/Documents/statadata/.venv/bin/python clean_data.py --data-dir /Users/air/Documents/statadata/data

#    If you want consolidated too, add: --allow-consolidated

# 2) Merge filtered files into one wide file, collapsing to one row per company-year and adding serial_number (first column)
/Users/air/Documents/statadata/.venv/bin/python merge_filtered.py --data-dir /Users/air/Documents/statadata/data

# 3) Attach ocscore (Symbol+Date), compute Altman Z + derived metrics, normalize extremes, create data quality flags, and append into merged
/Users/air/Documents/statadata/.venv/bin/python apply_analytics.py --data-dir /Users/air/Documents/statadata/data

# 4) (Optional) Classify from the merged file into product and diversification outputs
/Users/air/Documents/statadata/.venv/bin/python classify_data.py --data-dir /Users/air/Documents/statadata/data

# 5) Generate a summary report of filters and counts (writes to docs/report_summary.txt by default)
/Users/air/Documents/statadata/.venv/bin/python report_summary.py --data-dir /Users/air/Documents/statadata/data

What each step produces:

• Step 1 (clean_data.py): filtered source files in <data-dir>/filtered, applying year-end (Dec 31) where applicable, year coverage, and parent-only by default; --allow-consolidated keeps consolidated too. IFS is filtered only by target years.
• Step 2 (merge*filtered.py): a wide outer-join merged_filtered.csv in <data-dir>/filtered, collapsing to one row per company-year (averaging numeric duplicates, first non-numeric), normalizing Date to the year integer, and adding a sequential serial_number per Symbol as the first column; all non-key source columns are retained with source prefixes (the script fails fast if any expected prefixed source column is missing); CG_Ybasic fields are prefixed cg_ybasic*, BDT bdt*fin*, OFDI ofdi*finindex*, industry employees join by year + industry code with ifs_EmployeeNum/ifs_LegalEntityNum.
• Step 3 (apply_analytics.py): left-joins ocscore on Symbol+Date (prefixed ocscore*\*), computes raw metrics (X1–X5, Leverage, ROA, etc.), applies hard-clipping normalization to extreme values, creates 8 data quality flags, and appends all into merged_filtered.csv. Outputs:
  • Raw metrics: AltmanZScore, X1–X5 components, FirmSize_LogTotalAssets, Leverage, ROA, FixedAssetsRatio, ROI, SalesGrowth
  • Normalized metrics: X1_Normalized–X5_Normalized, X4_Cap, AltmanZScore_Normalized
  • Data quality flags: flag_x1_extreme, flag_x2_extreme, flag_x3_extreme, flag_x4_spike, flag_x4_consistently_high, flag_x5_negative, flag_leverage_extreme, flag_data_quality_issues
  • Matching *_formula columns (Excel-ready strings for all metrics)
  • ROI uses fs_comins_B002000000 / fs_combas_A001212000
  • ocscore_* columns refreshed on each run (existing ones dropped before merge)
  • Add --output PATH only if you also want a standalone metrics CSV
• Step 4 (classify_data.py, optional): classified outputs in <data-dir>/filtered/classified, preserving all merged columns (including analytics, *_formula, and ocscore):
  • parent_product_diversification.csv / consolidated_product_diversification.csv (ClassificationStandard=3 + diversification metrics)
  • parent_sales_diversification.csv / consolidated_sales_diversification.csv (ClassificationStandard=2 + diversification metrics)
• Step 5 (report_summary.py): summary of filters, per-source counts, merged stats written to docs/report_summary.txt by default (or custom --output).

Options (clean_data.py):

• --data-dir DIR : folder containing the source files (default: cwd).
• --output-dir DIR : where to write filtered outputs (default: /filtered).
• --years Y1 Y2 ... : target years (default: 2018 2019 2020 2021 2022 2023 2024).
• --min-years N : minimum count of target years required per company per dated file (default: 3). Coverage intersection uses CGCo, CG_Ybasic, FS_Combas, FS_Comins, MC*, BDT_FinDistMertonDD. Dated firm-level files excluded from coverage calc (FS_Comscfd, FS_Comscfi, FN_FN046, OFDI_FININDEX) are still trimmed to the common companies and must also meet --min-years; IFS_IndRegMSELE is excluded and filtered by target years only.
• --allow-consolidated : also keep consolidated statements (MC StateTypeCode=1, FS Typrep=A). Default keeps parent only.
• ocscore is passthrough (not filtered by coverage/years); it is merged later in apply_analytics.
• --debug : print coverage stats per dataset and intersection size.

Options (merge_filtered.py):

• --data-dir DIR : base directory; script looks in <data-dir>/filtered first. Output defaults to <data-dir>/filtered/merged_filtered.csv.

Options (classify_data.py):

• --data-dir DIR : base directory; script looks for filtered/merged_filtered.csv (falls back to merged_filtered.csv in base). Output defaults to <data-dir>/filtered/classified.

Options (apply_analytics.py):

• --data-dir DIR : base directory; script looks for filtered/merged_filtered.csv (falls back to merged_filtered.csv in base).
• --output PATH : optional separate Altman/analytics CSV; omit to only update merged_filtered.csv.
• --no-update-merged : skip writing derived columns back into merged_filtered.csv.

Options (report_summary.py):

• --data-dir DIR : base directory; script looks for filtered/merged_filtered.csv (falls back to merged_filtered.csv in base).
• --output PATH : optional path to write the report; defaults to docs/report_summary.txt alongside the scripts.

Outputs

Written to filtered/ (or your --output-dir), one file per source, suffixed _filtered and same extension.

The classify step writes to <data-dir>/filtered/classified by default:

• parent_product_diversification.csv (ClassificationStandard=3)
• consolidated_product_diversification.csv (ClassificationStandard=3)
• parent_sales_diversification.csv (ClassificationStandard=2)
• consolidated_sales_diversification.csv (ClassificationStandard=2)

Each classified file includes:

• Symbol (normalized company ID) and EndDate (Dec 31 only)
• StatementType: Parent or Consolidated (from StateTypeCode 2 or 1)
• Product-level fields from MC_DiverOperationsPro: ProductName_EN, Currency, revenue/cost/profit, ratios, and growth metrics
• Diversification fields from MC_DiverOperationsDegree (scope depends on ClassificationStandard): IsDiversifiedOperations, MainBusinessInvolvedF/MainBusinessInvolvedS, IncomeHHI, IncomeEntropyIndex, ClassificationStandard
• Company metadata from CG_Co: ShortName_EN, IndustryCodeC (2012 CSRC)
• Additional merged columns (e.g., employees from CG_Ybasic, distress/market value from BDT, TobinQ from OFDI, industry employees from IFS) remain available in merged_filtered.csv and the Altman metrics output.

Notes

• Year filtering uses the first date column per file (Accper for FS**, EndDate for MC**). The merged file stores Date as the year after collapsing company-year duplicates.
• Company join: FS** Stkcd matches MC** Symbol; CG_Co uses Stkcd for presence only.
• Classification uses the same ID normalization, filters Dec 31 rows, and splits by StateTypeCode (2 parent, 1 consolidated). Company metadata (ShortName_EN, IndustryCodeC) is joined when available.
• Altman/metrics operate on the year-level Date produced by the merged file (derived from Dec 31 rows in clean_data); outputs are appended to merged along with *_formula columns so spreadsheets can recalc (a separate CSV is only written if --output is provided).
• CSVs with bad rows are retried with python engine and on_bad_lines="skip".
• Only Dec 31 rows are kept for dated files; change filter_year_end if your fiscal year-end differs.
• ocscore stays unfiltered in cleaning, is merged in analytics on Symbol+Date with ocscore_* prefixes, and carries through into both merged and classified outputs.
• Normalization (applied in apply_analytics.py): Raw Z-Score metrics are normalized by applying hard-clipping to extreme values to reduce distortion from outliers. X4 (market value) gets a company-specific cap at 1.5x historical median. Normalized metrics are stored in X1_Normalized–X5_Normalized columns. AltmanZScore_Normalized uses the corrected coefficient for X5 (1.0 instead of 0.999) and normalized components. All 8 data quality flags are generated during normalization to identify anomalies and errors in the data.

---

Data Quality Flags

Flags are generated automatically by apply_analytics.py (function normalize_all_metrics(), lines 88-171).

8 Flags Available

1. flag_x1_extreme — Working Capital Anomaly

   • Trigger: abs(X1) > 2.0
   • Count: 0 rows
   • Severity: Low

2. flag_x2_extreme — Extreme Profitability Loss

   • Trigger: X2 < -0.5 (retained earnings loss > 50% assets)
   • Count: 32 rows (0.5%)
   • Severity: Medium

3. flag_x3_extreme — Extreme Operating Loss

   • Trigger: X3 < -0.3 (EBIT loss > 30% assets)
   • Count: 71 rows (1.0%)
   • Severity: Medium

4. flag_x4_spike — Market Valuation Spike

   • Trigger: X4 > company_specific_cap (1.5x historical median)
   • Count: 566 rows (8.1%) - Most common
   • Severity: Medium

5. flag_x4_consistently_high — Consistently High Valuation

   • Trigger: company_median(X4) > 50
   • Count: 392 rows (5.6%)
   • Severity: Low

6. flag_x5_negative — DATA ERROR: Negative Revenue

   • Trigger: X5 < 0 (impossible)
   • Count: 2 rows (0.03%)
   • Severity: Critical - Requires data fix

7. flag_leverage_extreme — INSOLVENT: TL > TA

   • Trigger: Leverage > 1.0 (liabilities exceed assets)
   • Count: 12 rows (0.2%)
   • Severity: Critical

8. flag_data_quality_issues — Summary Flag

   • Meaning: Count of all issues (0-7)
   • Count: 639 rows have 1+ issues (9.1% of data)

Where Flags Appear

• Main data file: data/filtered/merged_filtered.csv
• All classification outputs: data/filtered/classified/*.csv
• Any exported dataset via analyze_data_quality.py

All 8 flags automatically flow through entire pipeline.

---

Commands Reference

Data Quality Analysis

Generate comprehensive report on all flags (saves to docs/data_quality_report.txt):

python3 analyze_data_quality.py --data-dir ./data

Custom report output path:

python3 analyze_data_quality.py --data-dir ./data --report-output my_report.txt

Focus on specific flag with detailed analysis (prints to stdout):

python3 analyze_data_quality.py --data-dir ./data --focus flag_x4_spike
python3 analyze_data_quality.py --data-dir ./data --focus flag_x5_negative
python3 analyze_data_quality.py --data-dir ./data --focus flag_leverage_extreme

Export flagged records to CSV:

python3 analyze_data_quality.py --data-dir ./data --export-flagged flagged_data.csv
python3 analyze_data_quality.py --data-dir ./data --export-clean clean_data.csv
python3 analyze_data_quality.py --data-dir ./data --export-flag flag_x5_negative --output data_errors.csv
python3 analyze_data_quality.py --data-dir ./data --export-flag flag_leverage_extreme --output insolvency.csv

Pipeline Commands

Run full pipeline:

python3 apply_analytics.py
python3 classify_data.py
python3 report_summary.py

Run only apply_analytics (adds normalization and all 8 flags):

python3 apply_analytics.py

Creates 16 new columns:

• X1_Normalized through X5_Normalized
• X4_Cap
• AltmanZScore_Normalized
• 8 flag columns (flag_x1_extreme through flag_data_quality_issues)

Using Flags in Python

import pandas as pd

df = pd.read_csv('data/filtered/merged_filtered.csv')

# Get clean data only
clean_df = df[df['flag_data_quality_issues'] == 0]
print(f"Clean records: {len(clean_df)}")  # 6,349

# Find data errors
errors = df[df['flag_x5_negative'] > 0]
print(f"Data errors: {len(errors)}")  # 2

# Find market spikes
spikes = df[df['flag_x4_spike'] > 0]
print(f"Market spikes: {len(spikes)}")  # 566

# Find insolvent companies
insolvent = df[df['flag_leverage_extreme'] > 0]
print(f"Insolvent: {len(insolvent)}")  # 12