<jupyter_start><jupyter_text>Prepare validation data Load sales for the period: 2017, January - March (PE, TRG)<jupyter_code>import pandas as pd
import numpy as np
sales2017 =  pd.DataFrame.from_csv('PE-TRG-Jan-Mar-2017.csv').reset_index()

#remove duplications by summing up quantity by days
sales2017 = pd.pivot_table(sales2017, values='Quantity', index=['Locationid','PLU','Year','Month','Day'], aggfunc=np.sum).reset_index()<jupyter_output><empty_output><jupyter_text>Keep only March data for validation<jupyter_code>sales_2017_march = sales2017[(sales2017['Year']==2017)&(sales2017['Month']==3)]<jupyter_output><empty_output><jupyter_text>Raw data properties<jupyter_code>positions = sales_2017_march.groupby(['Locationid','PLU']).size().rename('counts').reset_index()
positions_count = len(positions.index)
locations_count = len(positions.drop_duplicates(['Locationid']).index)
products_count = len(positions.drop_duplicates(['PLU']).index)

def setCountGroup(row):
    if row['counts'] < 10:
        return "1. less than 10"
    elif row['counts'] <= 20:
        return "2. 20 or less"
    elif row['counts'] <= 30:
        return "3. 30 or less"
    else: 
        return "4. more than 30"
    
counts_groups = positions
counts_groups['segment'] = counts_groups.apply(setCountGroup, axis=1)
counts_groups = counts_groups.groupby('segment').size().rename('counts').reset_index()
counts_groups['%'] = counts_groups.apply(lambda r: r['counts']/positions_count*100, axis=1)

rows_in_march = len(sales_2017_march.index)
rows_with_negative = len(sales_2017_march[sales_2017_march['Quantity'] < 0].index)
rows_with_zero = len(sales_2017_march[sales_2017_march['Quantity'] == 0].index)
rows_with_positive = len(sales_2017_march[sales_2017_march['Quantity'] > 0].index)

# ==== output results ====

print("Total unique positions (Location-Recipe) %d" % (positions_count))
print("Total locations %d" % (locations_count))
print("Total recipes %d" % (products_count))

print("Data rows %d" % (rows_in_march))
print("Negative data %.2f%% (%d)" % (rows_with_negative/rows_in_march*100, rows_with_negative))
print("Zero data %.2f%% (%d)" % (rows_with_zero/rows_in_march*100, rows_with_zero))
print("Positive data %.2f%% (%d)" % (rows_with_positive/rows_in_march*100, rows_with_positive))

counts_groups<jupyter_output>Total unique positions (Location-Recipe) 431646
Total locations 515
Total recipes 5213
Data rows 4735802
Negative data 2.85% (134970)
Zero data 1.46% (69156)
Positive data 95.69% (4531676)<jupyter_text>Save to CSV<jupyter_code>sales_2017_march.to_csv('sales-2017-march.csv')<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.14990439770554492, 'fraction_numerical': 0.06386233269598471, 'mean_word_length': 7.2784810126582276, 'pattern_counts': {'":': 0, '<': 20, '<?xml version=': 0, '>': 17, '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': '4', 'dir': 'jupyter-structured-clean-dedup', 'id': '63701', 'license': 'MIT', 'n_tokens_mistral': 1069, 'n_tokens_neox': 910, 'n_words': 171, 'path': 'prepare-data-for-validation.ipynb', 'repo_name': 'xtrmstep/PredictionValidation', 'size': '2615'}