Group numbers from excel column based on character

3 次查看(过去 30 天)
I have an excel spreadsheet, with several sheets. The column is made up of positive and negative numbers which alternate in continuous, uneven segments. I would like to group consecutive numbers that are positive, then begin a new group when a negative number is reached, and group these until a positive number is reached at which point a new group is made etc.. etc..
Then I want to sum the grouped values for each group...
e.g., curv 1 2 3 4 -2 -3 4 6 6 -3 -6 -5 -7 2 1 5
Any ideas? cheers

回答(3 个)

Iain
Iain 2013-5-16
Theres a couple methods:
Threshold your column to create a logical array. (arr = col > 0 ) Create a label array (eg. label = bwlabel(arr) ) Loop from 1 to the highest number in the label array and calculate the average. for i = 1:max(label) average(i) = mean(col(i==label)); end Average now contains the averages of each of the positive groups. You'll need to repeat for the negative.
Alternatively you can do something like:
group = 0; groupsize = 0; sum = 0; for i = 1:numel(col) if sign(sum) == sign(col(i)) sum = sum + 1; groupsize = groupsize + 1; elseif sum == 0 sum = col(i); groupsize = 1; else group = group + 1; average(group) = sum/groupsize; groupsize = 1; sum = col(i); end end group = group + 1; average(group) = sum/groupsize;
  2 个评论
Josh
Josh 2013-5-16
Apologies for my stupidity, I am pretty new to Matlab. Could you run me through the coding for this step-by-step?
Iain
Iain 2013-5-17
If you take the code I'd put in my prior answer, and insert new lines after each ";", after for i=1:numel(col), after if sign(sum)==sign(col(i)), elseif sum == 0, else and after each end, you will get the code you need.
The logic of the "if" statements 1: check to see if the current element of your column (col) has the same sign as a running total, and if so, add it to the running total, and if not, but sum ==0, set sum equal to the current element, and in the other case, calculate the running average of what must be the previous group.

请先登录,再进行评论。


Cedric
Cedric 2013-5-19
编辑:Cedric 2013-5-19
Here is a developed example:
>> data = [1 2 3 4 -2 -3 4 6 6 -3 -6 -5 -7 2 1 5] ;
We try to develop a solution that detects the location of transitions between, groups of positive (incl. 0) numbers and groups of negative numbers.
>> gteqZero = data >= 0 % Vector of flags "greater than or equal to 0".
gteqZero =
1 1 1 1 0 0 1 1 1 0 0 0 0 1 1 1
Places were there is a transition indicate groups boundaries; transitions are indicated by a non-zeros difference between consecutive elements:
>> diff(gteqZero)
ans =
0 0 0 -1 0 1 0 0 -1 0 0 0 1 0 0
As you can see, this could be used to spot ends of all groups but the last. We can add two non-zero entries to build a vector suitable for spotting starts of all groups, including the one that would come after the last..
>> [1, diff(gteqZero), 1]
ans =
1 0 0 0 -1 0 1 0 0 -1 0 0 0 1 0 0 1
>> groupStart = find([1, diff(gteqZero), 1])
groupStart =
1 5 7 10 14 17
and you can check that groups start at these positions. We included the starting position of a non-existent group that would start after the last group so we can compute the size of each group, as the difference between starting positions:
>> groupSize = diff(groupStart)
groupSize =
4 2 3 4 3
Now we have quite a few vectors that we can use to group your data. Which one(s) we use depends on what you want to achieve. Assuming that you want to create a cell array of groups, we can use group sizes to indicate MAT2CELL where to cut data:
>> group = mat2cell(data, 1, groupSize)
group =
[1x4 double] [1x2 double] [1x3 double] [1x4 double] [1x3 double]
>> group{1}
ans =
1 2 3 4
>> group{2}
ans =
-2 -3
etc..
Let me know if you have any question. Once you fully understand this example, let me know if you want to compute some statistics per group instead of splitting data into a cell array of groups.
EDIT: note the the whole can be aggregated into
>> groupSize = diff(find([1, diff(data>=0), 1])) ;
>> group = mat2cell(data, 1, groupSize)

Andrei Bobrov
Andrei Bobrov 2013-5-19
more variant
a = [1 2 3 4 -2 -3 4 6 6 -3 -6 -5 -7 2 1 5]
v = a > 0;
v = v(:)';
ii = [strfind([0 v],[0 1]),strfind(v,[1 0])+1];
a1 = zeros(size(a));
a1(ii) = 1;
idx = cumsum(a1);
out = accumarray(idx(:),a(:),[],@(x){x});

类别

Help CenterFile Exchange 中查找有关 Data Import from MATLAB 的更多信息

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!

Translated by