count a block of numbers, and calculate block size

[ekalavya]

Hi everyone,

I have approximately 100 rows (samples) and 4000 columns (Trait1, 2, ... n) that look like below.

Input

Size	0.1	0.3	0.5	0.6	0.9	1	1.1	2.2
Sample	Trait1	Trait2	Trait3	Trait4	Trait5	Trait6	Trait7	Trait8
A	0	0	0	1	1	0	0	0
B	1	1	1	0	1	1	1	1
C	0	0	1	1	1	0	0	0
D	0	0	0	0	0	0	1	1

<tbody>
</tbody>

For each sample, I want to scan the block of numbers, and report the size of the block (first row). For example, Sample A has first block of 0s of size 0.5 (0.5 - 0.0); second block of 1s of size 0.3 (0.9 - 0.6); and third block of 0s of size 1.2 (2.2 - 1.0).

Desired output

Sample	Block1	Size1	Block2	Size2	Block3	Size3
A	0	0.5	1	0.3	0	1.2
B	1	0.5	0	0	1	1.3
C	0	0.3	1	0.4	0	1.2
D	0	1	1	1.1

<tbody>
</tbody>

Please note that for sample B, which as only 1 zero, the size is also zero as it is not a block. Therefore, if there's a value by itself (count of 1), then the size should be zero.

I hope someone here can help me with this as my data size is pretty big and it is killing me to scan it by color-coding the spreadsheet.

Thank you for your generous help, as always.

 

[yky]

For example, Sample A has first block of 0s of size 0.5 (0.5 - 0.0);

Why is it not 0.4 (0.5 - 0.1)?

 

[ekalavya]

Why is it not 0.4 (0.5 - 0.1)?

Counting for this data begins with 0 so the first 'size' is value - 0.

Actually, in hindsight, I should have listed 0 as the first value in the 'Size' row to avoid confusion.