Change value based on the values of another column

I have a table with 2 columns and 6000 rows each. Sample values in the 1st column is like:

-0.00554

-0.00503

-0.00406

-0.00406

-0.00316

-0.00274

-0.00274

0

0

0

0

0

0

0.00233

0.00452

0.00552

0.00715

0.00831

0

0

-9.00000000000000e-05

-9.00000000000000e-05

-9.00000000000000e-05

-9.00000000000000e-05

-9.00000000000000e-05

-9.00000000000000e-05

-9.00000000000000e-05

0

0

0

0.00552

0.00715

0.00831

0.00715

0.00831

0

0

0

0

0

-0.00406

-0.00316

The corresponding values in the 2nd column will be starting from 1,2,3, etc. until the positive value changes again to negative value in the 1st column. To be more specific, 1st column will be having values starting with -ve sign followed by 0s and +ve values. So, the -ve value will be changing to +ve and again back to -ve. Once the -ve value show up again, the count on the 2nd column should start from 1 again. Please help me wih this.

Answer 1

[Star Strider]

Another approach —

v = [-0.00554
-0.00503
-0.00406
-0.00406
-0.00316
-0.00274
-0.00274
0
0
0
0
0
0
0.00233
0.00452
0.00552
0.00715
0.00831
0
0
-9.00000000000000e-05
-9.00000000000000e-05
-9.00000000000000e-05
-9.00000000000000e-05
-9.00000000000000e-05
-9.00000000000000e-05
-9.00000000000000e-05
0
0
0
0.00552
0.00715
0.00831
0.00715
0.00831
0
0
0
0
0
-0.00406
-0.00316];

sv = sign(v);
sv(sv>=0) = 1;                                                  % Adjust 'sign' Vector
pn = [0 strfind(sv.', [1 -1]) numel(v)];                        % +vn To -ve Transition Indices
for k = 1:numel(pn)-1
    idx = [pn(k) pn(k+1)-1]+1;                                  % Index Range
    Col2(idx(1):idx(2)) = (idx(1) : idx(2)) - (idx(1)-1);       % Create Column #2 (As Row Vector)
end

Result = [v, Col2(:)]                                           % Full Matrix

Result = 42×2

-0.0055 1.0000 -0.0050 2.0000 -0.0041 3.0000 -0.0041 4.0000 -0.0032 5.0000 -0.0027 6.0000 -0.0027 7.0000 0 8.0000 0 9.0000 0 10.0000

ResultMtx = [Result(1:10,:) Result(11:20,:) Result(21:30,:) Result(31:40,:)]    % Display (Remove Later)

ResultMtx = 10×8

-0.0055 1.0000 0 11.0000 -0.0001 1.0000 0.0055 11.0000 -0.0050 2.0000 0 12.0000 -0.0001 2.0000 0.0072 12.0000 -0.0041 3.0000 0 13.0000 -0.0001 3.0000 0.0083 13.0000 -0.0041 4.0000 0.0023 14.0000 -0.0001 4.0000 0.0072 14.0000 -0.0032 5.0000 0.0045 15.0000 -0.0001 5.0000 0.0083 15.0000 -0.0027 6.0000 0.0055 16.0000 -0.0001 6.0000 0 16.0000 -0.0027 7.0000 0.0072 17.0000 -0.0001 7.0000 0 17.0000 0 8.0000 0.0083 18.0000 0 8.0000 0 18.0000 0 9.0000 0 19.0000 0 9.0000 0 19.0000 0 10.0000 0 20.0000 0 10.0000 0 20.0000

ResultEnd = Result(41:end,:)                                                    % Display (Remove Later)

ResultEnd = 2×2

-0.0041 1.0000 -0.0032 2.0000

.

[Krishna]

It worked!!

[Star Strider]

Thank you!

[Krishna]

@Star Strider i need one more help. So, the -ve value will be changing to +ve and again back to -ve. Once the -ve value show up again, the value on the 2nd column should start from 0 and need to find the difference between corresponding row values of 1st column. For example, by 20th row of 1st column, value change from 0/+ve value to negative. Then the corresponding row in 2nd column will change to 0. Next value of 2nd column will be difference between 21st row and 20th row of 1st column. Please let me know if you have any queries.

[Star Strider]

I do not understand.

Taking a wild guess —

v = [-0.00554
-0.00503
-0.00406
-0.00406
-0.00316
-0.00274
-0.00274
0
0
0
0
0
0
0.00233
0.00452
0.00552
0.00715
0.00831
0
0
-9.00000000000000e-05
-9.00000000000000e-05
-9.00000000000000e-05
-9.00000000000000e-05
-9.00000000000000e-05
-9.00000000000000e-05
-9.00000000000000e-05
0
0
0
0.00552
0.00715
0.00831
0.00715
0.00831
0
0
0
0
0
-0.00406
-0.00316];

sv = sign(v);
sv(sv>=0) = 1;                                                      % Adjust 'sign' Vector
pn = [0 strfind(sv.', [1 -1]) numel(v)];                            % +vn To -ve Transition Indices
for k = 1:numel(pn)-1
    idx = [pn(k) pn(k+1)-1]+1;                                      % Index Range
%     Col2(idx(1):idx(2)) = (idx(1) : idx(2)) - (idx(1)-1);       % Create Column #2 (As Row Vector)
    Col2(idx(1):idx(2)) = diff([v(idx(1)); v(idx(1) : idx(2))]);    % Create Column #2 (As Row Vector)
end

Result = [v, Col2(:)]                                           % Full Matrix

Result = 42×2

-0.0055 0 -0.0050 0.0005 -0.0041 0.0010 -0.0041 0 -0.0032 0.0009 -0.0027 0.0004 -0.0027 0 0 0.0027 0 0 0 0

ResultMtx = [Result(1:10,:) Result(11:20,:) Result(21:30,:) Result(31:40,:)]    % Display (Remove Later)

ResultMtx = 10×8

-0.0055 0 0 0 -0.0001 0 0.0055 0.0055 -0.0050 0.0005 0 0 -0.0001 0 0.0072 0.0016 -0.0041 0.0010 0 0 -0.0001 0 0.0083 0.0012 -0.0041 0 0.0023 0.0023 -0.0001 0 0.0072 -0.0012 -0.0032 0.0009 0.0045 0.0022 -0.0001 0 0.0083 0.0012 -0.0027 0.0004 0.0055 0.0010 -0.0001 0 0 -0.0083 -0.0027 0 0.0072 0.0016 -0.0001 0 0 0 0 0.0027 0.0083 0.0012 0 0.0001 0 0 0 0 0 -0.0083 0 0 0 0 0 0 0 0 0 0 0 0

ResultEnd = Result(41:end,:)                                                    % Display (Remove Later)

ResultEnd = 2×2

-0.0041 0 -0.0032 0.0009

.

[Krishna]

The code worked. But, here 0 is generating everytime when a negative value is encountered even after few 0's. In the given column, after a set of negative values, 0's and +ve values are present. But, once the negative value is shown anytime after a set of +ve values, only then the 0 should be created in the corresponding row of new column. Also, what is the method to find the sum of the rows insted of difference? The process remains same. Thank you for your help.

[Star Strider]

I still do not understand.

If you want all the second column values to be zero for all -ve values of ‘v’ (or column 1), add this after the initial ‘Col2’ assignment if there are to be more calculations on ‘Col2’ within the loop:

Col2(v(idx(1):idx(2)) < 0) = 0;

or better, after the loop to change all of them at once:

Col2(v < 0) = 0;

To create a cumulative sum inmstead of calculating the differences, use the cumsum function:

Col2(idx(1):idx(2)) = cumsum(v(idx(1) : idx(2))); % Create Column #2 (As Row Vector)

.

[Krishna]

@Star Strider Sorry, the question is bit complicated. I don't want all the second column values to be zero for all -ve values of ‘v’. Insted, I just need the 1st negative value, after the +ve/ 0 values to be zero. The remaining values will be the difference between rows itself.

sv = sign(v);

sv(sv>=0) = 1; % Adjust 'sign' Vector

pn = [0 strfind(sv.', [1 -1]) numel(v)]; % +vn To -ve Transition Indices

for k = 1:numel(pn)-1

idx = [pn(k) pn(k+1)-1]+1; % Index Range

Col2(idx(1):idx(2)) = (idx(1) : idx(2)) - (idx(1)-1); % Create Column #2 (As Row Vector)

end

This code actually worked and the result is exactly what I expect. When I executed this code, 0 is generating everytime when a negative value is encountered even after few 0's. I just need the 1st negative value, after the +ve/ 0 values to be zero. The remaining values will be the difference between rows itself.

[Star Strider]

I’mn getting infinitesimally closer to understanding it, however my comment to your other post (copied in part here) describes some of my confusion:

I’ve been trying to understand this, however much needs to be clarified.

Input Output

-0.0055 0

-0.0050 -0.0055

-0.0041 -0.0091

-0.0041 -0.0082

-0.0032 -0.0073 % <— This seems to me to be anomalous - Should it be -0.0114? If not, why not?

-0.0027 -0.0059 % And similarly here ...

-0.0027 -0.0054 % And similarly here ...

0 -0.0027 % And similarly here.

0 0

0 0

0 0

0 0

0 0

0.0023 0.0023

0.0045 0.0068

0.0055 0.0100

-0.0025 0

-0.0005 -0.0030

-0.0021 -0.0026

-0.0041 -0.0062

Unfortunately, I don’t understand the description of the problem, either, although the earlier problem was straightforward.

.

[Krishna]

@Star Strider Consider this as my table. The previous one was wrong. Sorry. The relationship between input2 and output is, when input2 value changes to a -ve from +ve value within the subset, output will again count from 0. The difference is found from input1 alone.

Input1 input2 Output

1 -0.0103 0

3 -0.0105 2

5 -0.0091 4

7 -0.0082 6

10 -0.0073 9

11 -0.0059 10

15 -0.0054 14

20 -0.0027 19

21 0 20

22 0 21

23 0 22

24 0 23

25 -0.0059 24

26 -0.0054 25

27 -0.002723 26

35 0 34

36 0.0023 35

38 0.0068 37

40 0.0100 39

41 0 40

42 -0.0030 0

43 -0.0026 1

44 -0.0062 2

......

[Star Strider]

The values for ‘Input1’ are different, and I do not understand how either it or ‘Output’ are incremented.

I just do not see any sort of pattern here that lends itself to being coded.

Answer 2

[dpb]

If there's always at least one zero before the new -ive value excepting the initial element, then

ix=find(diff(sign([0;x]))==-1);

will locate the beginng line of each section.

Or, actually, on reflection,

ix=find(diff(sign([0;x]))<0);

will find a transition from either 0 to -ive or +ive to -ive

[Krishna]

There is no 0 at some cases. new -ve value is also present immediately after a +ve value.

[Krishna]

I'm running this code inside a loop as depicted below:

for k = 1:rows

ix = find(diff(table.first_column([0;k]))<0);

if(ix < 0)

table.second_column(k) = 1;

end

end

But, while running this code, I'm getting an error like:

"Array indices must be positive integers or logical values."

Answer 3

[Andrei Bobrov]

v = [-0.00554
-0.00503
-0.00406
-0.00406
-0.00316
-0.00274
-0.00274
0
0
0
0
0
0
0.00233
0.00452
0.00552
0.00715
0.00831
0
0
-9.00000000000000e-05
-9.00000000000000e-05
-9.00000000000000e-05
-9.00000000000000e-05
-9.00000000000000e-05
-9.00000000000000e-05
-9.00000000000000e-05
0
0
0
0.00552
0.00715
0.00831
0.00715
0.00831
0
0
0
0
0
-0.00406
-0.00316];

s = sign(v); 
p = diff([0;s]) == -1 & s == -1;
i = accumarray(cumsum(p),1);
x = ones(size(p));
x(p) = x(p) - [ 0;i(1:end-1)];
out = cumsum(x);