<jupyter_start><jupyter_text>01-congress-prep**Purpose**: extract social media and biographical information from the [congress-legislators](https://github.com/unitedstates/congress-legislators) repo for legislators who served in the 114th, 115th, and/or 116th session.- 114th started on Jan 3, 2015. Get last commit pre-Jan 3, 2017 not related to 115th. [(November 15, 2016)](https://github.com/unitedstates/congress-legislators/tree/a35d649180d55a0b7d1e381e1774d315371a9188)- 115th started on Jan 3, 2017. Get last commit pre-Jan 3, 2019 not related to 116th. [(Jan 1, 2019)](https://github.com/unitedstates/congress-legislators/tree/b36de263d6d9dbf40b95f0d1dfc1e0fcbb764bd6)- 116th started on Jan 3, 2019. Get last commit pre-Jan 3, 2021 not related to 117th. [(Dec 7, 2020)](https://github.com/unitedstates/congress-legislators/tree/47223a5a7b96976f2eadfdbbb9646ca0da2b5927)  1. Clone the repo and navigate to it (i.e., make it the working directory) 2. Copy the .yaml files into the associated data subdirectory``` social media infogit show a35d649180d55a0b7d1e381e1774d315371a9188:legislators-social-media.yaml > legislators-social-media-114.yamlgit show b36de263d6d9dbf40b95f0d1dfc1e0fcbb764bd6:legislators-social-media.yaml > legislators-social-media-115.yamlgit show 47223a5a7b96976f2eadfdbbb9646ca0da2b5927:legislators-social-media.yaml > legislators-social-media-116.yamlmv legislators-social-media-114.yaml ../us-right-media/data/01-raw/01-congress-legislatorsmv legislators-social-media-115.yaml ../us-right-media/data/01-raw/01-congress-legislatorsmv legislators-social-media-116.yaml ../us-right-media/data/01-raw/01-congress-legislators biographical info note: the most recent commit hash at the time of this project is `1dfb8b8c73c3f06ff411930fd94a5d945dd4ae94`.cp legislators-historical.yaml ../us-right-media/data/01-raw/01-congress-legislators/legislators-historical.yamlcp legislators-current.yaml ../us-right-media/data/01-raw/01-congress-legislators/legislators-current.yaml```  3. load libraries and specify input/output locations<jupyter_code>import os
dir_inp = os.path.join('..', '..', 'data', '01-raw', '01-congress-legislators')
dir_out = os.path.join('..', '..', 'data', '02-intermediate', '01-congress-legislators')

import pandas as pd
import re
from ruamel.yaml import YAML
yaml=YAML(typ='base')
# include typ='base': otherwise it reads twitter_id as float first, which messes with the digits
# "If you load after doing `yaml = YAML(typ='base'), you will get the baseloader and every scalar loads as a string"
# https://stackoverflow.com/questions/54820256/how-to-read-load-yaml-parameters-with-leading-zeros-as-a-string<jupyter_output><empty_output><jupyter_text>4. extract data from .yaml files into dataframes<jupyter_code>def extract_social_media(fn):
    """Extract social media information for congressional legislators.
     
    Args:
        fn (str): name of .yaml file
        
    Returns:
        A dataframe where each row corresponds to a legislator.
    
    """

    with open(os.path.join(dir_inp, fn)) as stream:
        data = yaml.load(stream)
        df = pd.DataFrame(data)
    print(f'The {fn} file contains {len(df)} legislators.')
    
    df_soc = pd.DataFrame({'bioguide': [x.get('bioguide') for x in df['id']],
                            'thomas': [x.get('thomas') for x in df['id']],
                            'govtrack': [x.get('govtrack') for x in df['id']],
                            'twitter': [x.get('twitter') for x in df['social']],
                            'twitter_id': [x.get('twitter_id') for x in df['social']],
                            'facebook': [x.get('facebook') for x in df['social']],
                            'facebook_id': [x.get('facebook_id') for x in df['social']],
                            'youtube_id': [x.get('youtube_id') for x in df['social']],
                            'congress': re.findall(r'\d+', fn)[0]
                            })
    
    return df_soc
soc_114 = extract_social_media('legislators-social-media-114.yaml')
soc_115 = extract_social_media('legislators-social-media-115.yaml')
soc_116 = extract_social_media('legislators-social-media-116.yaml')

soc = soc_114.append(soc_115).append(soc_116).reset_index(drop=True)
soc.to_pickle(os.path.join(dir_out, 'legislators-social-media.pkl'))

print(f'The combined dataframe contains {len(soc)} rows. There are duplicate entries when legislators serve more than 1 term.')
# show dataframe
soc
# show terms served by legislator
soc.groupby(['bioguide', 'congress']).size()
def extract_bio_info(fn):
    """Extract biographical information for congressional legislators.
     
    Args:
        fn (str): name of .yaml file
        
    Returns:
        A dataframe where each row corresponds to a legislator.
        
    """
     
    with open(os.path.join(dir_inp, fn)) as stream:
        data = yaml.load(stream)
        df = pd.DataFrame(data)
        print(f'The {fn} file contains {len(df)} legislators.')
    
    df_bio = pd.DataFrame({'bioguide': [x.get('bioguide') for x in df['id']],
                            'thomas': [x.get('thomas') for x in df['id']],
                            'lis': [x.get('lis') for x in df['id']],
                            'govtrack': [x.get('govtrack') for x in df['id']],
                            'opensecrets': [x.get('opensecrets') for x in df['id']],
                            'votesmart': [x.get('votesmart') for x in df['id']],
                            'fec': [x.get('fec') for x in df['id']],
                            'cspan': [x.get('cspan') for x in df['id']],
                            'wikipedia': [x.get('wikipedia') for x in df['id']],
                            'house_history': [x.get('house_history') for x in df['id']],
                            'ballotpedia': [x.get('ballotpedia') for x in df['id']],
                            'maplight': [x.get('maplight') for x in df['id']],
                            'icpsr': [x.get('icpsr') for x in df['id']],
                            'wikidata': [x.get('wikidata') for x in df['id']],
                            'google_entity_id': [x.get('google_entity_id') for x in df['id']],
                            'first': [x.get('first') for x in df['name']],
                            'last': [x.get('last') for x in df['name']],
                            'official_full': [x.get('official_full') for x in df['name']],
                            'birthday': [x.get('birthday') for x in df['bio']],
                            'gender': [x.get('gender') for x in df['bio']],
                            'terms': [x for x in df['terms']],
                            'leadership_roles': [x for x in df['leadership_roles']],
                            'other_names': [x for x in df['other_names']],
                            'family': [x for x in df['family']]}
                            )
                            
    return df_bio
bio_curr = extract_bio_info('legislators-current.yaml')
bio_hist = extract_bio_info('legislators-historical.yaml')

bio = bio_curr.append(bio_hist).reset_index(drop=True)
bio.to_pickle(os.path.join(dir_out, 'legislators-biographical.pkl'))

print(f'The combined dataframe contains {len(bio)} legislators.')<jupyter_output>The legislators-current.yaml file contains 538 legislators.
The legislators-historical.yaml file contains 12045 legislators.
The combined dataframe contains 12583 legislators.<jupyter_text>5. merge social media and biographical dataframes- "`bioguide`: The alphanumeric ID for this legislator in [http://bioguide.congress.gov](http://bioguide.congress.gov). Note that at one time some legislators (women who had changed their name when they got married) had two entries on the bioguide website. Only one bioguide ID is included here. This is the best field to use as a primary key." [(source: congress-legislators)](https://github.com/unitedstates/congress-legislators)<jupyter_code># check that bioguide as primary key makes sense
# use bio as base dataframe and join soc to it
for key in ['bioguide', 'thomas', 'govtrack']:
    print(f"{key}: missing {soc[key].isna().sum()} entries")

print(f"every row in `bio` has a unique bioguide: {len(bio)==bio.bioguide.nunique()}")
print(f"every row in `soc` has a unique bioguide: {len(soc)==soc.bioguide.nunique()}")
# sort by bioguide and congress and then only keep the most recent entry to collect the most updated social media information
soc_sorted = soc.sort_values(by=['bioguide','congress'])
soc_latest = soc_sorted.drop_duplicates('bioguide', keep='last').reset_index(drop=True)
soc_latest
# check: before + after filtering
# example ID
display(soc.loc[soc['bioguide']=='K000386'])
display(soc_latest.loc[soc_latest['bioguide']=='K000386'])

# count of legislators is the same for 116th (i.e., most recent) and decreases for 114th and 115th since some legislators also served in 116th
display(soc.groupby('congress').size())
display(soc_latest.groupby('congress').size())<jupyter_output><empty_output><jupyter_text>- `bio` contains legislators who only served before the 114th session. We want to exclude these legislators from the merged dataframe since we are only interested in the 114th session onwards.- To do so, we use an inner join between `bio` and `soc` based on `bioguide` to only keep legislators from the 114-116th sessions.<jupyter_code># because there are overlapping keys (thomas, govtrack) between `soc` and `bio`,
# first select the keys we want to merge from `soc` into `bio`. This avoids creating thomas_x, thomas_y, govtrack_x, govtrack_y.
# get the difference between the two dataframes' columns. Add `bioguide` back in since it is the key that is used for joining.
merge_cols = soc_latest.columns.difference(bio.columns).to_list() + ['bioguide']
merge_cols
pol = pd.merge(left=bio,
                   right=soc_latest[merge_cols],
                   how='inner', # Use intersection of keys from both frames
                   on='bioguide', # Column or index level names to join on. Must be found in both the left and right DataFrame and/or Series objects.
                   validate='one_to_one') # “one_to_one” or “1:1”: checks if merge keys are unique in both left and right datasets.

display(pol[['official_full', 'twitter', 'twitter_id']])
print(pol.columns)
# Extract information about term(s) served
pol['terms_party'] = pol['terms'].map(lambda x: [term.get('party', None) for term in x])
pol['latest_party'] = pol['terms_party'].map(lambda x: x[-1])
pol['terms_type'] = pol['terms'].map(lambda x: [term.get('type', None) for term in x])
pol['terms_state'] = pol['terms'].map(lambda x: [term.get('state', None)  for term in x])
pol['latest_state'] = pol['terms_state'].map(lambda x: x[-1])
pol['terms_district'] = pol['terms'].map(lambda x: [term.get('district', None) for term in x])
# merge state/district lists into tuples (state, district number if applicable)
pol['terms_region'] = [list(x) for x in map(zip, pol['terms_state'], pol['terms_district'])]
pol<jupyter_output><empty_output><jupyter_text>6. Manually check/add Twitter accounts if account information isn't available from congress-legislators<jupyter_code># members with no Twitter info
bioguides_no_tw = (pol.groupby(['bioguide'])['twitter'] # same set when using ['twitter_id']
                      .count()
                      .sort_values()
                      .where(lambda x : x<1).dropna()
                      .index.tolist())

pol[pol['bioguide'].isin(bioguides_no_tw)][['bioguide', 'official_full', 'twitter', 'terms_state', 'latest_party']]<jupyter_output><empty_output><jupyter_text>[Finding Twitter User IDs](https://gist.github.com/kentbrew/8942accb5c584f11a775af02d097dd40)- search name in Google, look for verified accounts- twitter ID is after `profile_banners` in HTML- can find account page using `https://twitter.com/intent/user?user_id=`<jupyter_code># https://stackoverflow.com/a/38467449:
# conditional lookup using .loc, don't use chained indexing since it can return either a view of the original or a separate copy

# 272 <NAME>
pol.loc[pol['official_full']=='<NAME>', 'twitter'] = 'timkaine'
pol.loc[pol['official_full']=='<NAME>', 'twitter_id'] = '172858784'

# 484 <NAME> - if adding df_trp in 2-congress-prep.ipynb, this legislator is already excluded from dataset
pol.loc[pol['official_full']=='<NAME>', 'twitter'] = 'DavidVitter'
pol.loc[pol['official_full']=='<NAME>', 'twitter_id'] = '19028248'

# 509 <NAME>
# pol.loc[pol['official_full']=='<NAME>', 'twitter'] = 'RepJohnMica' # private
# pol.loc[pol['official_full']=='<NAME>', 'twitter_id'] = 

# 510 <NAME>
# pol.loc[pol['official_full']=='<NAME>', 'twitter'] = 'RepJeffMiller' # does not exist anymore
# pol.loc[pol['official_full']=='<NAME>', 'twitter_id'] = 

# 522 <NAME>
pol.loc[pol['official_full']=='<NAME>', 'twitter'] = 'AlanGrayson'
pol.loc[pol['official_full']=='<NAME>', 'twitter_id'] = '41017380'

# 548 <NAME>
pol.loc[pol['official_full']=='<NAME>', 'twitter'] = 'RepCharlieDent'
pol.loc[pol['official_full']=='<NAME>', 'twitter_id'] = '242376736'

# 560 <NAME> - Guam delegate; no account
# pol.loc[pol['official_full']=='<NAME>', 'twitter'] = 
# pol.loc[pol['official_full']=='<NAME>', 'twitter_id'] = 

# 656 <NAME>
pol.loc[pol['official_full']=='<NAME>', 'twitter'] = 'justinamash'
pol.loc[pol['official_full']=='<NAME>', 'twitter_id'] = '233842454'

# 658 <NAME>
pol.loc[pol['official_full']=='<NAME>', 'twitter'] = 'LacyClayMO1'
pol.loc[pol['official_full']=='<NAME>', 'twitter_id'] = '584912320'

# 695 <NAME>
pol.loc[pol['official_full']=='<NAME>', 'twitter'] = 'GregForMontana'
pol.loc[pol['official_full']=='<NAME>', 'twitter_id'] = '3420965229'<jupyter_output><empty_output><jupyter_text>7. Export datasets<jupyter_code># print all unique party affiliations across legislators
parties = []
list(map(parties.extend, pol['terms_party'].tolist()))
parties = list(set(parties))
parties
# all politicians together regardless of party affiliation
pol.to_pickle(os.path.join(dir_out, f'politicians-all-parties.pkl'))

# by party affiliation
for party in parties:
    pol_party = pol.loc[pol['latest_party']==party].reset_index(drop=True)
    pol_party.to_pickle(os.path.join(dir_out, f'politicians-{party.lower()}.pkl'))
pol.groupby('latest_party',dropna=False).size()
# misc fun fact: politicians who switched parties
switched = (pol['terms_party'].map(set) # get the set of unique party values 
                  .map(len) # count how many are in the set 
                  .sort_values() # optional (just easier for checking)
                  .where(lambda x : x>1).dropna().index # remove if the party affiliation has stayed constant (i.e., equals 1)
                  .tolist()) # convert to list for looping

with pd.option_context('display.max_colwidth', None):
    for member_idx in switched:
        print('---------------------------------------------------------------------------------------------------------------------------------------')
        display(pol.iloc[member_idx][['official_full', 'twitter', 'terms_party']])<jupyter_output>---------------------------------------------------------------------------------------------------------------------------------------
{'fraction_non_alphanumeric': 0.1609044301301433, 'fraction_numerical': 0.033521756277113186, 'mean_word_length': 3.342180365296804, 'pattern_counts': {'":': 0, '<': 58, '<?xml version=': 0, '>': 61, 'https://': 9, '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': '7', 'dir': 'jupyter-structured-clean-dedup', 'id': '437950', 'license': 'MIT', 'n_tokens_mistral': 5280, 'n_tokens_neox': 4789, 'n_words': 1231, 'path': 'usrightmedia/code/01-congress/01-congress-prep.ipynb', 'repo_name': 'wlmwng/us-right-media', 'size': '15427'}