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

问题描述

我使用命令 xlsread 从Excel文件导入数据。数据如下所示：

I想要格式化这些数据，以便输出如下所示：

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

在Excel中，我将使用数据透视表。在MATLAB中是否有一个等价的，或者我将如何开始编码？是 reshape 这里有一个选项吗？ 从档案中读取资料如下：

  data = xlsread（'your_file.xls'）; 
  

这给你一个包含数据的数字矩阵。您可以使用 unique ，然后使用结果作为索引到 accumarray 来收集中间栏中的数据。然后你只需要添加行和列标签：
$ b $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ rowVals，〜，rowIndex = unique（data（：，3） ）;
[colVals，〜，colIndex] = unique（data（:, 1）。'）;
A = accumarray（[rowIndex colIndex]，data（：，2））;
A = [NaN colVals; rowVals A];

结果如上，您的示例数据为：

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

如果您有重复的条目（即具有相同日期和标识符的条目），则上面的值将默认为它们的总和。您可以将<函数句柄提供给 accumarray 如果你想要做别的事情。例如：
$ b $ pre $ A = accumarray（[rowIndex colIndex]，data（：，2），[]，@mean）; ％平均它们
A = accumarray（[rowIndex colIndex]，data（：，2），[]，@（x）x（1））; ％保持第一个入口


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]

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?

解决方案

I'll assume you are reading your data from the file as follows:

data = xlsread('your_file.xls');

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

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屋！