<jupyter_start><jupyter_text>Load Data Brokers Import CA data brokers list<jupyter_code>fn = '../data/data_brokers/ca-data-brokers.csv'
df = pd.read_csv(fn)
df['state'] = 'CA'
ca = df[['Data Broker Name', 'Email Address', 'Website URL', 'Physical Address', 'state']].copy()
ca.rename(inplace=True, columns={
    'Data Broker Name':'name',
    'Email Address':'email',
    'Website URL':'url',
    'Physical Address':'address'
})<jupyter_output><empty_output><jupyter_text>Import VT data brokers list<jupyter_code>fn = '../data/data_brokers/vt-data-brokers.csv'
df = pd.read_csv(fn)
df['state'] = "VT"

vt = df[['Data Broker Name:','Address:', 'Email Address:', 'Primary Internet Address:', 'state']].copy()
vt.rename(inplace=True, columns={
    'Data Broker Name:':'name',
    'Address:':'address',
    'Email Address:':'email',
    'Primary Internet Address:':'url'
})<jupyter_output><empty_output><jupyter_text>Merge the two<jupyter_code>brokers = pd.concat([ca, vt])<jupyter_output><empty_output><jupyter_text>Save as output<jupyter_code>brokers.to_csv('../data/matching_process/brokers.csv', index=False)<jupyter_output><empty_output><jupyter_text>Load Lobbyist Clients<jupyter_code>client_list = []

folder = '../data/lobbying/'
for path, dirs, files in os.walk(folder):
    for file in files:
        fullpath = os.path.join(path, file)
        if file.endswith(".xml"):
            with open(fullpath, "rb") as data:
                tree = ET.parse(data)
                root = tree.getroot()
                for filing in root.iter('Filing'):
                    filing_info = filing.attrib
                    for client in filing.iter('Client'):
                        client_info = client.attrib
                        info = {
                            'filing.id': filing_info['ID'],
                            'filing.period': filing_info['Period'],
                            'filing.year': filing_info['Year'],
                            'client.name': client_info['ClientName'],
                            'client.id': client_info['ClientID'],
                            'client.desc': client_info['GeneralDescription'],
                            'client.state': client_info['ClientState'],
                            'client.country': client_info['ClientCountry']
                        }
                        client_list.append(info)

cf = pd.DataFrame(client_list)<jupyter_output><empty_output><jupyter_text>Filter for just 2020 filings<jupyter_code>clients = cf[cf['filing.year'] == '2020'].copy()<jupyter_output><empty_output><jupyter_text>Add bridge to matches<jupyter_code>clients['client.name.check'] = clients['client.name'].str.replace(",","").str.replace(".","").str.upper()<jupyter_output>/Users/maddy/Documents/2021/markup/investigations-data-broker-lobbying/databrokers/lib/python3.7/site-packages/ipykernel_launcher.py:1: FutureWarning: The default value of regex will change from True to False in a future version. In addition, single character regular expressions will*not* be treated as literal strings when regex=True.
  """Entry point for launching an IPython kernel.<jupyter_text>Save as output<jupyter_code>clients.to_csv('../data/matching_process/clients.csv', index=False)<jupyter_output><empty_output><jupyter_text>Guess Matches<jupyter_code>brokers['name.check'] = brokers['name'].str.replace(",","").str.replace(".","").str.upper()
unique_clients = pd.DataFrame()
unique_clients['client.name.check'] = clients['client.name.check'].unique()

choices = list(brokers['name.check'].unique())
choices.extend([
    'EQUIFAX',
    'EXPERIAN',
    'X-MODE',
    'IHS MARITIME & TRADE',
    'ACXIOM',
    'DELOITTE', 
    'PUBLICIS GROUP', 
    'ORACLE',
    'ACCENTURE FEDERAL SERVICES',
    'RELX',
    'ELSEVIER',
    'LIVERAMP',
    'INMAR',
    'EPSILON DATA'])

def guess(client):
    if client in choices:
        return client, 100
    pick, score = process.extractOne(client, choices)
    return pick, score<jupyter_output><empty_output><jupyter_text>First Pass: Data Broker Name List<jupyter_code>unique_clients['guess'] = unique_clients['client.name.check'].parallel_apply(guess)
unique_clients[['guess.name', 'guess.confidence']] = unique_clients['guess'].apply(pd.Series)<jupyter_output><empty_output><jupyter_text>Export for Human Double-Checking<jupyter_code>describe = unique_clients['guess.confidence'].describe()
guesses = unique_clients[unique_clients['guess.confidence'] > describe['75%']].sort_values(by='guess.confidence', ascending=False)
guesses.to_csv('../data/matching_process/match-guesses.csv', index=False)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.15359618717504334, 'fraction_numerical': 0.004332755632582322, 'mean_word_length': 3.7353846153846155, 'pattern_counts': {'":': 0, '<': 44, '<?xml version=': 0, '>': 45, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 1}, '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': '11', 'dir': 'jupyter-structured-clean-dedup', 'id': '668342', 'license': 'Unlicense', 'n_tokens_mistral': 1554, 'n_tokens_neox': 1480, 'n_words': 248, 'path': 'notebooks/0-computer-matching.ipynb', 'repo_name': 'thbland/investigation-data-broker-lobbying', 'size': '4616'}