数学建模竞赛

%导入附件2
data = readtable('数值化.xlsx');

% 获取列名
column_names = data.Properties.VariableNames;

% 使用 renamevars 函数将列名更改为原列名
data = renamevars(data, {'x____','x______','x_____1','x_______1','x__________','x_____2','x_______2','x___'},
                  {'销售日期','扫码销售时间','单品编码','销量(千克)','销售单价(元/千克)','销售类型','是否打折销售','销售额'});

%导入附件1
data1 = readtable('附件1.xlsx');
data1 = renamevars(data1, {'x____', 'x_____1','x_____2','x_____3'},
                   {'单品编码','单品名称','分类编码','分类名称'});

%data2用于储存单品编码与单品名称的映射
data1 = data1(:, {'单品编码', '单品名称','分类名称'});

%将单品名称映射进data中
data2 = join(data, data1, 'Keys', '单品编码');

%将单品名称移到开头
data2 = data2(:, {'单品名称','销售日期','分类名称','扫码销售时间','单品编码','销量(千克)','销售单价(元/千克)','是否打折销售','销售额','销售类型'});

sales = data2.('销量(千克)');

%删除销量为负值的数据，退货不在考虑范围
valid_rows = sales >= 0;
data2 = data2(valid_rows, :);

writetable(data2, '12合并去负值.xlsx');

summaryTable = groupsummary(data2, '分类名称', 'sum', '销量(千克)');
pinglei = summaryTable(:, {'分类名称', 'sum_销量(千克)'});
pinglei = renamevars(pinglei, {'分类名称', 'sum_销量(千克)'}, {'pinleimingcheng', 'Total_Sales_KG'});
writetable(pinglei, '品类与销量.xlsx');

summaryTable = groupsummary(data2, '单品名称', 'sum', '销量(千克)');
danpin = summaryTable(:, {'单品名称', 'sum_销量(千克)'});
danpin = renamevars(danpin, {'单品名称', 'sum_销量(千克)'}, {'danpinmingcheng', 'Total_Sales_KG'});
writetable(danpin, '单品与销量.xlsx');

x = 1:length(pinglei.pinleimingcheng);
plot(x, pinglei.Total_Sales_KG, '-o', 'LineWidth', 2, 'MarkerSize', 8);
xticks(x);
xticklabels(pinglei.pinleimingcheng);
title('商品销售量');
xlabel('商品品类');
ylabel('销售量 (千克)');
legend('销售量');
set(gcf, 'Position', [100, 100, 800, 400]);

%绘制销量的箱线图，发现销量带负值，为退货，有160kg销售鲜粽叶的特殊值
sales = data2.('销量(千克)');
boxplot(sales);
title('销量(千克)的箱线图');
ylabel('销量(千克)');

%绘制销售单价的箱线图
sales1 = data2.('销售单价(元/千克)');
boxplot(sales1);
title('销售单价(元/千克)的箱线图');
ylabel('销售单价(元/千克)');

outliers = false(size(data3));  % 创建与数据表格相同大小的逻辑数组

for col = 2:size(data3, 2)  % 从第二列开始（第一列通常是标签）
    column_data = data3{:, col};  % 获取当前列数据
    Q1 = quantile(column_data, 0.25);  % 计算下四分位数
    Q3 = quantile(column_data, 0.75);  % 计算上四分位数
    IQR = Q3 - Q1;  % 计算IQR

    k = 1.5;  % 调整异常值的阈值
    lower_threshold = Q1 - k * IQR;  % 下界阈值
    upper_threshold = Q3 + k * IQR;  % 上界阈值

    % 识别异常值
    outliers(:, col) = column_data < lower_threshold | column_data > upper_threshold;
end

% 按品类每日汇总销售量
category_sales = varfun(@sum, data2, 'GroupingVariables', {'time','sortname'},'InputVariables', 'xiaoliang');
% 按单品每日汇总销售量
category_sales1 = varfun(@sum, data2, 'GroupingVariables', {'time','danpingname'},'InputVariables', 'xiaoliang');

% 计算相关性
correlation_matrix = corr(table2array(category_sales));

sortname = category_sales.sortname;
time = category_sales.time;
sum_xiaoliang = category_sales.sum_xiaoliang;

unique_sortnames = unique(sortname);

figure;
hold on;

% 遍历每个不同的sortname值，绘制不同的线段
for i = 1:length(unique_sortnames)
    current_sortname = unique_sortnames{i};
    mask = strcmp(sortname, current_sortname);
    plot(time(mask), sum_xiaoliang(mask), 'DisplayName', current_sortname);
end

title('不同品类的销售量折线图');
xlabel('时间');
ylabel('销量');
legend('Location', 'best');
hold off;

% 合并数据
data3 = readtable('附件3.xlsx');
data3 = renamevars(data3, {'x__', 'x____','x__________'}, {'time','singlema','pifajia'});
mergedData = join(data2, data3, 'Keys',{'time','singlema'});

% 进行多元线性回归分析
% ...

%首先计算出收益
data.shouyi = (data.xiaoliang .* data.danjia) - (data.xiaoliang .* data.pifajia ./ (1 - data.sunhao ./100));

%通过时间与单品名称聚类，计算每日单品收益和
category_sales = varfun(@sum, data, 'GroupingVariables', {'time','danpingname'},'InputVariables', 'shouyi');
%通过时间与单品名称聚类，计算每日单品销量和
category_sales1 = varfun(@sum, data, 'GroupingVariables', {'time','danpingname'},'InputVariables', 'xiaoliang');
%合并
mergedData = join(category_sales1, category_sales, 'Keys',{'time','danpingname'});