<jupyter_start><jupyter_text>Committee Reference &ndash; Data Quality  Overview Committees in the FEC data set have a unique ID assigned to them.  However, since we are combining Committee records from multiple election cycle source files, we really should join to `cmte` using both `cmte_id` and `elect_cycle`.  The purpose of this notebook is to explore the "quality" of the Committee data, both within and across election cycles, to see how consistent it is, and how we can create a unified Committee Master entity to improve referential integrity for the larger, complete data set.Here is a list of the examinations in this notebook:* Quality of Committee Names (`cmte_nm`)* Integrity of Committee ID (`cmte_id`)* Consistency of names for `cmte_id`'s across election cycles* Multiple `cmte_id`'s for identical names &ndash; within election cycles* Multiple `cmte_id`'s for identical names &ndash; across election cyclesHere are additional examinations to do:* Multiple `cmte_id`'s for *similar* names &ndash; within election cycles* Multiple `cmte_id`'s for *similar* names &ndash; across election cycles  Notebook Setup  Configure database connect info/options Note: database connect string can be specified on the initial `%sql` command:```pythondatabase_url = "postgresql+psycopg2://user@localhost/fecdb"%sql $database_url```Or, connect string is taken from DATABASE_URL environment variable (if not specified for `%sql`):```python%sql```<jupyter_code>%load_ext sql
%config SqlMagic.autopandas=True
%config InteractiveShell.ast_node_interactivity='last_expr_or_assign'
# connect string taken from DATABASE_URL environment variable
%sql<jupyter_output><empty_output><jupyter_text>Configure Python modules<jupyter_code>import pandas as pd

pd.set_option("display.max_rows", 200)<jupyter_output><empty_output><jupyter_text>Set styling<jupyter_code>%%html
<style>
  tr, th, td {
    text-align: left !important;
  }
</style><jupyter_output><empty_output><jupyter_text>Examination of Committee Data Set   High-level summary First count total records and distinct `cmte_id`'s (and save out results for reference)<jupyter_code>%%sql result <<
select count(*) as count_total,
       count(distinct cmte_id) as count_distinct_ids,
       count(distinct cmte_nm) as count_distinct_names
  from cmte
cmte_count_total    = int(result.loc[0][0])
cmte_distinct_ids   = int(result.loc[0][1])
cmte_distinct_names = int(result.loc[0][2])
"cmte_count_total = %d, cmte_distinct_ids = %d, cmte_distinct_names = %d" % \
    (cmte_count_total, cmte_distinct_ids, cmte_distinct_names)<jupyter_output><empty_output><jupyter_text>Quality of Committee Names (`cmte_nm`) Let's try and get a sense of the extent of formatting problems (inconsistencies or flaws).  First look for names that have lowercase letters (uppercase is now the standard)...<jupyter_code>%%sql
select elect_cycle,
       count(*)
  from cmte
 where cmte_nm ~ '[a-z]'
 group by 1
 order by 1
%%sql
select cmte_nm,
       array_agg(distinct elect_cycle)
  from cmte
 where cmte_nm ~ '[a-z]'
 group by 1
 order by 1<jupyter_output>* postgresql+psycopg2://crash@caladan/fecdb
15 rows affected.<jupyter_text>Next, look for names with consecutive whitespace...<jupyter_code>%%sql
select elect_cycle,
       count(*)
  from cmte
 where cmte_nm ~ '\s{2,}'
 group by 1
 order by 1
%%sql
select cmte_nm,
       array_agg(distinct elect_cycle)
  from cmte
 where cmte_nm ~ '\s{2,}'
 group by 1
 order by 1
 limit 50<jupyter_output>* postgresql+psycopg2://crash@caladan/fecdb
50 rows affected.<jupyter_text>And now, names with errant spaces within parentheses...<jupyter_code>%%sql
select elect_cycle,
       count(*)
  from cmte
 where cmte_nm ~ '\( | \)'
 group by 1
 order by 1
%%sql
select cmte_nm,
       array_agg(distinct elect_cycle)
  from cmte
 where cmte_nm ~ '\( | \)'
 group by 1
 order by 1
 limit 50<jupyter_output>* postgresql+psycopg2://crash@caladan/fecdb
11 rows affected.<jupyter_text>And inconsistent spacing around commas (either space before, or no space after)<jupyter_code>%%sql
select elect_cycle,
       count(*)
  from cmte
 where cmte_nm ~ ' ,|,[^ ]'
 group by 1
 order by 1
%%sql
select cmte_nm,
       array_agg(distinct elect_cycle)
  from cmte
 where cmte_nm ~ ' ,|,[^ ]'
 group by 1
 order by 1
 limit 50<jupyter_output>* postgresql+psycopg2://crash@caladan/fecdb
22 rows affected.<jupyter_text>Integrity of Committee ID (`cmte_id`) Count records across election cycles and see if we have any null `cmte_id`'s (all zeros would be good)<jupyter_code>%%sql
select elect_cycle,
       count(*)                  as records,
       count(*) - count(cmte_id) as null_cmte_ids
  from cmte
 group by 1
 order by 1<jupyter_output>* postgresql+psycopg2://crash@caladan/fecdb
11 rows affected.<jupyter_text>Let's see if there are any duplicate `cmte_id`'s in any election cycles (should also be zero)<jupyter_code>%%sql
with dup_cmte_id as (
    select elect_cycle,
           cmte_id,
           count(*) as id_count
      from cmte
     group by 1, 2
    having count(*) > 1
)
select elect_cycle,
       count(*) as dupes,
       sum(id_count) as total_dupe_ids,
       max(id_count) as max_dupe_ids
  from dup_cmte_id
 group by 1<jupyter_output>* postgresql+psycopg2://crash@caladan/fecdb
0 rows affected.<jupyter_text>Now let's look at repeated `cmte_id`'s across election cycles (note that specifying `distinct` within `array_agg` is a tricky way of sorting the values, for consistency, if we care to group by that field)<jupyter_code>%%sql
with cmte_id_sum as (
    select cmte_id,
           count(*) as ec_count,
           array_agg(distinct elect_cycle) as elect_cycles
      from cmte
     group by 1
)
select ec_count,
       count(*) as cmte_ids,
       round(count(*)::numeric / :cmte_distinct_ids * 100.0, 2) as pct_cmte_ids
  from cmte_id_sum
 group by 1
 order by 1 desc<jupyter_output>* postgresql+psycopg2://crash@caladan/fecdb
11 rows affected.<jupyter_text>Consistency of names for `cmte_id`'s across election cycles Count the number of different names for Committee records (identified by `cmte_id`) appearing in multiple election cycles<jupyter_code>%%sql
with cmte_diff_names as (
    select cmte_id,
           count(distinct cmte_nm)     as num_diff_names,
           array_agg(distinct cmte_nm) as diff_names
      from cmte
     group by 1
    having count(distinct cmte_nm) > 1
)
select count(*) as cmte_ids,
       round(count(*)::numeric / :cmte_distinct_ids * 100.0, 2) as pct_cmte_ids
  from cmte_diff_names<jupyter_output>* postgresql+psycopg2://crash@caladan/fecdb
1 rows affected.<jupyter_text>Let's report by the different levels of variation on name (i.e. number of different representations)<jupyter_code>%%sql
with cmte_diff_names as (
    select cmte_id,
           count(distinct cmte_nm)     as num_diff_names,
           array_agg(distinct cmte_nm) as diff_names
      from cmte
     group by 1
    having count(distinct cmte_nm) > 1
)
select num_diff_names,
       count(*) as cmte_ids,
       round(count(*)::numeric / :cmte_distinct_ids * 100.0, 2) as pct_cmte_ids
  from cmte_diff_names
 group by 1
 order by 1 desc<jupyter_output>* postgresql+psycopg2://crash@caladan/fecdb
8 rows affected.<jupyter_text>Get an idea of what the different names associated with the same `cmte_id` look like&mdash;let's start with a sampling of Committee IDs with `num_diff_names` = 2 (compare adjacent `cmte_nm`'s)...<jupyter_code>%%sql
with cmte_diff_names as (
    select cmte_id,
           count(distinct cmte_nm)     as num_diff_names,
           array_agg(distinct cmte_nm) as diff_names
      from cmte
     group by 1
    having count(distinct cmte_nm) = 2
)
select cmte_id,
       unnest(diff_names) as cmte_nm
  from cmte_diff_names
 order by cmte_id
 limit 50<jupyter_output>* postgresql+psycopg2://crash@caladan/fecdb
50 rows affected.<jupyter_text>Now, let's look at `num_diff_names` = 3...<jupyter_code>%%sql
with cmte_diff_names as (
    select cmte_id,
           count(distinct cmte_nm)     as num_diff_names,
           array_agg(distinct cmte_nm) as diff_names
      from cmte
     group by 1
    having count(distinct cmte_nm) = 3
)
select cmte_id,
       unnest(diff_names) as cmte_nm
  from cmte_diff_names
 order by cmte_id
 limit 51<jupyter_output>* postgresql+psycopg2://crash@caladan/fecdb
51 rows affected.<jupyter_text>And `num_diff_names` = 4...<jupyter_code>%%sql
with cmte_diff_names as (
    select cmte_id,
           count(distinct cmte_nm)     as num_diff_names,
           array_agg(distinct cmte_nm) as diff_names
      from cmte
     group by 1
    having count(distinct cmte_nm) = 4
)
select cmte_id,
       unnest(diff_names) as cmte_nm
  from cmte_diff_names
 order by cmte_id
 limit 52<jupyter_output>* postgresql+psycopg2://crash@caladan/fecdb
52 rows affected.<jupyter_text>And for the fun of it, let's look at the most extreme examples (`num_diff_names` > 7)<jupyter_code>%%sql
with cmte_diff_names as (
    select cmte_id,
           count(distinct cmte_nm)     as num_diff_names,
           array_agg(distinct cmte_nm) as diff_names
      from cmte
     group by 1
    having count(distinct cmte_nm) > 7
)
select cmte_id,
       unnest(diff_names) as cmte_nm
  from cmte_diff_names
 order by cmte_id
 limit 100<jupyter_output>* postgresql+psycopg2://crash@caladan/fecdb
26 rows affected.<jupyter_text>Multiple `cmte_id`'s for identical names &ndash; within election cycles Let's first see how many names are involved, and what percentage of total distinct names that represents<jupyter_code>%%sql
with shared_cmte_name as (
    select elect_cycle,
           cmte_nm,
           count(*) as num_shares
      from cmte
     where cmte_nm is not null
     group by 1, 2
    having count(*) > 1
)
select count(*) as shared_names,
       round(count(*)::numeric / :cmte_distinct_names * 100.0, 2) as pct_distinct_names
  from shared_cmte_name<jupyter_output>* postgresql+psycopg2://crash@caladan/fecdb
1 rows affected.<jupyter_text>Now we'll report by the level of replication (name sharing by different Committees, as identified by `cmte_id`) we have in various cycles; note that the same name may "offend" (map to multiple `cmte_id`'s) within different election cycles (either in the same, or differing, "num_shares")<jupyter_code>%%sql
with shared_cmte_name as (
    select elect_cycle,
           cmte_nm,
           count(*) as num_shares
      from cmte
     where cmte_nm is not null
     group by 1, 2
    having count(*) > 1
)
select num_shares,
       count(*) as shared_names,
       array_agg(distinct elect_cycle) as elect_cycles
  from shared_cmte_name
 group by 1
 order by 1 desc<jupyter_output>* postgresql+psycopg2://crash@caladan/fecdb
4 rows affected.<jupyter_text>Let's take a look at some of the top offenders (see "elect_cycles" for multiple offenses of the same "num_shares" by a name)<jupyter_code>%%sql
with shared_cmte_name as (
    select elect_cycle,
           cmte_nm,
           count(*) as num_shares,
           array_agg(distinct cmte_id) as cmte_ids
      from cmte
     where cmte_nm is not null
     group by 1, 2
    having count(*) > 2
)
select cmte_nm,
       array_length(cmte_ids, 1) num_cmte_ids,
       cmte_ids,
       array_agg(elect_cycle) as elect_cycles
  from shared_cmte_name
 group by 1, 3
 order by array_length(cmte_ids, 1) desc, count(*) desc, cmte_nm<jupyter_output>* postgresql+psycopg2://crash@caladan/fecdb
61 rows affected.<jupyter_text>And we'll look at the top recurrences for cases where the number of shares is exactly two in an election cycle<jupyter_code>%%sql
with shared_cmte_name as (
    select elect_cycle,
           cmte_nm,
           count(*) as num_shares,
           array_agg(distinct cmte_id) as cmte_ids
      from cmte
     where cmte_nm is not null
     group by 1, 2
    having count(*) = 2
)
select cmte_nm,
       cmte_ids,
       count(*) as num_elect_cycles,
       array_agg(elect_cycle) as elect_cycles
  from shared_cmte_name
 group by 1, 2
 order by 3 desc, 1, 2
 limit 50<jupyter_output>* postgresql+psycopg2://crash@caladan/fecdb
50 rows affected.<jupyter_text>Multiple `cmte_id`'s for identical names &ndash; across election cycles As above, we'll see how many names are mapped to different `cmte_id`'s, except now *across* election cycles<jupyter_code>%%sql
with shared_cmte_name as (
    select cm.cmte_nm,
           count(distinct cm2.cmte_id) as num_cmte_ids,
           array_agg(distinct cm2.elect_cycle) as elect_cycles
      from cmte cm
      join cmte cm2 on  cm2.cmte_nm      = cm.cmte_nm
                    and cm2.cmte_id     != cm.cmte_id
                    and cm2.elect_cycle != cm.elect_cycle
     group by 1
)
select count(*) as shared_names,
       round(count(*)::numeric / :cmte_distinct_names * 100.0, 2) as pct_distinct_names
  from shared_cmte_name<jupyter_output>* postgresql+psycopg2://crash@caladan/fecdb
1 rows affected.<jupyter_text>And now report by the level of replication (name sharing by different Committees) we have in across cycles<jupyter_code>%%sql
with shared_cmte_name as (
    select cm.cmte_nm,
           count(distinct cm2.cmte_id) as num_cmte_ids,
           array_agg(distinct cm2.elect_cycle) as elect_cycles
      from cmte cm
      join cmte cm2 on  cm2.cmte_nm      = cm.cmte_nm
                    and cm2.cmte_id     != cm.cmte_id
                    and cm2.elect_cycle != cm.elect_cycle
     group by 1
)
select num_cmte_ids,
       count(*) as num_shared_names
  from shared_cmte_name
 group by 1
 order by 1 desc<jupyter_output>* postgresql+psycopg2://crash@caladan/fecdb
9 rows affected.<jupyter_text>And we'll take a look at the top offenders for this replication<jupyter_code>%%sql
with shared_cmte_name as (
    select cm.cmte_nm,
           count(distinct cm2.cmte_id) as num_cmte_ids,
           array_agg(distinct cm2.cmte_id) as cmte_ids,
           count(*) as count_records
      from cmte cm
      join cmte cm2 on  cm2.cmte_nm      = cm.cmte_nm
                    and cm2.cmte_id     != cm.cmte_id
                    and cm2.elect_cycle != cm.elect_cycle
     group by 1
)
select cmte_nm,
       num_cmte_ids,
       cmte_ids
  from shared_cmte_name
 order by 2 desc, 1
 limit 50<jupyter_output>* postgresql+psycopg2://crash@caladan/fecdb
50 rows affected.
{'fraction_non_alphanumeric': 0.08140513251998317, 'fraction_numerical': 0.014023278642546627, 'mean_word_length': 3.8088334457181388, 'pattern_counts': {'":': 0, '<': 81, '<?xml version=': 0, '>': 87, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '24', 'dir': 'jupyter-structured-clean-dedup', 'id': '231900', 'license': 'MIT', 'n_tokens_mistral': 5119, 'n_tokens_neox': 4872, 'n_words': 1454, 'path': 'notebooks/data_quality/dq1_cmte_ref_data.ipynb', 'repo_name': 'crashka/fecdb', 'size': '14262'}