<jupyter_start><jupyter_text>115th U.S. Congress Composition and Voting Record - Data wrangling Table of ContentsIntroductionLibrariesVoting record dataCongress members dataMerge voting record and Congress members dataSenate dataHouse of representatives dataCurrent Congress members demographic dataConclusion  Introduction **Background:** With the 2018 U.S. midterm elections right around the corner and the control of Congress at stake, the 115th U.S. Congress might soon drastically change. Indeed, the coming election features of the most __[diverse pool of candidates](https://www.nbcnews.com/politics/first-read/2018-candidate-diversity-goes-beyond-gender-n901956)__, from record numbers of women and LGBTQ candidates to unprecedented racial and religious diversity.This project aims to analyze the composition and voting record of the 115th U.S. Congress on the eve of the 2018 midterm elections.**Goal:**    Prepare data for the analysis phase.  Libraries<jupyter_code>import numpy as np
import pandas as pd<jupyter_output><empty_output><jupyter_text>Voting record data<jupyter_code># Voting record dataset
votes_df = pd.read_csv('data/HS115_votes.csv')

# Preview of the dataset
votes_df.head()
# Drop irrelevant columns from votes df
votes_cols = ['congress', 'prob']
votes_df = votes_df.drop(votes_cols, axis=1)
votes_df['cast_code'].value_counts().to_frame()
# Change values for votes (1: yes vote, 5: no vote, 7 or 9: abstention)
votes_map = {1: 1, 6: 0, 7 : 0.5, 9: 0.5} 
votes_df['cast_code'] = votes_df['cast_code'].map(votes_map)
# Add a vote column
votes_map = {1: 'Yea', 0: 'Nay', 0.5 : 'Abstention'} 
votes_df['vote'] = votes_df['cast_code'].map(votes_map)

# Rename 'icpsr' column
votes_df = votes_df.rename(columns={'icpsr': 'member_id'})
votes_df.head()<jupyter_output><empty_output><jupyter_text>Congress members data<jupyter_code># Members
congress_df = pd.read_csv('data/HS115_members.csv')

# Preview dataset
congress_df.head()
# Drop irrelevant columns from members df
keep_cols =  ['chamber', 'icpsr', 'state_icpsr', 'state_abbrev', 'party_code', 'bioname']
congress_df = congress_df.filter(keep_cols)
congress_df['chamber'].value_counts().to_frame()
# Remove members that are not part of Congress (i.e. President)
congress_df = congress_df[congress_df['chamber'] != 'President']
congress_df['party_code'].value_counts().to_frame()
# Add a 'party' column
party_map = {100: 'D', 200: 'R', 328: 'I'} # Democrat, republican, independent
congress_df['party'] = congress_df['party_code'].map(party_map)
congress_df = congress_df.drop(['party_code'], axis=1)
# Split 'bioname' column into 'last_name' and 'first_name' columns
congress_df[['last_name', 'first_name']] = congress_df['bioname'].str.split(',', expand=True, n=1)
congress_df['last_name'] = congress_df['last_name'].str.capitalize()
congress_df = congress_df.drop('bioname', axis=1)
# Rename 'icpsr' and 'state_icpsr' columns
congress_df = congress_df.rename(columns={'icpsr': 'member_id', 'state_icpsr': 'state_id', 
                                          'state_abbrev': 'state_code'})

# Re-order columns
new_order = ['chamber', 'member_id', 'last_name', 'first_name', 'party', 'state_code', 'state_id']
congress_df = congress_df[new_order]
congress_df.head()<jupyter_output><empty_output><jupyter_text>Merge voting record and Congress members data  Senate data<jupyter_code># Create new dataframe for Senate votes only
senate_votes_df = votes_df[votes_df['chamber'] == 'Senate']

# Pivot Senate voting record dataframe based on Congress members' ID
senate_pivot_df = (senate_votes_df.pivot_table(index=['member_id'], columns='rollnumber', 
                                               values='cast_code').reset_index())
# Remove senators that were part of the 115th Congress for a very limited time (to avoid skewing the results)
minimum_votes = round(senate_votes_df['rollnumber'].nunique() * 0.25) # Must have voted for more than 25% of the bills

row_count = senate_pivot_df.shape[0]
senate_pivot_df = senate_pivot_df.dropna(thresh=minimum_votes)
new_row_count = senate_pivot_df.shape[0]
print(str(row_count - new_row_count) + " row(s) removed.")

senate_pivot_df = senate_pivot_df.fillna(0.5) # Fill missing values with abstentions
# Join voting record dataframe and Congress members dataframe
senate_df = pd.merge(congress_df, senate_pivot_df, on='member_id', how='inner')
senate_df.head()
# Export to CSV
senate_df.to_csv('data/clean_senate_votes.csv', index=False)<jupyter_output><empty_output><jupyter_text>House of representatives data<jupyter_code># Create new dataframe for Senate votes only
house_votes_df = votes_df[votes_df['chamber'] == 'House']

# Pivot Senate voting record dataframe based on Congress members' ID
house_pivot_df = (house_votes_df.pivot_table(index=['member_id'], columns='rollnumber', values='cast_code')
                  .reset_index())
# Remove senators that were part of the 115th Congress for a very limited time (to avoid skewing the results)
row_count = house_pivot_df.shape[0]
house_pivot_df = house_pivot_df.dropna(thresh=minimum_votes)
new_row_count = house_pivot_df.shape[0]
print(str(row_count - new_row_count) + " row(s) removed.")

house_pivot_df = house_pivot_df.fillna(0.5) # Fill missing values with abstentions
# Joing voting record dataframe and Congress members dataframe
house_df = pd.merge(congress_df, house_pivot_df, on='member_id', how='inner')
house_df.head()
# Export to CSV
house_df.to_csv('data/clean_house_votes.csv', index=False)<jupyter_output><empty_output><jupyter_text>Current Congress members demographic data<jupyter_code># Senators
senators_df = pd.read_csv("data/us-senate.csv")

# Preview of the dataset
senators_df.head()
# Representatives
representatives_df = pd.read_csv("data/us-house.csv")

# Preview of the dataset
representatives_df.head()
# Correct spelling in 'LGBT' column name
senators_df = senators_df.rename(columns={'openley_lgbtq': 'openly_lgbtq'})

# Drop irrelevant columns
keep_cols = ['state_name', 'state_code', 'party', 'first_name', 'last_name', 'gender', 'ethnicity', 
             'religion', 'openly_lgbtq', 'date_of_birth']
senators_df = senators_df.filter(keep_cols)
representatives_df = representatives_df.filter(keep_cols)

# Add chamber columns:
senators_df['chamber'] = 'Senate'
representatives_df['chamber'] = 'House'
# Merge senators and representatives df
demo_df = pd.concat([senators_df, representatives_df], axis=0)
demo_df.head()
# Rename 'ethnicity' column
demo_df = demo_df.rename(columns={'ethnicity': 'race'})


# Rename parties and some races
mapping = {'party': {'democrat': 'D', 'republican': 'R', 'independent': 'I'},
           'race': {'white-american': 'White', 'asian-american': 'Asian', 'hispanic-american': 'Hispanic',
                        'african-american': 'African-American', 'pacific-islander': 'Pacific Islander',
                        'native-american': 'Native American'},
           'openly_lgbtq': {'no': 'Straight'}
          }
demo_df.replace(mapping, inplace=True)
# Check for missing values
demo_df.columns[demo_df.isna().any()]
# Fill in missing values in 'religion' column
demo_df['religion'].fillna('Unspecified', inplace=True)

# Update religion columns with broad religions instead of branches
religion_list = demo_df['religion'].unique().tolist()
religion_map = {religion: 'Christianity' for religion in religion_list}
non_christian = {'jewish': 'Judaism', 'muslim': 'Islam', 'hindu': 'Hinduism', 'buddhism': 'Buddhism',
                 'soka-gakkai-buddhist': 'Buddhism', 'jodo-shinshu-buddhist': 'Buddhism', 'deist': 'Deism',
                 'unspecified': 'Unspecified'}
religion_map.update(non_christian)
demo_df['religion'] = demo_df['religion'].map(religion_map)
# Change date of birth to age
demo_df['age'] = pd.to_numeric(demo_df['date_of_birth'].str.split('-').str[0]).apply(lambda x: 2018-x)
demo_df = demo_df.drop(['date_of_birth'], axis=1)
# Capitalize values
cap_cols = ['gender', 'religion', 'openly_lgbtq']
demo_df[cap_cols] = demo_df[cap_cols].apply(lambda x: x.str.capitalize())
demo_df.head()
# Export to CSV
demo_df.to_csv('data/clean_congress_demo.csv', index=False)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.1287674589561382, 'fraction_numerical': 0.011026709139916687, 'mean_word_length': 5.982891360136869, 'pattern_counts': {'":': 0, '<': 25, '<?xml version=': 0, '>': 25, 'https://': 1, 'lorem ipsum': 0, 'www.': 1, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 4, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '6', 'dir': 'jupyter-structured-clean-dedup', 'id': '161053', 'license': 'MIT', 'n_tokens_mistral': 2885, 'n_tokens_neox': 2735, 'n_words': 656, 'path': 'congress-data-wrangling.ipynb', 'repo_name': 'leilasaoud/congress-kmeans-clustering', 'size': '8162'}