<commit_before>import pandas as pd
import numpy as np
import collections
import click
from table_aggregator import make_table

from ei_config import cols, converters, lookup_location, lookup_cnae
from ei_config import CNAE_BLACKLISTED, HS_BLACKLISTED
from ei_config import PURCHASES, DEVOLUTIONS, REMITS, CREDITS, TRANSFERS
RECEIVER, SENDER = "receiver", "sender"


'''
EXAMPLE USAGE:

python format_raw_new.py --fname path/to/ei_2013_01.csv --blpath path/to/BlackList_2013_01.csv --odir output/path -y 2013 -m 1
'''

def mylen(x):
    return len(x) if isinstance(x, collections.Iterable) else 1

def setup(df, blacklist_df):
    ''' Calculate value columns from the raw data that will eventually be stored in the database
        Perform intial grouping of data into the deepest nesting form (year, month, sender, receiver, product)
        while summing values aggregating the unique CNAEs'''

    df['icms_tax'] = df.ICMS_ST_Value + df.ICMS_Value 
    df['tax'] = df.icms_tax + df.IPI_Value + df.PIS_Value + df.COFINS_Value + df.II_Value + df.ISSQN_Value

    df["purchase_value"] = df.apply(lambda x: x["product_value"] if x["CFOP_ID"] == PURCHASES else 0, axis=1)
    df["transfer_value"] = df.apply(lambda x: x["product_value"] if x["CFOP_ID"] == TRANSFERS else 0, axis=1)
    df["devolution_value"] = df.apply(lambda x: x["product_value"] if x["CFOP_ID"] == DEVOLUTIONS else 0, axis=1)
    df["icms_credit_value"] = df.apply(lambda x: x["product_value"] if x["CFOP_ID"] == CREDITS else 0, axis=1)
    df["remit_value"] = df.apply(lambda x: x["product_value"] if x["CFOP_ID"] == REMITS else 0, axis=1)

    def uniq_ests(bra, cnae):
        ''' Given a BRA and a CNAE or a BRA and a list of CNAEs compute the total number of establishments'''
        if not isinstance(cnae, collections.Iterable):
            cnae = [cnae]
        total = 0

        if bra.startswith("0XX"):
            return 10000
        elif not bra.startswith("4mg"):
            return 3 * len(cnae)

        for x in cnae:
            if x == "x00":
                total +=3; 
            else:
                try:
                    total += blacklist_df.xs([bra, x]).num_est
                except KeyError:
                    print "[SEVERE WARNING] BRA %s CNAE %s is not defined in Blacklist!" % (bra,x)
        return total
    
    print "Doing groupby..."
    ''' Groups the raw data by bra_id_r, cnae_id_r, bra_id_s, cnae_id_s, and hs '''    
    grouped_df = df.groupby(["bra_id_r", "cnae_id_r", "bra_id_s", "cnae_id_s", "hs_id"]).agg({
                "cnae_r_og": lambda x: set.union(set(x)),
                "cnae_s_og" : lambda x: set.union(set(x)),

                "purchase_value" : pd.Series.sum,
                "transfer_value" : pd.Series.sum,
                "devolution_value" : pd.Series.sum,
                "icms_credit_value" : pd.Series.sum,
                "icms_tax" : pd.Series.sum,
                "tax" : pd.Series.sum,
    })

    grouped_df = grouped_df.reset_index()<commit_msg>updates for handling BRA/CNAE combination not in blacklist<commit_after>import pandas as pd
import numpy as np
import collections
import click
from table_aggregator import make_table

from ei_config import cols, converters, lookup_location, lookup_cnae
from ei_config import CNAE_BLACKLISTED, HS_BLACKLISTED
from ei_config import PURCHASES, DEVOLUTIONS, REMITS, CREDITS, TRANSFERS
RECEIVER, SENDER = "receiver", "sender"


'''
EXAMPLE USAGE:

python format_raw_new.py --fname path/to/ei_2013_01.csv --blpath path/to/BlackList_2013_01.csv --odir output/path -y 2013 -m 1
'''

def mylen(x):
    return len(x) if isinstance(x, collections.Iterable) else 1

def setup(df, blacklist_df):
    ''' Calculate value columns from the raw data that will eventually be stored in the database
        Perform intial grouping of data into the deepest nesting form (year, month, sender, receiver, product)
        while summing values aggregating the unique CNAEs'''

    df['icms_tax'] = df.ICMS_ST_Value + df.ICMS_Value 
    df['tax'] = df.icms_tax + df.IPI_Value + df.PIS_Value + df.COFINS_Value + df.II_Value + df.ISSQN_Value

    df["purchase_value"] = df.apply(lambda x: x["product_value"] if x["CFOP_ID"] == PURCHASES else 0, axis=1)
    df["transfer_value"] = df.apply(lambda x: x["product_value"] if x["CFOP_ID"] == TRANSFERS else 0, axis=1)
    df["devolution_value"] = df.apply(lambda x: x["product_value"] if x["CFOP_ID"] == DEVOLUTIONS else 0, axis=1)
    df["icms_credit_value"] = df.apply(lambda x: x["product_value"] if x["CFOP_ID"] == CREDITS else 0, axis=1)
    df["remit_value"] = df.apply(lambda x: x["product_value"] if x["CFOP_ID"] == REMITS else 0, axis=1)

    def uniq_ests(bra, cnae):
        ''' Given a BRA and a CNAE or a BRA and a list of CNAEs compute the total number of establishments'''
        if not isinstance(cnae, collections.Iterable):
            cnae = [cnae]
        total = 0

        if bra.startswith("0XX"):
            return 10000
        elif not bra.startswith("4mg"):
            return 3 * len(cnae)

        for x in cnae:
            if x == "x00":
                total +=3; 
            else:
                try:
                    total += blacklist_df.xs([bra, x]).num_est
                except KeyError:
                    ''' Per Elton, we treat items not in the blacklist as if they have at least 3 establishments '''
                    total += 3
                    
        return total
    
    print "Doing groupby..."
    ''' Groups the raw data by bra_id_r, cnae_id_r, bra_id_s, cnae_id_s, and hs '''    
    grouped_df = df.groupby(["bra_id_r", "cnae_id_r", "bra_id_s", "cnae_id_s", "hs_id"]).agg({
                "cnae_r_og": lambda x: set.union(set(x)),
                "cnae_s_og" : lambda x: set.union(set(x)),

                "purchase_value" : pd.Series.sum,
                "transfer_value" : pd.Series.sum,
                "devolution_value" : pd.Series.sum,
                "icms_credit_value" : pd.Series.sum,
                "icms_tax" : pd.Series.sum,
                "tax" : pd.Series.sum,
    })

    grouped_df = grouped_df.reset_index()
{'fraction_non_alphanumeric': 0.11794536234254867, 'fraction_numerical': 0.013414035661704564, 'mean_word_length': 2.6698679471788718, 'pattern_counts': {'":': 4, '<': 3, '<?xml version=': 0, '>': 3, '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}}
{'dir': 'git-commits-cleaned', 'id': '6878692', 'n_tokens_mistral': 2213, 'n_tokens_neox': 2035, 'n_words': 566}