Can we use AWK and gsub() to process data with multiple colons ":" ? How?
Question: Here is an example of the data:

    
    
    Col_01:14 .... Col_20:25    Col_21:23432    Col_22:639142
    Col_01:8  .... Col_20:25    Col_22:25134    Col_23:243344
    Col_01:17 .... Col_21:75    Col_23:79876    Col_25:634534    Col_22:5    Col_24:73453
    Col_01:19 .... Col_20:25    Col_21:32425    Col_23:989423
    Col_01:12 .... Col_20:25    Col_21:23424    Col_22:342421    Col_23:7    Col_24:13424    Col_25:67
    Col_01:3  .... Col_20:95    Col_21:32121    Col_25:111231
    

As you can see, some of these columns are not in the correct order...

Now, I think the correct way to import this file into a dataframe is to
preprocess the data such that you can output a dataframe with `NaN` values,
e.g.

    
    
    Col_01 .... Col_20    Col_21    Col22    Col23    Col24    Col25
    8      .... 25        NaN       25134    243344   NaN      NaN
    17     .... NaN       75        2        79876    73453    634534
    19     .... 25        32425     NaN      989423   NaN      NaN
    12     .... 25        23424     342421   7        13424    67
    3      .... 95        32121     NaN      NaN      NaN      111231
    

The solution was shown by @JamesBrown here : [How to preprocess and load a
"big data" tsv file into a python
dataframe?](http://stackoverflow.com/questions/39398986/how-to-preprocess-and-
load-a-big-data-tsv-file-into-a-python-dataframe/)

Using said awk script:

    
    
    BEGIN {
        PROCINFO["sorted_in"]="@ind_str_asc" # traversal order for for(i in a)                  
    }
    NR==1 {       # the header cols is in the beginning of data file
                  # FORGET THIS: header cols from another file replace NR==1 with NR==FNR and see * below
        split($0,a," ")                  # mkheader a[1]=first_col ...
        for(i in a) {                    # replace with a[first_col]="" ...
            a[a[i]]
            printf "%6s%s", a[i], OFS    # output the header
            delete a[i]                  # remove a[1], a[2], ...
        }
        # next                           # FORGET THIS * next here if cols from another file UNTESTED
    }
    {
        gsub(/: /,"=")                   # replace key-value separator ": " with "="
        split($0,b,FS)                   # split record from ","
        for(i in b) {
            split(b[i],c,"=")            # split key=value to c[1]=key, c[2]=value
            b[c[1]]=c[2]                 # b[key]=value
        }
        for(i in a)                      # go thru headers in a[] and printf from b[]
            printf "%6s%s", (i in b?b[i]:"NaN"), OFS; print ""
    }
    

And put the headers into a text file `cols.txt`

    
    
    Col_01 Col_20 Col_21 Col_22 Col_23 Col_25
    

My question now: how do we use awk if we have data that is not `column: value`
but `column: value1: value2: value3`?

We would want the database entry to be `value1: value2: value3`

Here's the new data:

    
    
    Col_01:14:a:47 .... Col_20:25:i:z    Col_21:23432:6:b    Col_22:639142:4:x
    Col_01:8:z .... Col_20:25:i:4    Col_22:25134:u:0    Col_23:243344:5:6
    Col_01:17:7:z .... Col_21:75:u:q    Col_23:79876:u:0    Col_25:634534:8:1   
    

We still provide the columns beforehand with `cols.txt`

How can we create a similar database structure? Is it possible to use `gsub()`
to limit to the first value before `:` which is the same as the header?

EDIT: This doesn't _have to_ be awk based. Any language will do naturally


Answer: Here is another alternative...

    
    
    $ awk -v OFS='\t' '{for(i=1;i<NF;i+=2)                  # iterate over name: value pairs
                         {c=$i;                             # copy name in c to modify
                          sub(/:/,"",c);                    # remove colon
                          a[NR,c]=$(i+1);                   # collect data by row number, name
                          cols[c]}}                         # save name
                    END{n=asorti(cols,icols);               # sort names
                        for(j=1;j<=n;j++) printf "%s", icols[j] OFS;   # print header 
                        print ""; 
                        for(i=1;i<=NR;i++)                  # print data
                          {for(j=1;j<=n;j++) 
                             {v=a[i,icols[j]];             
                              printf "%s", (v?v:"NaN") OFS} # replace missing data with NaN
                           print ""}}' file | column -t     # pipe to column for pretty print
    
    Col_01   Col_20  Col_21     Col_22      Col_23      Col_25
    14:a:47  25:i:z  23432:6:b  639142:4:x  NaN         NaN
    8:z      25:i:4  NaN        25134:u:0   243344:5:6  NaN
    17:7:z   NaN     75:u:q     NaN         79876:u:0   634534:8:1
    

