Split into multiple files grouped by column value using AWK
Question: My data frame is something similar to this in structure:

    
    
        A   C
    1   a   1
    2   a   2
    3   a   3
    4   a   4
    5   b   5
    6   b   6
    7   b   7
    8   b   8
    9   c   9
    

it has 16 million rows, I'm facing memory issues while processing in Python. I
tried to split this file using `split` command, but I don't want to spread
values in `A` column into different files.  
I don't want something like this:

File one:

    
    
     A   C
    1   a   1
    2   a   2
    3   a   3
    4   a   4
    5   b   5
    6   b   6
    

File two:

    
    
    7   b   7
    8   b   8
    9   c   9
    

Something like this would work:

File one:

    
    
     A   C
    1   a   1
    2   a   2
    3   a   3
    4   a   4
    5   b   5
    6   b   6
    7   b   7
    8   b   8
    

File two:

    
    
    9   c   9
    

Let me know, if I'm not clear. I wrote a script to do this job in python-
pandas, but it's taking long time. Any quick solution using awk?

EDIT: My python script to split this file, this file is taking too long.

    
    
    import pandas as pd
    import sys
    import os
    
    tp = pd.read_table(sys.argv[1], iterator=True, chunksize=int(sys.argv[2]))
    gff3File = pd.concat(tp, ignore_index=True)
    i = 0
    colNames = ['query_id', 'subject_id', 'percent_idendity', 'align_len', 'mismatch', 'gap_opens', 'q_start', 'q_end', 's_start', 's_end', 'evalue', 'bit_score']
    result = pd.DataFrame(columns= colNames)
    os.mkdir('splitted')
    os.chdir('splitted')
    print(len(gff3File))
    
    while True:
        first_gene = gff3File.loc[gff3File["query_id"] == gff3File.query_id.iloc[0]]
        gff3File = gff3File[gff3File["query_id"] != gff3File.query_id.iloc[0]]
        result = result.append(first_gene)
        #print(len(first_gene))
        if len(result) >= int(sys.argv[2]) or len(gff3File) == 0:
            result.to_csv(''.join(['split', "_", str(i), '.csv']), sep = ',')
            print("wrote to:",''.join(['split', "_", str(i), '.csv']))
            result = pd.DataFrame(columns= colNames)
            i+=1
        if len(gff3File) == 0: break
    

Head of my data:

    
    
    query_id    subject_id  percent_idendity    align_len   mismatch    gap_opens   q_start q_end   s_start s_end   evalue  bit_score
    LOC_Os01g01010.1    Chr1    100.00  811 0   0   370 1180    7134    7944    0.0 1498
    LOC_Os01g01010.1    Chr1    100.00  410 0   0   1592    2001    9208    9617    0.0  758
    LOC_Os01g01010.1    Chr1    100.00  202 0   0   1392    1593    8407    8608    2e-101   374
    LOC_Os01g01010.1    Chr1    100.00  169 0   0   1   169 3449    3617    4e-83    313
    LOC_Os01g01010.1    Chr1    82.25   338 30  19  1669    1996    43230640    43230323    1e-68    265
    

Here, I don't want to spread `query_id` to different files while splitting.


Answer: **UPDATE** :

Since the OP wanted to group several keys into a single file, I wrote a less
simple AWK script to do the processing!

    
    
    BEGIN {
        query_count = 0
        file_index = 0
    }
    {
        if ($2 in arr)
            print >> arr[$2]".split"
        else {
            arr[$2] = file_index
            print > arr[$2]".split"
        }   
        if ( query_count % 5000 == 0 )
            file_index++
        query_count++
    }
    END {
        for ( key in arr )
            print key ": " arr[key]
    }
    

**Explanation** :

For every _5000_ records, it will group the `keys` into a file. For any record
in the entire database, the records with those _5000_ `keys` will be fixed
into file `0.split`. After which it keeps increment to `1.split` and so on.

Please note, this does not mean that _5000_ records go into 1 file. A little
bit of careful thought should be suffice to reach to a more optimized number
for your dataset, _5000_ should work fine for a general non-specific set.

With a value of 5 instead of _5000_ for the OP's example, the above script
produces the following output:

    
    
      cat 0.split                                                             
    1   a   1
    2   a   2
    3   a   3
    4   a   4
    5   b   5
    6   b   6
    7   b   7
    8   b   8
      cat 1.split                                                             
    9   c   9
    

* * *

**_Initial naive solution_** :

This simple awk script will work beautifully.

    
    
    awk '{print >> $1".split"}' subset.data
    

What this does is uses your `query_id.split` as a filename, effectively
grouping the entire data using the `query_id`.

Your example when run on the above awk script produces the following files:

    
    
      awk '{print >> $2".split"}' temp.data                                   
      cat a.split                                                             
    1   a   1
    2   a   2
    3   a   3
    4   a   4
      cat b.split                                                             
    5   b   5
    6   b   6
    7   b   7
    8   b   8
      cat c.split                                                             
    9   c   9
    

