Overview

• find negatives
• remove negatives because we cannot take a logarithm of negatives
• negative values are replaced by interpolating between the positive values
• there are so few doesnt matter what we do
• we finally get round to taking logarithms

Taking Logarithms

Identifying Negatives

An inspection on our dataset identifies 6 rows that contain negatives.

import subprocess  
%run ./dataframes/step3_identify_negatives.py  
df = identify_negs()  
subprocess.run(['wl-copy'], input=df.to_html(index=False).encode())  

We can see that those rows don't have negatives for columns terms 8 and above.

import subprocess  
%run ./dataframes/step3_identify_negatives.py  
df = identify_negs()  
df = df.drop(columns=[8.5, 9,    9.5,     10,   10.5,     11,   11.5,     12,   12.5,     13, 13.5,     14,   14.5,     15,   15.5,     16,   16.5,     17,   17.5, 18,   18.5,     19,   19.5,     20,   20.5,     21,   21.5,     22, 22.5,     23,   23.5,     24,   24.5,     25,   25.5,     26,   26.5, 27,   27.5,     28,   28.5,     29,   29.5,     30,   30.5,     31, 31.5,     32,   32.5,     33,   33.5,     34,   34.5,     35,   35.5, 36,   36.5,     37,   37.5,     38,   38.5,     39,   39.5,     40])
subprocess.run(['wl-copy'], input=df.to_html(index=False).encode())  

Replacing negatives

We replace negatives with NaN

import subprocess  
%run ./dataframes/step3_replace_negatives_with_nan.py  
df = replace_negs()  
subprocess.run(['wl-copy'], input=df.to_html(index=False).encode())  

Interpolating to replace the NaNs

import subprocess  
%run ./dataframes/step3_interpolating_over_nan.py  
df = interpolating_nans()  
df = df.loc[[604, 605, 606, 608, 609, 611]]
subprocess.run(['wl-copy'], input=df.to_html(index=False).encode())  

Checks on the interpolation

It is best to add in a check to ensure that the process has been performed correctly...

to do a sense check we obtain a count of the negative values we had

import subprocess  
%run ./dataframes/step3_count_nans.py  
df = count_nans()  

and we consider the sum of the data before and after the interpolation

import subprocess  
%run ./dataframes/step3_sum_before_interpolation.py
df = sum_before_interpolation()  

before the sum is ... 16195.080000000002

import subprocess  
%run ./dataframes/step3_sum_after_interpolation.py
df = sum_after_interpolation()  

after the sum is ... 16197.025000000001

Taking Logarithms

we could improve couple of tables below as losing 1 row with the filter

import subprocess  
%run ./dataframes/step3_taking_logs.py  
df = taking_logs()  
df = df.loc[[604, 605, 606, 608, 609, 611]]
subprocess.run(['wl-copy'], input=df.to_html(index=False).encode())  

Python

Functions

filename: step3_identify_negatives.py

from dataframes.step2_remove_upto_term_5 import *

def identify_negs():
    df = remove_upto_term5()

    # build the mask on numeric columns only
    mask = (df.iloc[:,1:] <= 0).any(axis=1)

    # apply mask to the FULL dataframe (keeps dates)
    identifying_negatives = df[mask]

    return identifying_negatives

filename: step3_replace_negatives_with_nan.py

from dataframes.step3_identify_negatives import *
import numpy as np

def replace_negs():
    df = identify_negs()
    numeric_cols = df.select_dtypes(include='number').columns #this gives you just a list of column names
    df[numeric_cols] = df[numeric_cols].where(df[numeric_cols] > 0, np.nan)
    return df

filename: step3_replace_negatives_with_nan_whole_dataframe.py

from dataframes.step3_identify_negatives import *
import numpy as np

def replace_negs():
    df = remove_upto_term5()
    numeric_cols = df.select_dtypes(include='number').columns #this gives you just a list of column names
    df[numeric_cols] = df[numeric_cols].where(df[numeric_cols] > 0, np.nan)
    return df

filename: step3_interpolating_over_nan.py

from dataframes.step3_replace_negatives_with_nan_whole_dataframe import *
import numpy as np

def interpolating_nans():
    df = replace_negs()
    df[df.columns.difference(['Date'])] = df[df.columns.difference(['Date'])].interpolate()
    return df

filename: step3_count_nans.py

from dataframes.step3_identify_negatives import *
import numpy as np

def count_nans():
    df = remove_upto_term5()
    numeric_cols = df.select_dtypes(include='number').columns #this gives you just a list of column names
    df[numeric_cols] = df[numeric_cols].where(df[numeric_cols] >= 0, np.nan)
    df = df.isna().sum().sum()
    print ( df )
    return df