Checklist: cai.csv data file format

[MattTriano]

Requirements for the cai.csv data file format

Per page 24 of the policy notice and Notices of Changes to the process.

Column Names, Order, and Data Types (dtypes):

1. type (dtype: string)
2. entity_name (dtype: string)
3. entity_number (dtype: integer)
   • test: test_cai_column_dtypes__entity_number
4. CMS number (or cms_number) (dtype: string)
5. frn (dtype: string)
6. location_id (dtype: integer)
   • test: test_cai_column_dtypes__location_id
7. address_primary (dtype: string)
8. city (dtype: string)
9. state (dtype: two-character string)
10. zip_code (dtype: five-character string)
11. longitude (dtype: float; but this project uses str dtype to make it possible to check 6-decimal-place precision)
    • test: test_cai_col_content__longitude
12. latitude (dtype: float; but this project uses str dtype to make it possible to check 6-decimal-place precision)
    • test: test_cai_col_content__latitude
13. explanation (dtype: string)
14. need (dtype: integer)
    • test: test_cai_column_dtypes__need
15. availability (dtype: string)
    • test: test_cai_column_dtypes__availability

• Correct column names
  • test: test_cai__column_names
• Correct column order
  • test: test_cai__column_order

Column Content Requirements

• type: Enum; in [S, L, G, H, F, P, C]
  • test: test_cai_col_content__type
  • test: test_CAIType_validator
• location_id: A 10 digit integer $\ge 10^9$ and $\lt 10^{10}$.
  • test: test_cai_col_content__location_id
  • test: test_BSLLocationIdNullableValidator
• state: Enum; a 2 letter state identifier
  • test: test_cai_col_content__state
  • test: test_State_validator
• zip_code: A zero-padded string of exactly 5 digits.
  • test: test_cai_col_content__zip_code
  • test: test_ZipNullableValidator
• longitude: A value reflecting the unprojected longitude (in WGS-84 CRS, but this code doesn't/can't check for that) in decimal degrees, with 6 decimal places of precision.
  • test: test_cai_col_content__longitude
• latitude: A value reflecting the unprojected latitude (in WGS-84 CRS, but this code doesn't/can't check for that) in decimal degrees, with 6 decimal places of precision.
  • test: test_cai_col_content__latitude
• need: A positive integer reflecting the needed broadband speed (in Mbps; presumably 1000 everywhere)
  • test: test_cai_col_content__need
• availability: An integer reflecting the highest speed service available at the location (in Mbps).
  • test: test_cai_col_content__availability

Optional

• CMS number: Should identify a healthcare practice (As best I can tell from google and the given links, these are 6 or 10 digit numbers)
  • test: test_cai_col_content__cms_number
  • test: test_CMSCertificateNullableValidator
• frn: FCC registration number, 6 or 10 digits, uniquely identifies registrant with FCC.
  • test: test_cai_col_content__frn
  • test: test_FrnNullableValidator

Columns Required to be Not Null

• type: test: test_cai_col_content__type
• entity_name: test test_cai_col_content__entity_name
• state: test: test_cai_col_content__state
• need: test: test_cai_col_content__need

Columns that can be Conditionally Null

• location_id: Can only be null if (address_primary, city, zip_code) are all not null, or if (latitude, longitude) are both not null.
  • test: test_cai_row_rule__location_possibly_determinable
• address_primary: Can only be null if location_id is not null, or if (latitude, longitude) are both not null.
  • test: test_cai_row_rule__location_possibly_determinable
• city: Can only be null if location_id is not null, or if (latitude, longitude) are both not null.
  • test: test_cai_row_rule__location_possibly_determinable
• zip_code: Can only be null if location_id is not null, or if (latitude, longitude) are both not null.
  • test: test_cai_row_rule__location_possibly_determinable
• longitude: Can only be null if location_id is not null, or if (address_primary, city, zip_code) are all not null.
  • test: test_cai_row_rule__location_possibly_determinable
• latitude: Can only be null if location_id is not null, or if (address_primary, city, zip_code) are all not null.
  • test: test_cai_row_rule__location_possibly_determinable
• explanation: Cannot be null if type is in [C]
  • test: test_cai_row_rule__explanation_given_cai_type

Optional

• CMS number: Cannot be null when type is in [H]
  • test: test_cai_row_rule__cms_number_given_cai_type
• frn: Cannot be null when type is in [S, L, H]
  • test: test_cai_row_rule__frn_given_cai_type

Multi-File Column Rules

N/A

Intentionally noted but not checked by this project

• entity_number: "Mandatory if CAI participates in E-Rate program. Leave empty for CAIs that are neither type S nor L and do not participate in the E-Rate program."
• CMS number: Should be valid and maybe turn up in Medicare lookup tools that allow for search by CNN?
• frn: Should be valid and in the FCC's systems.
• location_id: Must be in the Fabric for that state.