ColumnsManager.py

Go to the documentation of this file.

from TemplateManager import TemplateManager 
from ErrorHandler import *
# from copy import deepcopy
from pandas import DataFrame, to_datetime
 
class BuildOttoData:
    """
    Builds a homogenized dataframe according to ottomapping field of each format in out database 
    Checks each ftype and applies different instructions depending of ftype:
    ftype = header: Creates a new columns using field "name".
    ftype = fixed_value: Creates a new column which contains only field "value".
    ftype = custom_code: Creates a new column using the column given by format_type.
    ftype = ignore: Creates a empty column using field "name".
    """
 
    def ftypes(self, df, ftype, Format, filename):
        """
        Maps the columns from almost any template to otto template 
 
        Args:
            df (pandas dataframe): usage report loaded as dataframe
            ftype (list): a list of each column info
            Format (str): template format
            filename (str): current filename
        Returns: df_otto (pandas dataframe)
        """
        df_otto = DataFrame()
        for i in ftype:
            print(i)
            if i["ftype"] == "header":
                if i["name"] == "date" and df[i["value"].lower()].dtype == int:
                    df[i["value"].lower()] = to_datetime(df[i["value"].lower()], format='%Y%m%d')
                df_otto[i["name"]] = df[i["value"].lower()].astype(i["dtype"])
            elif i["ftype"] == "fixed_value":
                    df_otto[i["name"]] = i["value"]
            elif i["ftype"] == "custom_code":
                if i["name"] == "date":
                    date = TemplateManager.getDate(df=df, type_format=Format, filename=filename)
                    df_otto[i["name"]] = date
                    df_otto[i["name"]] = df_otto[i["name"]].astype("datetime64[ns]")
                else:
                    TemplateManager.getType(df=df, type_format=Format, filename=filename)
                    df_otto[i["name"]] = df[i["value"].lower()]
            elif i["ftype"] == "ignore":
                df_otto[i["name"]] = None  
        # TemplateManager.set_full_type(df_otto) 
        df_otto["full_type"] = df_otto.apply(lambda Row: TemplateManager.full_type(Row["type"]), axis=1) 
        return df_otto
 
    def currency_type(self, currency_col, currency):
        """
        Gives a value to each currency 
 
        Args:
            currency_col (pandas Series): column assigned to currency
            exchange (dict): it contains all exchange rates for current report
        Returns: exchange (float)
        """
        for item in currency:
            if item["currency"] == currency_col:
                # values.append(item["value"])
                return item["period"], item["value"]
        # return exchange[currency_col]
 
    def add_currency_exception(self, service_col, currency_col, currency_exceptions):
        """
        Adds currency_exception column to current dataframe iff it is indicate in the input
 
        Args:
            service_col (pandas Series): column assigned to service/dsp
            currency_col (pandas Series): column assigned to currency
            currency_exceptions (dict): it contains all currency exceptions for current report
        Returns: period (str), currency_value(float)
        """
        for item in currency_exceptions:
            if item["dsp"].lower() == service_col:
                currency_value = [currency["value"] for currency in item["currencies"] if currency["currency"] == currency_col][0]
                # print(item["period"], currency_value)
                period = item["period"]
                return period, currency_value
 
    def add_exchange(self, df, exchange, currency_exception=None):
        """
        Adds exchange_rate and total_local columns to current dataframe
 
        Args:
            df (pandas dataframe): usage report loaded as dataframe
            exchange (dict): it contains all exchange rates for current report
            currency_exception (dict): it contains all currency exception rates for current report (if it needs them)
        Returns: df (pandas dataframe)
        """
        try:
            if currency_exception:
                dsp = [i["dsp"].lower() for i in currency_exception]
                df['currency_period'], df['exchange_rate'] = zip(*df.apply(lambda Row: self.add_currency_exception(Row['service_id'].lower(), Row['currency'], currency_exception) if Row['service_id'].lower() in dsp else self.currency_type(Row['currency'], exchange), axis=1))
            else:
                df['currency_period'], df["exchange_rate"] = zip(*df.apply(lambda Row: self.currency_type(Row['currency'], exchange), axis=1))
            df["total_local"] = df["exchange_rate"]*df["total_foreign"]
            return df
        except Exception:
            return "There are currencies not found in your file, please check currencies"
 
    def discount(self, df):
        """
        Makes a discount for selected templates
 
        Args:
            df (pandas dataframe): usage report loaded as dataframe
        Returns: df (pandas dataframe)
        """
        df["total_local"] = df["total_local"]-(df["total_local"]*0.015)
        return df
    
    def add_info_columns(self, *args):
        df = args[0]
        filename = args[1]
        client_id = args[2]
        base_currency = args[3]
 
        from datetime import date 
        df["accounting_date"] = date.today()
        df["accounting_date"] = df["accounting_date"].astype("datetime64[ns]")
        df["full_total_foreign"] = df["total_local"]
        df["file"] = filename
        df["client_id"] = client_id
        df["foreign_currency"] = df["currency"]
        df["local_currency"] = base_currency
        # df["line"] = list(df.index)
 
        if "total_gross" in df.columns and "total_net" in df.columns:
            df = df[["release_id","isrc_id", "quantity", "total_foreign", "territory_code", "date", "release_title", "track_title", "label_id", "artists", 
                    "currency", "type", "service_id", "file", "accounting_date", "exchange_rate", "currency_period", "total_local", "total_gross", "total_net", "client_id"] ]
        else:
            df = df[["release_id","isrc_id", "quantity", "total_foreign", "territory_code", "date", "release_title", "track_title", "label_id", "artists", 
                    "currency", "type", "full_type", "service_id", "file", "accounting_date", "full_total_foreign", "exchange_rate", "local_currency", 
                    "foreign_currency", "currency_period", "total_local", "client_id"] ]
        return df