在 MATLAB 中格式化 Excel 工作表中的数据 [英] Formatting data from an excel sheet in MATLAB

问题描述

我使用命令 xlsread 从 excel 文件中导入数据.数据如下所示:

I import data from an excel file using the command xlsread. The data look like the following:

我想格式化这些数据，使输出看起来像:

I would like to format these data so that the output looks like:

A = [NaN 1 2 3; 
    20160101 100 80 90; 
    20170101 150 90 200]

在 excel 中，我会使用数据透视表.MATLAB 中是否有等价物，或者我将如何开始编写代码?reshape 这里是一个选项吗?

In excel, I would use a Pivot table. Is there an equivalent in MATLAB or how would I start to code this? Is reshape an option here?

推荐答案

我假设你是 从文件中读取你的数据如下:

data = xlsread('your_file.xls');

这会为您提供一个包含数据的数字矩阵.然后，您可以通过使用 unique<解析第一列和最后一列来重新组织它/code>，然后将结果用作 的索引accumarray 收集中心列的数据.然后你只需添加行和列标签:

Which gives you a numeric matrix containing your data. You can then reorganize it by parsing your first and last columns using unique, then using the results as indices into accumarray to collect the data in the center column. Then you just add the row and column labels:

[rowVals, ~, rowIndex] = unique(data(:, 3));
[colVals, ~, colIndex] = unique(data(:, 1).');
A = accumarray([rowIndex colIndex], data(:, 2));
A = [NaN colVals; rowVals A];

结果，对于您上面的示例数据:

And the result, for your sample data above:

A =

         NaN           1           2           3
    20160101         100          80         200
    20170101         150          90         200

如果您有重复的条目(即具有相同日期和标识符的条目)，以上将默认将它们相加.您可以向 accumarray函数句柄> 如果你想让它做其他事情.例如:

If you have duplicate entries (i.e. entries that have the same date and identifier), the above will sum them by default. You can provide a function handle to accumarray if you'd like it to do something else. For example:

A = accumarray([rowIndex colIndex], data(:, 2), [], @mean);      % Averages them
A = accumarray([rowIndex colIndex], data(:, 2), [], @(x) x(1));  % Keeps the first entry

这篇关于在 MATLAB 中格式化 Excel 工作表中的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！