Converting values in columns into rows

Question

I have a text file which has data in the following format. I want to make one row for each value in the first column.

0-09935152                          RC=3       CC=2   L=10-11
                   M=1
                   BNT=4
0-09935153                   F=31                     L=11
                   M=1
0-09935154                   F=31                     L=11
                   M=1
0-09935155                   F=31                     L=11
                   M=1
0-09935156                   F=31                     L=11
                   M=1
0-09935157                   F=31                     L=11
                   M=1
0-09935158                   F=31                     L=11
                   M=1
0-09935159                   F=31                     L=11
                   M=1
0-0993516                    F=31                     L=11
                   M=1
0-0993517                    F=31                     L=11
                   M=1
0-0993518                    F=31                     L=11
                   M=1
0-0993519                    F=0               CC=2
                   M=1

I want to pull all corresponding values into one row. The expected output will be like

Code|M|F|CC|L|BNT|RC
0-09935152|1||2|10-11|4|3
0-09935153|1|31||11|||

I am trying to use awk to parse the text file. I am only able to segregate the columns but unable to proceed any further.

Any help is appreciated.

Are all related data allready on the same row? That example looks a bit messy. — James Brown
– James Brown, Commented Jul 7, 2016 at 6:29
No, that is the challenge. For every code that i have for example the first line 0-09935152 the corresponding values are in the next column one below each other. I am trying to make one row for each. — ThePatBan
– ThePatBan, Commented Jul 7, 2016 at 6:31
Not necessary, it is a number followed by "-" and then another set of numbers. So there is a possibility of a number string like 99-12345 also — ThePatBan
– ThePatBan, Commented Jul 7, 2016 at 6:39

Chet · Accepted Answer · 2016-07-07 07:50:15Z

1

awk -f script.awk file

script.awk

BEGIN{RS="[ \n]";OFS="|";print "Code","M","F","CC","L","BNT","RC"} #print headers
length > 1 {size=split($0,t,"=")} #split values by "="
size==2{a[t[1]]=t[2]} #non-code values
size==1 && flag {print code,a["M"],a["F"],a["CC"],a["L"],a["BNT"],a["RC"];delete a;code=$0} #print values for each code switch
size==1 && !flag{flag++;code=$0} #skip first
{delete t;size=0} #clear data 
END{print code,a["M"],a["F"],a["CC"],a["L"],a["BNT"],a["RC"]} # print last value

Output

Code|M|F|CC|L|BNT|RC
0-09935152|1||2|10-11|4|3
0-09935153|1|31||11||
0-09935154|1|31||11||
0-09935155|1|31||11||
0-09935156|1|31||11||
0-09935157|1|31||11||
0-09935158|1|31||11||
0-09935159|1|31||11||
0-0993516|1|31||11||
0-0993517|1|31||11||
0-0993518|1|31||11||
0-0993519|1|0|2|||

answered Jul 7, 2016 at 7:50

Chet

1,2251 gold badge12 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

James Brown · Accepted Answer · 2016-07-07 08:08:47Z

0

BEGIN {
    RS="( +|\n)" # set the record separator to put every piece of data on a separate row for split
    OFS="|"      # # below: initialize the header
    arr[0]="Code"; arr["M"]="M"; arr["F"]="F"; arr["CC"]="CC"; arr["L"]="L"; arr["BNT"]="BNT"; arr["RC"]="RC"
}
/^[0-9]+-/ {     # print arr when new code starts
    print arr[0],arr["M"],arr["F"],arr["CC"],arr["L"],arr["BNT"],arr["RC"];
    delete arr;  # empty previous values from arr
    arr[0]=$0
}
{
    split($0,brr,"[=]"); # split from "=" to another array
    arr[brr[1]]=brr[2]   # first part is the index, latter is the value
}
END { # print the last line, too bad you can't "/^[0-9]+-/ || END {..."
print arr[0],arr["M"],arr["F"],arr["CC"],arr["L"],arr["BNT"],arr["RC"];
}

$ awk -f test.awk test.in
Code|M|F|CC|L|BNT|RC
0-09935152|1||2|10-11|4|3
0-09935153|1|31||11||
0-09935154|1|31||11||
[...]

edited Jul 7, 2016 at 8:08

answered Jul 7, 2016 at 7:28

James Brown

37.7k8 gold badges52 silver badges64 bronze badges

4 Comments

Chet Over a year ago

check OP's Commnets. The code can start with Non-Zero value

James Brown Over a year ago

Dude, it was first draft on my first day of summer vacation, please give me a minute for my morning coffee. :D

Chet Over a year ago

could use OFS for "|" delimiter

James Brown Over a year ago

Yeah, would probably look neater. Let's see.

Collectives™ on Stack Overflow

Converting values in columns into rows

2 Answers 2

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related