<jupyter_start><jupyter_text>Process and feature engineering on the logistics dataset1. Load data2. Create an out-of-stock binary variable3. Create window calculations of the last value and rolling averages<jupyter_code>import os

import pandas as pd
import numpy as np
%matplotlib inline
# Primary Dataset
lc_data = pd.read_csv("final_data/contraceptive_logistics_data.csv")
lcd_date_dict = dict(year = lc_data.year,
                     month = lc_data.month,
                     day = [28]*len(lc_data))
lc_data['year_month']  = pd.to_datetime(lcd_date_dict)
lc_data.to_csv("lc_data.csv",index=False)
lc_data.head()<jupyter_output><empty_output><jupyter_text>Feature Engineering__Potential__- Lagging indicator- Out of stock variable- Clustering label for products- Clustering label for sites__Notes__- timeseries data is not symmetrical- We need to write our own algorithm to handle the calculation of lagging indicators  Stockout during period<jupyter_code># Compute an out of stock variable
have_stock = lc_data["stock_stockout_days"] == 0 
stock_event = []
for x in have_stock:
    stock_event.append(int(x == False))
lc_data["out_of_stock_during_period"] = stock_event
lc_data['stock_change'] = lc_data['stock_initial'] - lc_data['stock_end']
lc_data['stock_pct_change'] = (lc_data['stock_initial'] - lc_data['stock_end'])/ lc_data['stock_initial']
lc_data.head()<jupyter_output><empty_output><jupyter_text>Calculate timeseries indicators for each site and product- Calculate last value- Calculate last value 12 months ago- Calculate average value for the last 3 periods- Calculate average value for the last 6 periods- Calculate average value for the last 12 periods- Calculate min value last 3 periods- Calculate min value last 6 periods- Calculate min value last 12 periods- Calculate max value last 3 periods- Calculate max value last 6 periods- Calculate max value last 12 periods<jupyter_code>numeric_cols = ['stock_initial',
                'stock_received',
                'stock_distributed',
                'stock_ordered',
                'stock_adjustment',
                'stock_end',
                'average_monthly_consumption']
lc_data_grouped = lc_data.set_index(['site_code','product_code'])
test_df = lc_data_grouped.loc[('C1024','AS21126'),:].sort_values(by='year_month').set_index('year_month',append=True)

#test_df
# Moves values one step into the series
test_df['stock_distributed'].shift(1)

# Moves Values one step backward in the series
test_df['stock_distributed'].shift(-1)
# Takes the rolling average of one value
test_df['stock_distributed'].rolling(3).mean().shift(1)
site_products = lc_data[['site_code','product_code']].drop_duplicates()
timeseries_data = {
    'lv':[],
    'lv_1yr':[],
    "avg_3":[],
    "avg_6":[],
    'avg_12':[],
    'min_3':[],
    'min_6':[],
    "min_12":[],
    "max_3":[],
    "max_6":[],
    "max_12":[]}

numeric_cols = ['stock_initial','stock_received','stock_distributed','stock_adjustment','stock_end','average_monthly_consumption']
lc_data_grouped = lc_data.set_index(['site_code','product_code'])

for index, data in site_products.iterrows():
    # Filter data to site, product pair
    site_key = data['site_code']
    product_key = data['product_code']
    
    site_product_ts = lc_data_grouped.loc[(site_key,product_key),:].sort_values(by='year_month').set_index('year_month',append=True)
    #Calculate last value
    lv = site_product_ts[numeric_cols].shift(-1)
    lv.columns = [ col+ "_lv" for col in numeric_cols]
    timeseries_data['lv'].append(lv)
    
    #Calculate last value 12 months ago
    lv_1yr = site_product_ts[numeric_cols].shift(-12)
    lv_1yr.columns = [ col+ "_lv_1yr" for col in numeric_cols]
    timeseries_data['lv_1yr'].append(lv_1yr)
    
    #Calculate average value for the last 3 periods
    avg_3 = site_product_ts[numeric_cols].rolling(3).mean().shift(1)
    avg_3.columns = [ col+ "_avg3" for col in numeric_cols]
    timeseries_data['avg_3'].append(avg_3)
    
    #Calculate average value for the last 6 periods
    avg_6 = site_product_ts[numeric_cols].rolling(6).mean().shift(1)
    avg_6.columns = [ col+ "_avg6" for col in numeric_cols]
    timeseries_data['avg_6'].append(avg_6)
    
    #Calculate average value for the last 12 periods
    avg_12 = site_product_ts[numeric_cols].rolling(12).mean().shift(1)
    avg_12.columns = [ col+ "_avg12" for col in numeric_cols]
    timeseries_data['avg_12'].append(avg_12)
    
    #Calculate min value last 3 periods
    min_3 = site_product_ts[numeric_cols].rolling(3).min().shift(1)
    min_3.columns = [ col+ "_min3" for col in numeric_cols]
    timeseries_data['min_3'].append(min_3)
    
    #Calculate min value last 6 periods
    min_6 =site_product_ts[numeric_cols].rolling(6).min().shift(1)
    min_6.columns = [ col+ "_min6" for col in numeric_cols]
    timeseries_data['min_6'].append(min_6)
    
    #Calculate min value last 12 periods
    min_12 = site_product_ts[numeric_cols].rolling(12).min().shift(1)
    min_12.columns = [ col+ "_min12" for col in numeric_cols]
    timeseries_data['min_12'].append(min_12)
    
    #Calculate max value last 3 periods
    max_3 = site_product_ts[numeric_cols].rolling(3).max().shift(1)
    max_3.columns = [ col+ "_max3" for col in numeric_cols]
    timeseries_data['max_3'].append(max_3)
    
    #Calculate max value last 6 periods
    max_6 =site_product_ts[numeric_cols].rolling(6).max().shift(1)
    max_6.columns = [ col+ "_max6" for col in numeric_cols]
    timeseries_data['max_6'].append(max_6)
    
    #Calculate max value last 12 periods
    max_12 = site_product_ts[numeric_cols].rolling(12).max().shift(1)
    max_12.columns = [ col+ "_max12" for col in numeric_cols]
    timeseries_data['max_12'].append(max_12)
# Add timeseries step to map as index
analytical_ts = {key:pd.concat(timeseries_data[key]) for key in timeseries_data.keys() }

# Add timeseries step data for merging
merged_analaytics_ts_df =pd.concat(analytical_ts.values(),axis = 1)
print("Shape of the generated analytics features")
print(merged_analaytics_ts_df.shape)

# Merging the transformed features onto the original data
analytical_lc_data = lc_data.set_index(["site_code",'product_code','year_month'])
analytical_lc_data = analytical_lc_data.merge(merged_analaytics_ts_df,left_index=True,right_index=True)

print("Shape of the analytics dataset after transformations")
print(analytical_lc_data.shape)

analytical_lc_data.reset_index().to_csv("analytical_data/analytical_logistics_data.csv",index=False)<jupyter_output>Shape of the generated analytics features
(38842, 66)
Shape of the analytics dataset after transformations
(38842, 81)
{'fraction_non_alphanumeric': 0.11428571428571428, 'fraction_numerical': 0.024661654135338346, 'mean_word_length': 4.83421052631579, 'pattern_counts': {'":': 6, '<': 12, '<?xml version=': 0, '>': 12, '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}}
{'chain_length': '3', 'dir': 'jupyter-structured-clean-dedup', 'id': '236247', 'license': 'Apache-2.0', 'n_tokens_mistral': 2311, 'n_tokens_neox': 2158, 'n_words': 507, 'path': 'Process and Feature Engineering Primary Data.ipynb', 'repo_name': 'Ayiti-Analytics/usaid_foecasting_challenge', 'size': '6650'}