ModelMuse02/AI_Sales_Forecasting

1/**2 * Data Preprocessing Module3 * Handles data validation, cleaning, outlier detection, and aggregation4 */5 6import {7  RawDataRow,8  CleanedDataRow,9  MonthlyAggregation,10  ProductAggregation,11  DataValidationResult,12} from '../types';13 14// Column name patterns for automatic detection15const DATE_PATTERNS = ['date', 'time', 'datetime', 'order_date', 'sale_date', 'transaction_date', 'day', 'period'];16const REVENUE_PATTERNS = ['revenue', 'sales', 'amount', 'total', 'value', 'price', 'income'];17const UNITS_PATTERNS = ['units', 'quantity', 'qty', 'count', 'items', 'sold', 'units_sold'];18const PRODUCT_PATTERNS = ['product', 'item', 'sku', 'name', 'category', 'product_name', 'item_name'];19const COST_PATTERNS = ['cost', 'expense', 'cogs', 'unit_cost', 'purchase_price', 'wholesale'];20 21/**22 * Detect column type based on name patterns23 */24function matchColumn(columnName: string, patterns: string[]): boolean {25  const normalized = columnName.toLowerCase().replace(/[_\s-]/g, '');26  return patterns.some(pattern => normalized.includes(pattern.replace(/[_\s-]/g, '')));27}28 29/**30 * Auto-detect relevant columns from the dataset31 */32export function detectColumns(columns: string[]): DataValidationResult['detectedColumns'] {33  const detected: DataValidationResult['detectedColumns'] = {34    date: null,35    product: null,36    revenue: null,37    units: null,38    cost: null,39  };40 41  for (const col of columns) {42    if (!detected.date && matchColumn(col, DATE_PATTERNS)) {43      detected.date = col;44    } else if (!detected.revenue && matchColumn(col, REVENUE_PATTERNS)) {45      detected.revenue = col;46    } else if (!detected.units && matchColumn(col, UNITS_PATTERNS)) {47      detected.units = col;48    } else if (!detected.product && matchColumn(col, PRODUCT_PATTERNS)) {49      detected.product = col;50    } else if (!detected.cost && matchColumn(col, COST_PATTERNS)) {51      detected.cost = col;52    }53  }54 55  return detected;56}57 58/**59 * Parse various date formats60 */61function parseDate(value: string | number | null | undefined): Date | null {62  if (value === null || value === undefined) return null;63  64  const strValue = String(value).trim();65  if (!strValue) return null;66 67  // Try standard date parsing68  const date = new Date(strValue);69  if (!isNaN(date.getTime())) return date;70 71  // Try DD/MM/YYYY format72  const ddmmyyyy = strValue.match(/^(\d{1,2})[\/\-](\d{1,2})[\/\-](\d{4})$/);73  if (ddmmyyyy) {74    const [, day, month, year] = ddmmyyyy;75    const parsed = new Date(parseInt(year), parseInt(month) - 1, parseInt(day));76    if (!isNaN(parsed.getTime())) return parsed;77  }78 79  // Try MM/DD/YYYY format80  const mmddyyyy = strValue.match(/^(\d{1,2})[\/\-](\d{1,2})[\/\-](\d{4})$/);81  if (mmddyyyy) {82    const [, month, day, year] = mmddyyyy;83    const parsed = new Date(parseInt(year), parseInt(month) - 1, parseInt(day));84    if (!isNaN(parsed.getTime())) return parsed;85  }86 87  return null;88}89 90/**91 * Parse numeric values92 */93function parseNumber(value: string | number | null | undefined): number | null {94  if (value === null || value === undefined) return null;95  if (typeof value === 'number') return isNaN(value) ? null : value;96  97  const strValue = String(value).trim().replace(/[$,€£¥]/g, '').replace(/\s/g, '');98  if (!strValue) return null;99  100  const num = parseFloat(strValue);101  return isNaN(num) ? null : num;102}103 104/**105 * Detect outliers using IQR method106 */107export function detectOutliers(values: number[], multiplier: number = 1.5): { indices: number[]; bounds: { lower: number; upper: number } } {108  const sorted = [...values].sort((a, b) => a - b);109  const q1 = sorted[Math.floor(sorted.length * 0.25)];110  const q3 = sorted[Math.floor(sorted.length * 0.75)];111  const iqr = q3 - q1;112  const lower = q1 - multiplier * iqr;113  const upper = q3 + multiplier * iqr;114  115  const indices: number[] = [];116  values.forEach((val, idx) => {117    if (val < lower || val > upper) {118      indices.push(idx);119    }120  });121  122  return { indices, bounds: { lower, upper } };123}124 125/**126 * Handle outliers by capping to bounds127 */128function handleOutlier(value: number, bounds: { lower: number; upper: number }): number {129  if (value < bounds.lower) return bounds.lower;130  if (value > bounds.upper) return bounds.upper;131  return value;132}133 134/**135 * Validate and clean the raw dataset136 */137export function validateAndCleanData(rawData: RawDataRow[]): {138  validation: DataValidationResult;139  cleanedData: CleanedDataRow[];140} {141  const errors: string[] = [];142  const warnings: string[] = [];143  144  if (!rawData || rawData.length === 0) {145    return {146      validation: {147        isValid: false,148        errors: ['No data provided'],149        warnings: [],150        detectedColumns: { date: null, product: null, revenue: null, units: null, cost: null },151        rowCount: 0,152        duplicatesRemoved: 0,153        missingValuesHandled: 0,154        outliersDetected: 0,155      },156      cleanedData: [],157    };158  }159 160  // Detect columns161  const columns = Object.keys(rawData[0]);162  const detected = detectColumns(columns);163 164  // Validate required columns165  if (!detected.date) {166    errors.push('Could not detect date column. Please ensure your data has a column like "Date", "Order Date", etc.');167  }168  if (!detected.revenue) {169    errors.push('Could not detect revenue column. Please ensure your data has a column like "Revenue", "Sales", "Amount", etc.');170  }171 172  if (errors.length > 0) {173    return {174      validation: {175        isValid: false,176        errors,177        warnings,178        detectedColumns: detected,179        rowCount: rawData.length,180        duplicatesRemoved: 0,181        missingValuesHandled: 0,182        outliersDetected: 0,183      },184      cleanedData: [],185    };186  }187 188  // Process data189  let duplicatesRemoved = 0;190  let missingValuesHandled = 0;191  const seen = new Set<string>();192  const validRows: CleanedDataRow[] = [];193 194  for (const row of rawData) {195    const date = parseDate(row[detected.date!]);196    const revenue = parseNumber(row[detected.revenue!]);197    198    if (!date) {199      missingValuesHandled++;200      continue;201    }202 203    // Handle missing revenue with median imputation later204    if (revenue === null || revenue < 0) {205      missingValuesHandled++;206      continue;207    }208 209    const product = detected.product ? String(row[detected.product] || 'Unknown') : 'Unknown';210    const unitsSold = detected.units ? parseNumber(row[detected.units]) ?? 1 : 1;211    const cost = detected.cost ? parseNumber(row[detected.cost]) : null;212    const profit = cost !== null ? revenue - cost : null;213 214    // Check for duplicates215    const key = `${date.toISOString()}_${product}_${revenue}`;216    if (seen.has(key)) {217      duplicatesRemoved++;218      continue;219    }220    seen.add(key);221 222    validRows.push({223      date,224      dateStr: date.toISOString().split('T')[0],225      product,226      unitsSold,227      revenue,228      cost,229      profit,230    });231  }232 233  // Handle outliers in revenue234  const revenues = validRows.map(r => r.revenue);235  const outlierResult = detectOutliers(revenues);236  let outliersDetected = outlierResult.indices.length;237 238  if (outliersDetected > 0) {239    warnings.push(`Detected ${outliersDetected} outliers in revenue data. Values have been capped.`);240    outlierResult.indices.forEach(idx => {241      validRows[idx].revenue = handleOutlier(validRows[idx].revenue, outlierResult.bounds);242    });243  }244 245  // Sort by date246  validRows.sort((a, b) => a.date.getTime() - b.date.getTime());247 248  if (validRows.length < 12) {249    warnings.push('Less than 12 data points. Forecasting accuracy may be limited.');250  }251 252  if (!detected.product) {253    warnings.push('No product column detected. Product-level analysis will be limited.');254  }255 256  if (!detected.cost) {257    warnings.push('No cost column detected. Profit margin analysis will not be available.');258  }259 260  return {261    validation: {262      isValid: true,263      errors,264      warnings,265      detectedColumns: detected,266      rowCount: rawData.length,267      duplicatesRemoved,268      missingValuesHandled,269      outliersDetected,270    },271    cleanedData: validRows,272  };273}274 275/**276 * Aggregate data to monthly level277 */278export function aggregateMonthly(data: CleanedDataRow[]): MonthlyAggregation[] {279  const monthlyMap = new Map<string, {280    date: Date;281    revenues: number[];282    units: number[];283    costs: number[];284    products: Set<string>;285    count: number;286  }>();287 288  for (const row of data) {289    const period = `${row.date.getFullYear()}-${String(row.date.getMonth() + 1).padStart(2, '0')}`;290    291    if (!monthlyMap.has(period)) {292      monthlyMap.set(period, {293        date: new Date(row.date.getFullYear(), row.date.getMonth(), 1),294        revenues: [],295        units: [],296        costs: [],297        products: new Set(),298        count: 0,299      });300    }301 302    const entry = monthlyMap.get(period)!;303    entry.revenues.push(row.revenue);304    entry.units.push(row.unitsSold);305    if (row.cost !== null) entry.costs.push(row.cost);306    entry.products.add(row.product);307    entry.count++;308  }309 310  const result: MonthlyAggregation[] = [];311  312  for (const [period, entry] of monthlyMap) {313    const totalRevenue = entry.revenues.reduce((a, b) => a + b, 0);314    const totalUnits = entry.units.reduce((a, b) => a + b, 0);315    const totalCost = entry.costs.length > 0 ? entry.costs.reduce((a, b) => a + b, 0) : null;316    317    result.push({318      period,319      date: entry.date,320      totalRevenue,321      totalUnits,322      totalCost,323      totalProfit: totalCost !== null ? totalRevenue - totalCost : null,324      productCount: entry.products.size,325      transactionCount: entry.count,326    });327  }328 329  return result.sort((a, b) => a.date.getTime() - b.date.getTime());330}331 332/**333 * Aggregate data by product334 */335export function aggregateByProduct(data: CleanedDataRow[]): ProductAggregation[] {336  const productMap = new Map<string, {337    revenue: number;338    units: number;339    count: number;340  }>();341 342  const totalRevenue = data.reduce((sum, row) => sum + row.revenue, 0);343 344  for (const row of data) {345    if (!productMap.has(row.product)) {346      productMap.set(row.product, { revenue: 0, units: 0, count: 0 });347    }348    const entry = productMap.get(row.product)!;349    entry.revenue += row.revenue;350    entry.units += row.unitsSold;351    entry.count++;352  }353 354  const result: ProductAggregation[] = [];355  356  for (const [product, entry] of productMap) {357    result.push({358      product,359      totalRevenue: entry.revenue,360      totalUnits: entry.units,361      transactionCount: entry.count,362      percentage: (entry.revenue / totalRevenue) * 100,363      averageOrderValue: entry.revenue / entry.count,364    });365  }366 367  return result.sort((a, b) => b.totalRevenue - a.totalRevenue);368}369