# S-1 Filing Data Construction README

This directory documents the construction of `data/S1_Filings_0412.csv`, the final S-1 filing-level dataset. The pipeline combines SEC S-1 filing metadata, text-based readability measures, an entrepreneurship-textbook cosine-similarity measure, and CRSP/Compustat link-table identifiers.

working directory = /Build/Regression Sample Construction/Edgar_Crunchbase

## Directory layout

```text
replication/
├── code/                  # Python, R, and SLURM scripts used to construct the data
├── data/                  # Input, intermediate, and final CSV files
├── data/cosim/            # Per-array cosine-similarity outputs
├── log/                   # SLURM logs for non-array jobs
├── log/entrp_cosim/       # SLURM array logs for cosine-similarity jobs
├── data_diagram.txt       # Original data-flow sketch
└── README.md              # This file
```

## High-level data flow

```text
data/S1_Filings.csv
    |
    |  code/calculate_fog_numwords.sh
    |      -> code/calculate_fog_numwords.py
    |      -> logs: log/s1fog.out
    v
[data/S1_Filings_text.csv]
    |
    |  code/entrepreneur_cosim.sh  [SLURM array: 1-256]
    |      -> code/entrepreneur_cosim.R
    |      -> input: data/txtbook_bow.csv
    |      -> logs: log/entrp_cosim/cosim-*.out
    v
    data/cosim/S1_Filings_cosim_*.csv
    |
    |  code/concat_cosim.sh
    |      -> log: log/cat-cosim.out
    v
    data/S1_Filings_cosim_all.csv
    |
    |  code/save_S1.sh
    |      -> code/save_S1.py  [Python version of original save_S1.ipynb]
    |      -> input: data/crsp_linktable.csv
    |      -> log: log/save_s1.out
    v
data/S1_Filings_0412.csv
```

`data/S1_Filings_0412.csv` is the final output.

## Main files

### Input files

| File | Rows | Columns | Role |
|---|---:|---:|---|
| `data/S1_Filings.csv` | 27,314 | 8 | Starting S-1 filing metadata: CIK, company name, form type, filing date, SEC filename, year, quarter, filepath. |
| `data/txtbook_bow.csv` | 4,166 | 2 | Entrepreneurship textbook bag-of-words file used as the reference vector for cosine similarity. |
| `data/crsp_linktable.csv` | 31,638 | 13 | CRSP/Compustat link table merged onto the filing-level data by `cik`. |

### Intermediate files

| File or pattern | Rows | Columns | Produced by | Role |
|---|---:|---:|---|---|
| `data/S1_Filings_text.csv` | 14.4GB (large text) | expected to include filing text plus `num_words` and `fog_index` | `calculate_fog_numwords.py` | Intended input to the cosine-similarity stage. |
| `data/cosim/S1_Filings_cosim_*.csv` | 256 files | 11 columns each | `entrepreneur_cosim.R` via SLURM array | Per-task cosine-similarity outputs. Each array task processes up to 107 rows. |
| `data/S1_Filings_cosim_all.csv` | 27,304 | 11 | `concat_cosim.sh` | Concatenation of the 256 per-task cosine-similarity CSVs. |

### Final file

| File | Rows | Columns | Produced by | Role |
|---|---:|---:|---|---|
| `data/S1_Filings_0412.csv` | 27,304 | 30 | `save_S1.py` / `save_S1.ipynb` | Final filing-level dataset with text variables, cosine similarity, quantile bins, CRSP/Compustat identifiers, and link-date validity indicator. |

## Script-by-script documentation

### 1. `code/calculate_fog_numwords.py`

**SLURM wrapper:** `code/calculate_fog_numwords.sh`  
**Logs:** `log/s1fog.out`

This script reads:

```text
data/S1_Filings.csv
```

For each SEC filing, it attempts to load the raw filing from the Yens ZFS EDGAR mirror:

```text
/zfs/data/NODR/EDGAR_HTTPS/{filename}
```

It parses the filing with BeautifulSoup using the `lxml` parser, extracts the first `<document><text>` block, removes `ix:header` if present, strips HTML tags, and computes:

```text
num_words
fog_index
```

The log `log/s1fog.out` indicates that this step loaded 27,314 filings, produced non-missing `fog_index` for 27,304 filings, and took roughly 14,958 seconds for document loading plus 893 seconds for text-measure calculation.

**Important reproducibility note.** The current checked-in `calculate_fog_numwords.py` drops `s1_text` before writing `data/S1_Filings_text.csv`. However, `entrepreneur_cosim.R` expects an input column named `s1_text`. Therefore, a full from-scratch rerun requires either:

1. retaining `s1_text` in `data/S1_Filings_text.csv` until after the cosine-similarity stage, or
2. using an archived version of `data/S1_Filings_text.csv` that still contains `s1_text`.

This matters because the cosine-similarity stage cannot be rerun from only `num_words` and `fog_index`.

### 2. `code/entrepreneur_cosim.R`

**SLURM wrapper:** `code/entrepreneur_cosim.sh`  
**Logs:** `log/entrp_cosim/cosim-*.out`  
**Outputs:** `data/cosim/S1_Filings_cosim_*.csv`

This is a SLURM array job:

```bash
#SBATCH --array=1-256
```

The wrapper runs:

```bash
Rscript code/entrepreneur_cosim.R ${SLURM_CPUS_PER_TASK} ${SLURM_ARRAY_TASK_ID} 107
```

The three command-line arguments are:

```text
1. number of CPU cores
2. SLURM array task ID
3. rows to process per task, equal to 107
```

The R script reads the filing-level text file, drops rows with missing `fog_index`, slices the data according to the array task ID, and computes an entrepreneurship cosine-similarity measure. The reference text vector comes from:

```text
data/txtbook_bow.csv
```

Each task log is stored as:

```text
log/entrp_cosim/cosim-1.out
log/entrp_cosim/cosim-2.out
...
log/entrp_cosim/cosim-256.out
```

Each task produces one CSV. In the archived data tree, these files are stored as:

```text
data/cosim/S1_Filings_cosim_1.csv
...
data/cosim/S1_Filings_cosim_256.csv
```

**Path consistency note.** The current R script defines:

```r
savefilepath <- paste0("./data/S1_Filings_cosim_", task_id, ".csv")
```

but the archived outputs are under:

```text
data/cosim/
```

For a clean rerun consistent with the current directory structure, set:

```r
savefilepath <- paste0("./data/cosim/S1_Filings_cosim_", task_id, ".csv")
```

or move the per-task CSVs into `data/cosim/` before running `concat_cosim.sh`.

### 3. `code/concat_cosim.sh`

**Log:** `log/cat-cosim.out`  
**Input:** `data/cosim/S1_Filings_cosim_*.csv`  
**Output:** `data/S1_Filings_cosim_all.csv`

This script concatenates the 256 per-task cosine-similarity files into one CSV:

```text
data/S1_Filings_cosim_all.csv
```

The script uses natural sorting (`sort -V`) so that files are ordered as `1, 2, ..., 10, ...` rather than lexicographic order. It also removes repeated header rows using:

```bash
awk 'FNR == 1 && NR != 1 { next } { print }'
```

The log `log/cat-cosim.out` reports:

```text
Number of files found: 256
Wrote data/S1_Filings_cosim_all.csv
Number of header rows in final file: 0
```

`data/S1_Filings_cosim_all.csv` has 27,304 rows and 11 columns. The row count is lower than the original 27,314 filings because `entrepreneur_cosim.R` drops filings with missing `fog_index` before computing cosine similarity.

### 4. `code/save_S1.py` and `code/save_S1.ipynb`

**SLURM wrapper:** `code/save_S1.sh`  
**Log:** `log/save_s1.out`  
**Inputs:** `data/S1_Filings_cosim_all.csv`, `data/crsp_linktable.csv`  
**Output:** `data/S1_Filings_0412.csv`

`save_S1.ipynb` is the original notebook. `save_S1.py` is a cleaned Python-script version of the same workflow.

This step:

1. reads `data/S1_Filings_cosim_all.csv`,
2. prints a correlation table for `num_words`, `fog_index`, and `cosim_values`,
3. creates decile variables:
   - `dec_numwords`
   - `dec_fogindex`
   - `dec_cosimvalues`
4. creates median-split variables:
   - `med_numwords`
   - `med_fogindex`
   - `med_cosimvalues`
5. merges `data/crsp_linktable.csv` by `cik`,
6. checks whether each filing date falls between `LINKDT` and `LINKENDDT`,
7. resolves duplicate filing/link-table matches, and
8. writes the final dataset.

The log `log/save_s1.out` reports the duplicate-link partition:

```text
no valid link date:       962 filenames
one valid link date:      360 filenames
two valid link dates:       4 filenames
```

The final output is:

```text
data/S1_Filings_0412.csv
```

with 27,304 rows and 30 columns.

## Log-file reconciliation

| Code script | SLURM wrapper | Log file(s) | Output file(s) |
|---|---|---|---|
| `code/calculate_fog_numwords.py` | `code/calculate_fog_numwords.sh` | `log/s1fog.out` | intended `data/S1_Filings_text.csv` |
| `code/entrepreneur_cosim.R` | `code/entrepreneur_cosim.sh` | `log/entrp_cosim/cosim-*.out` | `data/cosim/S1_Filings_cosim_*.csv` |
| `code/concat_cosim.sh` | same file | `log/cat-cosim.out` | `data/S1_Filings_cosim_all.csv` |
| `code/save_S1.py` / `code/save_S1.ipynb` | `code/save_S1.sh` | `log/save_s1.out` | `data/S1_Filings_0412.csv` |

There are 256 cosine-similarity logs in `log/entrp_cosim/cosim-*.out`, corresponding to the 256 SLURM array tasks. They correspond one-for-one to the 256 per-task output files in `data/cosim/S1_Filings_cosim_*.csv`.

## Recommended clean rerun order

From the project root directory:

```bash
# 1. Create log/output directories if needed
mkdir -p log/entrp_cosim data/cosim

# 2. Compute filing text, word count, and fog index
sbatch code/calculate_fog_numwords.sh

# 3. Compute entrepreneurship cosine similarity by array task
sbatch code/entrepreneur_cosim.sh

# 4. Concatenate the 256 per-task cosine files
sbatch code/concat_cosim.sh

# 5. Build final CRSP-linked dataset
sbatch code/save_S1.sh
```

Before step 3, verify that the input used by `entrepreneur_cosim.R` contains `s1_text`. Before step 4, verify that the per-task files are located in `data/cosim/`.

## Final dataset columns

`data/S1_Filings_0412.csv` contains the following 30 columns:

```text
cik
company_name
form_type
date_filed
filename
year
quarter
filepath
num_words
fog_index
cosim_values
dec_numwords
dec_fogindex
dec_cosimvalues
med_numwords
med_fogindex
med_cosimvalues
gvkey
tic
cusip
sic
naics
LINKPRIM
LIID
LINKTYPE
LPERMNO
LPERMCO
LINKDT
LINKENDDT
valid_linkdate
```

## Notes on auxiliary files

The following files appear in `data/` but are not the main final-output path according to the current scripts:

| File | Note |
|---|---|
| `data/S1_Filings_fog.csv` | 50-row partial or diagnostic file with `num_words` and `fog_index`; not used by the final construction scripts. |
| `data/S1_Filings_notext.csv` | 27,314-row file with metadata and `num_words`; in the archived copy, `fog_index` is missing. It is not the input used by `entrepreneur_cosim.R`. |
| `data/EITbook.txt` | Entrepreneurship textbook raw text used historically to create `data/txtbook_bow.csv`. |
| `data/Entrepreneurship-and-Innovation-Toolkit-1575136145.docx` | Source document for the entrepreneurship textbook. |
| `data/mcc_keywords_MDA.csv`, `data/tf_mcc_MDA.csv` | Keyword/frequency files present in the data directory but not used by the S-1 pipeline scripts shown above. |

## Reproducibility Requirement

**Fog index depends on package versions.** The readability calculation depends on BeautifulSoup/lxml parsing and NLTK sentence tokenization. To replicate old outputs, use a pinned 2023-era environment if possible.