How to do following formatting using sed or awk or bash script on linux

Question

Suppose, I have two files:
file1 having below contents.

message_aaa.wav
  test 1 of 1
    test Label        : aaa
    test Sentence       : test aaa
    test String: test message
    Interpretation Score : 567
message_bbb.wav
  test 1 of 1
    test Label        : bbb
    test Sentence       : test bbb
    test String: test bbb
    Interpretation Score : 972
Message_ccc.wav
  test 1 of 1
    test Label        : ccc
    test Sentence       : ccc
    test String: test ccc
    Interpretation Score : 921

file2 having below contents:

message_aaa.wav
  test 1 of 1
    test Label        : aaa
    test Sentence       : test aaa
    test String: test message
    Interpretation Score : 55
message_bbb.wav
  test 1 of 1
    test Label        : bbb
    test Sentence       : test bbb
    test String: test bbb
    Interpretation Score : 34
message_ccc.wav
  test 1 of 1
    test Label        : ccc
    test Sentence       : ccc
    test String: test ccc
    Interpretation Score : 12

I want to create one CSV/xls file, which will have 3 columns as:
1st column will be name of .wav files from file1
2nd column will be "Interpretation score" from file1
3rd column will be "Interpretation score" from file2
like below.

message_aaa.wav,567,55
message_bbb.wav,972,34
message_ccc.wav,921,12

@choroba I tried to read both files line by line, but struggling to join lines containing '.wav' and scores from two files. — AVJ
– AVJ, Commented Mar 14, 2016 at 13:58
Is there a particular reason why you want to do this with a bash script? Something like this would be really easy with Python for example. — TravisJ
– TravisJ, Commented Mar 14, 2016 at 14:40

jijinp · Accepted Answer · 2016-03-14 14:54:39Z

Something like this with awk:

awk '/^[mM]essage_.*.wav/{
    n=NR+5;i=tolower($1)
}
n==NR{
    a[i]=a[i]","$4
}
END{
    for ( i in a){
        print i""a[i]
    }
}' file1 file2

If the line starts with [mM]essage_.*.wav the variable n is set to NR+5 (The line numbers where Interpretation Score value is) and the wav file name is saved in variable a i.

When n==NR and array with index as i is created and Interpretation Score($4) is appended to this array.

END print all the array index and its value as desired.

Here I assumed the Interpretation Score line always as the 5th line in each test.

techraf · Accepted Answer · 2016-03-14 15:16:02Z

1

grep -e 'wav\|Inter' file1.txt | cut -d: -f2 | awk '{print $1}'| awk 'NR%2{printf $0",";next;}1' > file1new.txt
grep -e 'wav\|Inter' file2.txt | cut -d: -f2 | awk '{print $1}'| awk 'NR%2{printf $0",";next;}1' > file2new.txt
join -t, file1new.txt file2new.txt

edited Mar 14, 2016 at 15:16

techraf

6,12111 gold badges36 silver badges51 bronze badges

answered Mar 14, 2016 at 15:03

keithbr

361 bronze badge

Add a comment |

cas · Accepted Answer · 2016-03-15 11:14:53Z

Here's a perl script that does the job. It reads through stdin, and extracts the filenames to use as the key to a hashed array called %wavfiles. The particular data structure used is a hash-of-arrays, a hashed array where each element is a list (i.e. an array) containing one or more values.

When it sees a line matching the regex /Interpretation Score/ AND if $filename is non-empty, it uses split() to extract the interpretation score and push it onto the array contained in $wavfiles{$filename}. It then clears $filename to prevent spurious data being added for that key.

When there is no more input to be read, it prints out the contents of the %wavfiles hash-of-arrays in CSV format.

This script will work with any number of input files, of any size. It treats all filenames specified on the command line and/or all data piped in from stdin as one input stream.

The order of the input is significant - it directly affects the order of the Interpretation Scores in the output (e.g. if it sees 567 before 55, that is the order it will display those values. and vice-versa).

The script is not limited to just two Interpretation Scores for each filename - it will print as many it sees for each filename, separated by commas in the order that it saw them.

#! /usr/bin/perl

use strict;

my %wavfiles = ();
my $filename = '';

while(<>) {
    chomp;    # strip line-feed from end of line

    $filename = $_ if (m/\.wav$/);

    if ((m/Interpretation Score/) && (! -z $filename)) {
        my (undef,$score) = split /: /;     # split input line on ': '
        push @{ $wavfiles{$filename} }, $score;
        $filename = '';
    };
}   

foreach $filename (keys %wavfiles) {
    print "$filename,", join(",",@{ $wavfiles{$filename} }),"\n";
}

Output using your sample input is:

$ ./interpscore.pl file1 file2
message_bbb.wav,972,34
Message_ccc.wav,921
message_aaa.wav,567,55
message_ccc.wav,12

Note that hash keys, like filenames, are case-sensitive, which is why there is an entry for both Message_ccc.wav (with a capital-M) and message_ccc.wav (with a lowercase-m) - that's what was in your input files. You need to edit your input files so that the filenames are consistent - after you do that, the output will be:

$ ./interpscore.pl file1 file2
message_aaa.wav,567,55
message_ccc.wav,921,12
message_bbb.wav,972,34

thanks @cas . One more thing to confirm, Above script will work even if wav file name contains any other string instead of message_ ..e.g like lisa_bbb.wav — AVJ
– AVJ, Commented Mar 15, 2016 at 10:43
yep, it searches for \.wav$ (a literal period \. followed by wav at the end of the line $). see the second non-blank line after the while(<>) {. you can edit that line so that it matches whatever you like. e.g. If you wanted it to match only your message_ filenames, you'd change the regexp to /^message_...\.wav$/ — cas
– cas, Commented Mar 15, 2016 at 10:49
BTW, one thing i should have mentioned in my answer - the field output order depends on the order of the input files. So if you run it as ./interpscore file2 file1 rather than ./interpscore.pl file1 file2 then, e.g., the message_aaa.wav line will look like this: message_aaa.wav,55,567 - in other words, the script will add the scores for each file in the order it sees them. — cas
– cas, Commented Mar 15, 2016 at 10:52
Also, it's not limited to just 2 scores for each filename - it will handle as many as it sees, whether that's 1 or 2 or 10000 or whatever, and print them all out separated by commas. — cas
– cas, Commented Mar 15, 2016 at 10:57

Stack Exchange Network

How to do following formatting using sed or awk or bash script on linux

3 Answers 3

You must log in to answer this question.

Hot Network Questions

How to do following formatting using sed or awk or bash script on linux

3 Answers 3

You must log in to answer this question.

Related

Hot Network Questions