Extract information from file

jiam912 · November 9, 2016, 10:42am

Gents,

If is possible please help.

I have a big file (example attached) which contends exactly same value in column, but from column 2 to 6 these values are diff. I will like to compile for all records all columns like the example attached in .csv format (output.rar ).. The last column in the output file is the average.

0.5     0   0   0 128 128 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 
1.0     0   0   0 128 128 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 
1.5     0   0   0 128 128 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 
2.0     0   0   0 128 128 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 
2.5     0   0   0 128 128 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 
3.0     0   0   0 128 128 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 
3.5     0   0   0 128 128 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 
4.0     0   0   0 128 128 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 
4.5     0   0   0 128 128 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 
5.0     0   0   0 128 128 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 
5.5     3  69  18 128 128 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 
6.0     2  79  12  63  15 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 
6.5    -1  75  11  28  24 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 
7.0     0  73  12  32  27 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 
7.5    -1  73  11  35  21 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 
8.0     0  72   7  37  21 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 
8.5     0  72   8  42  21 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 
9.0     0  73  12  38  25 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 
9.5     0  72  10  38  24 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 
10.0    1  72   9  39  25 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 
10.5    1  71   9  42  29 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 
11.0    2  73  14  43  27 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 
11.5    2  75  13  46  27 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 
12.0    0   0   0  55  27 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0

Hope somebody can help me... Appreciate your help

RudiC · November 9, 2016, 12:40pm

Any attempts/thoughts/ideas from your side?

And, BTW, your output.csv dosn't seem related to the input at all. Please specify in detail what the requirements are.

ctsgnb · November 9, 2016, 12:42pm

Sorry but it's unclear. Please give a short and comprehensive example of what you have, what you want, and what you've tried.
By respect for those who reads you, also take the time to explain the logic of the expected result, rather than waiting for people to download/unzip and auto-magically decipher your needs.

Thanks for your understanding

jiam912 · November 9, 2016, 1:23pm

Tks for you answer..

I have modified a little the input file to show it better.

In the first column there is a values from 0.5 to 12 ( increment of .5 each row) in from of each row there is 5 columns with values

0.5     0   0   0 128 128

.. the purpose is to add the all rows for each value..

0.5   0   0   0
1.0   0   0   0
1.5   0   0   0
2.0   0   0   0
2.5   0   0   0
3.0   0   0   0
3.5   0   0   0
4.0   0   0   0
4.5   0   0   0
5.0   0   0   0
5.5   3   3   3

6.0 2 1 1
6.5 - 1 -1 -2
7.0 0 0 -1
7.5 - 1 -2 -1
8.0 0 1 2
8.5 0 1 1
9.0 0 0 1
9.5 0 1 0
10.0 1 1 1
10.5 1 1 2
11.0 2 1 2
11.5 2 2 1
12.0 0 0 0[/CODE]

the output need to be separated by , (csv file ) and calculate the average in the last column ...Hope it helps

my input

0.5     0   0   0 128 128
1.0     0   0   0 128 128
1.5     0   0   0 128 128
2.0     0   0   0 128 128
2.5     0   0   0 128 128
3.0     0   0   0 128 128
3.5     0   0   0 128 128
4.0     0   0   0 128 128
4.5     0   0   0 128 128
5.0     0   0   0 128 128
5.5     3  69  18 128 128
6.0     2  79  12  63  15
6.5    -1  75  11  28  24
7.0     0  73  12  32  27
7.5    -1  73  11  35  21
8.0     0  72   7  37  21
8.5     0  72   8  42  21
9.0     0  73  12  38  25
9.5     0  72  10  38  24
10.0    1  72   9  39  25
10.5    1  71   9  42  29
11.0    2  73  14  43  27
11.5    2  75  13  46  27
12.0    0   0   0  55  27
                         
0.5     0   0   0 128 128
1.0     0   0   0 128 128
1.5     0   0   0 128 128
2.0     0   0   0 128 128
2.5     0   0   0 128 128
3.0     0   0   0 128 128
3.5     0   0   0 128 128
4.0     0   0   0 128 128
4.5     0   0   0 128 128
5.0     0   0   0 128 128
5.5     3  69  18 128 128
6.0     1  79  12  61  18
6.5    -1  75  11  28  25
7.0     0  73  11  31  28
7.5    -2  73   9  32  19
8.0     1  70   8  37  20
8.5     1  72   9  42  23
9.0     0  73   9  38  27
9.5     1  73  10  39  25
10.0    1  72  10  42  25
10.5    1  72   9  44  32
11.0    1  74  10  44  29
11.5    2  75  11  49  30
12.0    0   0   0  59  33
                         
0.5     0   0   0 128 128
1.0     0   0   0 128 128
1.5     0   0   0 128 128
2.0     0   0   0 128 128
2.5     0   0   0 128 128
3.0     0   0   0 128 128
3.5     0   0   0 128 128
4.0     0   0   0 128 128
4.5     0   0   0 128 128
5.0     0   0   0 128 128
5.5     3  69  18 128 128
6.0     1  79  12  51  15
6.5    -2  74  12  26  22
7.0    -1  72  11  25  21
7.5    -1  71  11  28  12
8.0     2  70  10  32  13
8.5     1  73  11  39  18
9.0     1  74  11  38  22
9.5     0  73   8  40  24
10.0    1  72   8  44  28
10.5    2  71   8  47  36
11.0    2  74  11  48  35
11.5    1  76  11  52  38
12.0    0   0   0  65  47

output desired

0.5   0   0   0   
1.0   0   0   0   
1.5   0   0   0   
2.0   0   0   0   
2.5   0   0   0   
3.0   0   0   0   
3.5   0   0   0   
4.0   0   0   0   
4.5   0   0   0   
5.0   0   0   0   
5.5   3   3   3   
6.0   2   1   1   
6.5 - 1  -1  -2   
7.0   0   0  -1   
7.5 - 1  -2  -1   
8.0   0   1   2   
8.5   0   1   1   
9.0   0   0   1   
9.5   0   1   0   
10.0  1   1   1   
10.5  1   1   2   
11.0  2   1   2   
11.5  2   2   1   
12.0  0   0   0   
                  
0.5   0   0   0   
1.0   0   0   0   
1.5   0   0   0   
2.0   0   0   0   
2.5   0   0   0   
3.0   0   0   0   
3.5   0   0   0   
4.0   0   0   0   
4.5   0   0   0   
5.0   0   0   0   
5.5  69  69  69   
6.0  79  79  79   
6.5  75  75  74   
7.0  73  73  72   
7.5  73  73  71   
8.0  72  70  70   
8.5  72  72  73   
9.0  73  73  74   
9.5  72  73  73   
10.0 72  72  72   
10.5 71  72  71   
11.0 73  74  74   
11.5 75  75  76   
12.0  0   0   0   
                  
0.5   0  0   0    
1.0   0  0   0    
1.5   0  0   0    
2.0   0  0   0    
2.5   0  0   0    
3.0   0  0   0    
3.5   0  0   0    
4.0   0  0   0    
4.5   0  0   0    
5.0   0  0   0    
5.5  18 18  18    
6.0  12 12  12    
6.5  11 11  12    
7.0  12 11  11    
7.5  11  9  11    
8.0   7  8  10    
8.5   8  9  11    
9.0  12  9  11    
9.5  10 10   8    
10.0  9 10   8    
10.5  9  9   8    
11.0 14 10  11    
11.5 13 11  11    
12.0  0  0   0    
                  
0.5  128  128  128
1.0  128  128  128
1.5  128  128  128
2.0  128  128  128
2.5  128  128  128
3.0  128  128  128
3.5  128  128  128
4.0  128  128  128
4.5  128  128  128
5.0  128  128  128
5.5  128  128  128
6.0   63   61   51
6.5   28   28   26
7.0   32   31   25
7.5   35   32   28
8.0   37   37   32
8.5   42   42   39
9.0   38   38   38
9.5   38   39   40
10.0  39   42   44
10.5  42   44   47
11.0  43   44   48
11.5  46   49   52
12.0  55   59   65
                  
0.5  128  128  128
1.0  128  128  128
1.5  128  128  128
2.0  128  128  128
2.5  128  128  128
3.0  128  128  128
3.5  128  128  128
4.0  128  128  128
4.5  128  128  128
5.0  128  128  128
5.5  128  128  128
6.0   15   18   15
6.5   24   25   22
7.0   27   28   21
7.5   21   19   12
8.0   21   20   13
8.5   21   23   18
9.0   25   27   22
9.5   24   25   24
10.0  25   25   28
10.5  29   32   36
11.0  27   29   35
11.5  27   30   38
12.0  27   33   47

bakunin · November 9, 2016, 1:49pm

Sorry! I might be slow, but i still don't get it: what exactly do you mean by "average"? Average of what? You need several values to calculate an average and i.e. here:

5.5   3   3   3

I cannot see how you arrived at specifically this output from your corresponding(?) input data:

[...]
5.5     3  69  18 128 128
[...]
5.5     3  69  18 128 128
[...]
5.5     3  69  18 128 128
[...]

Could you please explain in plain english what exactly you want to do, like this: i take value "5" from line 1, value "6" from line 2 and value "7" from line 3 and it gives me an average of "6", which i write in line 1 of my output ...

I hope this helps.

bakunin

jiam912 · November 9, 2016, 2:09pm

I got this value:

5.5   3   3   3

,, because the 3 values in column 2 in front of value 5.5 are recover as row..

8.5   8  9  11

the purpose is to select each value in column and transfer to row when the value in column 1 is the same..

I say to calculate the average at the last using the values

5.5   3   3   3 3

..

I will like to get it separate by

I have added some colors to the input at output to get more clear idea..

Thanks a lot
Hope u can help me

rbatte1 · November 10, 2016, 4:30am

Dear jiam912,

I'm still very confused so I just have more questions:-

What have you tried so far?
What output/errors do you get?
What OS and version are you using?
What are your preferred tools? (C, shell, perl, awk, etc.)
What logical process have you considered? (to help steer us to follow what you are trying to achieve)

Most importantly, What have you tried so far?

There are probably many ways to achieve most tasks, so giving us an idea of your style and thoughts will help us guide you to an answer most suitable to you so you can adjust it to suit your needs in future.

We're all here to learn and getting the relevant information will help us all.

Kind regards,
Robin

jiam912 · November 10, 2016, 6:21am

Robin,

I use to work with awk.

The code I try is the below.

awk '/0.5 /{if(s){print s;s=$NF+0}else{s=$NF+0}}
    /0.5/{s=s""$NF+0})
	END{print s}' input.txt

My OS is debian.

Tks

jiam912 · November 11, 2016, 12:01pm

Please somebody can help me to solve this issue.

RudiC · November 11, 2016, 12:54pm

I really, Really, REALLY would like to IF I only understood the problem.

Aia · November 11, 2016, 10:31pm

Save as jiam912.pl
Run as perl jiam912.pl input

#!/usr/bin/perl
use strict;
use warnings;

my @id;
my %data;
my $set = 0;

while(<>) {
    unless (/^\d/) { ++$set; next }
    my ($id, @fields) = (split)[0..5];
    push @id, $id unless $set;
    for my $f (0..$#{fields}) {
        push @{$data{$id}[$f]}, $fields[$f];
    }
}

for my $t (0..4) {
    for my $i (@id) {
        my $p  = shift @{$data{$i}};
        print join (",", $i, @{$p})."\n";
    }
    print "\n";
}

Output:

0.5,0,0,0
1.0,0,0,0
1.5,0,0,0
2.0,0,0,0
2.5,0,0,0
3.0,0,0,0
3.5,0,0,0
4.0,0,0,0
4.5,0,0,0
5.0,0,0,0
5.5,3,3,3
6.0,2,1,1
6.5,-1,-1,-2
7.0,0,0,-1
7.5,-1,-2,-1
8.0,0,1,2
8.5,0,1,1
9.0,0,0,1
9.5,0,1,0
10.0,1,1,1
10.5,1,1,2
11.0,2,1,2
11.5,2,2,1
12.0,0,0,0

0.5,0,0,0
1.0,0,0,0
1.5,0,0,0
2.0,0,0,0
2.5,0,0,0
3.0,0,0,0
3.5,0,0,0
4.0,0,0,0
4.5,0,0,0
5.0,0,0,0
5.5,69,69,69
6.0,79,79,79
6.5,75,75,74
7.0,73,73,72
7.5,73,73,71
8.0,72,70,70
8.5,72,72,73
9.0,73,73,74
9.5,72,73,73
10.0,72,72,72
10.5,71,72,71
11.0,73,74,74
11.5,75,75,76
12.0,0,0,0

0.5,0,0,0
1.0,0,0,0
1.5,0,0,0
2.0,0,0,0
2.5,0,0,0
3.0,0,0,0
3.5,0,0,0
4.0,0,0,0
4.5,0,0,0
5.0,0,0,0
5.5,18,18,18
6.0,12,12,12
6.5,11,11,12
7.0,12,11,11
7.5,11,9,11
8.0,7,8,10
8.5,8,9,11
9.0,12,9,11
9.5,10,10,8
10.0,9,10,8
10.5,9,9,8
11.0,14,10,11
11.5,13,11,11
12.0,0,0,0

0.5,128,128,128
1.0,128,128,128
1.5,128,128,128
2.0,128,128,128
2.5,128,128,128
3.0,128,128,128
3.5,128,128,128
4.0,128,128,128
4.5,128,128,128
5.0,128,128,128
5.5,128,128,128
6.0,63,61,51
6.5,28,28,26
7.0,32,31,25
7.5,35,32,28
8.0,37,37,32
8.5,42,42,39
9.0,38,38,38
9.5,38,39,40
10.0,39,42,44
10.5,42,44,47
11.0,43,44,48
11.5,46,49,52
12.0,55,59,65

0.5,128,128,128
1.0,128,128,128
1.5,128,128,128
2.0,128,128,128
2.5,128,128,128
3.0,128,128,128
3.5,128,128,128
4.0,128,128,128
4.5,128,128,128
5.0,128,128,128
5.5,128,128,128
6.0,15,18,15
6.5,24,25,22
7.0,27,28,21
7.5,21,19,12
8.0,21,20,13
8.5,21,23,18
9.0,25,27,22
9.5,24,25,24
10.0,25,25,28
10.5,29,32,36
11.0,27,29,35
11.5,27,30,38
12.0,27,33,47

Use this one if you want to have a round down average.

#!/usr/bin/perl
use strict;
use warnings;

my @id;
my %data;
my $set = 0;

while(<>) {
    unless (/^\d/) { ++$set; next }
    my ($id, @fields) = (split)[0..5];
    push @id, $id unless $set;
    for my $f (0..$#{fields}) {
        push @{$data{$id}[$f]}, $fields[$f];
    }
}

for my $t (0..4) {
    for my $i (@id) {
        my $p  = shift @{$data{$i}};
        my $avg = avg(@{$p});
        printf "%s,%d\n", join (",", $i, @{$p}), $avg;
    }
    print "\n";
}

sub avg {
    my $total = 0;
    foreach (@_) {
        $total += $_;
    }
    return  $total/@_;
}

jiam912 · November 12, 2016, 1:06am

Thanks, Thanks a lot Aia

It works fine.

Again thanks a lot.

---------- Post updated at 01:05 AM ---------- Previous update was at 01:03 AM ----------

It is there the option to get same output using AWK?

---------- Post updated at 01:06 AM ---------- Previous update was at 01:05 AM ----------

Dear RudiC,

Aia got the output using perl.. Can you please do the same with Awk..

Thanks