<jupyter_start><jupyter_text>Comparison between Chamber of Deputies CEAP datasets 1.0 and 2.0This notebook compares the old Chamber's CEAP dataset (the huge XML files) with the new one (CSV by year). The main objective of this comparison is to show we didn't lose any data on the migration from the 1.0 to the much more efficient 2.0 version of the data. This validates changes to serenata-toolbox so we can ditch 1.0 datasets for good and be prepare to their extinction by the Chamber's Open Data team.Let's begin by loading both old and new datasets<jupyter_code>import pandas as pd

pd.set_option('max_columns', 500)
from serenata_toolbox.datasets import Datasets

datasets = Datasets('../data')
datasets.downloader.download('2017-05-21-reimbursements.old.xz')
datasets.downloader.download('2017-05-21-reimbursements.new.xz')
old_dataset = pd.read_csv('../data/2017-05-21-reimbursements.old.xz',
                        compression='xz',
                        low_memory=False)
new_dataset = pd.read_csv('../data/2017-05-21-reimbursements.new.xz',
                        compression='xz',
                        low_memory=False)<jupyter_output><empty_output><jupyter_text>First we need to check if both datasets have the same columns, even in they are in the same order:<jupyter_code>old_keys = old_dataset.keys()
new_keys = new_dataset.keys()

print(old_keys==new_keys)<jupyter_output>[ True  True  True  True  True  True  True  True  True  True  True  True
  True  True  True  True  True  True  True  True  True  True  True  True
  True  True  True  True  True  True  True]<jupyter_text>We can also make sure they have the same types for all columns<jupyter_code>new_dataset.dtypes == old_dataset.dtypes<jupyter_output><empty_output><jupyter_text>Now we can take a slice of the datasets by year and compare their sizes. We also remove the current year, because this ongoing registry seems to have different update pace between versions, so it makes no sense comparing them:<jupyter_code>old_dataset = old_dataset[old_dataset['year'] != 2017]
new_dataset = new_dataset[new_dataset['year'] != 2017]

for year in pd.unique(old_dataset['year']):
    old_size = len(old_dataset[old_dataset['year']==year])
    new_size = len(new_dataset[new_dataset['year']==year])
    print('year: {} old: {} new: {} diff: {}'.format(year, old_size, new_size, new_size-old_size))<jupyter_output>year: 2009 old: 171942 new: 171942 diff: 0
year: 2010 old: 204299 new: 204299 diff: 0
year: 2011 old: 213379 new: 213379 diff: 0
year: 2012 old: 197019 new: 197019 diff: 0
year: 2013 old: 194157 new: 194157 diff: 0
year: 2014 old: 172144 new: 172144 diff: 0
year: 2015 old: 208729 new: 208729 diff: 0
year: 2016 old: 200943 new: 200942 diff: -1<jupyter_text>Oddly enough, there is a single row missing in the new dataset. Let's find out which document is that and also make sure the exact document_ids are present in both datasets:<jupyter_code>new_docs = list(new_dataset['document_id'])
old_docs = list(old_dataset['document_id'])

old_extra = list(set(old_docs) - set(new_docs))
print('Extra documents found in old dataset: {}'.format(len(old_extra)))

new_extra = list(set(new_docs) - set(old_docs))
print('Extra documents found in new dataset: {}'.format(len(new_extra)))<jupyter_output>Extra documents found in old dataset: 1
Extra documents found in new dataset: 0<jupyter_text>So there is really only one inconsistency between datasets. A quick query can show us the culprit:<jupyter_code>old_dataset[old_dataset['document_id'].isin(old_extra)]<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.09506449803701626, 'fraction_numerical': 0.0535614133482894, 'mean_word_length': 4.8, 'pattern_counts': {'":': 0, '<': 22, '<?xml version=': 0, '>': 22, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, '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': '446515', 'license': 'MIT', 'n_tokens_mistral': 1290, 'n_tokens_neox': 1109, 'n_words': 384, 'path': 'research/develop/2017-05-21-luizcavalcanti-chamber-ceap-api-version-comparison.ipynb', 'repo_name': 'richardljs/serenata-de-amor', 'size': '3566'}