Faster way to split a large CSV file evenly by Groups into smaller CSV files?

I'm sure there is a better way for this but I am drawing a blank. I have a CSV file in this format. The ID column is sorted so everything is grouped together at least:

Text                 ID
this is sample text, AAAA
this is sample text, AAAA
this is sample text, AAAA
this is sample text, AAAA
this is sample text, AAAA
this is sample text2, BBBB
this is sample text2, BBBB
this is sample text2, BBBB
this is sample text3, CCCC
this is sample text4, DDDD
this is sample text4, DDDD
this is sample text5, EEEE
this is sample text5, EEEE
this is sample text6, FFFF
this is sample text6, FFFF

What I want to do is split the CSV fast across X amount of smaller CSV files fast. So if X==3, then AAAA would go into "1.csv", BBBB would go into "2.csv", CCCC would go into "3.csv" and the next group would loop back around and go into "1.csv".

The groups vary in size so a hardcoded split by numbers won't work here.

Is there a faster way to split these reliably then my current method which just uses Pandas groupby in Python to write them?

    file_ = 0
    num_files = 3

    for name, group in df.groupby(by=['ID'], sort=False):

        file_+=1
        group['File Num'] = file_

        group.to_csv(file_+'.csv',index=False, header=False, mode='a')

        if file_ == num_files:

            file_ = 0

This is a python based solution but I am open to stuff using awk or bash if it gets the job done.

EDIT:

For clarification, I want the groups split across a fixed amount of files I can set.

In this case, 3. (So x = 3). The first group (AAAA) would go into 1.csv, the 2nd into 2.csv, the third into 3.csv and then for the fourth group, it would loop back and insert it into 1.csv. etc.

Example output 1.csv:

Text                 ID
this is sample text, AAAA
this is sample text, AAAA
this is sample text, AAAA
this is sample text, AAAA
this is sample text, AAAA
this is sample text4, DDDD
this is sample text4, DDDD

Example output 2.csv:

Text                 ID
this is sample text2, BBBB
this is sample text2, BBBB
this is sample text2, BBBB
this is sample text5, EEEE
this is sample text5, EEEE

Example output 3.csv:

Text                 ID
this is sample text3, CCCC
this is sample text6, FFFF
this is sample text6, FFFF

Using any awk in any shell on every Unix box:

$ cat tst.awk
NR==1 {
    hdr = $0
    next
}
$NF != prev {
    out = (((blockCnt++) % X) + 1) ".csv"
    if ( blockCnt <= X ) {
        print hdr > out
    }
    prev = $NF
}
{ print > out }

<p>

$ awk -v X=3 -f tst.awk input.csv

<p>

$ head [0-9]*.csv
==> 1.csv <==
Text                 ID
this is sample text, AAAA
this is sample text, AAAA
this is sample text, AAAA
this is sample text, AAAA
this is sample text, AAAA
this is sample text4, DDDD
this is sample text4, DDDD

==> 2.csv <==
Text                 ID
this is sample text2, BBBB
this is sample text2, BBBB
this is sample text2, BBBB
this is sample text5, EEEE
this is sample text5, EEEE

==> 3.csv <==
Text                 ID
this is sample text3, CCCC
this is sample text6, FFFF
this is sample text6, FFFF

If X was some large enough number that you exceed your system limit for concurrently open files and you start getting a "too many open files" error then you'd need to use GNU awk as it handles that internally or change the code to only have 1 file open at a time:

NR==1 {
    hdr = $0
    next
}
$NF != prev {
    close(out)
    out = (((blockCnt++) % X) + 1) ".csv"
    if ( blockCnt <= X ) {
        print hdr > out
    }
    prev = $NF
}
{ print >> out }

or implement your own way of managing how many files are open concurrently.

EDIT: here's what the suggestion by @PaulHodges in the comments would result in a script like:

NR == 1 {
    for ( i=1; i <= X; i++ ) {
        print > (i ".csv")
    }
    next
}
$NF != prev {
    out = (((NR-1) % X) + 1) ".csv"
    prev = $NF
}
{ print > out }

You may use this awk solution:

awk -v X=3 '
FNR == 1 {   # save 1st record as header 
   hdr = $0
   next
}
p != $NF {   # ID field changes, move to new output csv file 
   close(fn)
   fn = ((n++ % X) + 1)".csv" # construct new file name
}
!seen[fn]++ {                 # do we need to print header
   print hdr > fn 
}
{
   print >> fn                # append each record to output
   p = $NF                    # save last field in variable p
}' file

With your shown samples please try following code. As mentioned considering last column is sorted as per shown samples.

awk -v x="3" '
BEGIN{
  count=1
  outFile=count".csv"
}
FNR==1{
  print
  next
}
prev!=$NF && prev{
  close(outFile)
  count++
  outFile=count".csv"
}
{
  print >> (outFile)
  prev=$NF
}
x==count{ count=1 }
' Input_file

Using groupby and factorize modulo the number of desired groups (N):

N = 3

for i, g in df.groupby(pd.factorize(df['ID'])[0]%N):
    g.to_csv(f'chunk{i+1}.csv', index=False)

Output files:

# chunk1.csv
Text,ID
this is sample text,AAAA
this is sample text,AAAA
this is sample text,AAAA
this is sample text,AAAA
this is sample text,AAAA
this is sample text4,DDDD
this is sample text4,DDDD

# chunk2.csv
Text,ID
this is sample text2,BBBB
this is sample text2,BBBB
this is sample text2,BBBB
this is sample text5,EEEE
this is sample text5,EEEE

# chunk3.csv
Text,ID
this is sample text3,CCCC
this is sample text6,FFFF
this is sample text6,FFFF

timings

Tested on 14 million rows:

15.8 s ± 687 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)

~14s of which is due to I/O

Comparison with other answers (using time in the shell):

# @mozway as a python script including imports and reading the file
real	0m20,834s

# @RavinderSingh13
real	1m22,952s

# @anubhava
real	1m23,790s

# @Ed Morton (updated code, original solution was 2m58,171s)
real	0m8,599s

As a function:

import pandas as pd

def split_csv(filename, N=3, id_col='ID', out_basename='chunk'):
    df = pd.read_csv(filename)
    for i, g in df.groupby(pd.factorize(df[id_col])[0]%N):
        g.to_csv(f'{out_basename}{i+1}.csv', index=False)

split_csv('my_file.csv', N=3)

Here

group.to_csv(file_+'.csv',index=False, header=False, mode='a')

you are providing string as 1st argument, however to_csv method allow you to provide file-like object as 1st argument, in which case you might avoid doing file-opening-related stuff multiple times, consider following simple simple comparison

import os
import time
import pandas as pd
REPEAT = 1000
df = pd.DataFrame({'col1':range(100)})
t1 = time.time()
for _ in range(REPEAT):
    df.to_csv('file.csv',index=False,header=False,mode='a')
t2 = time.time()
os.remove('file.csv')
t3 = time.time()
with open('file.csv','a') as f:
    for _ in range(REPEAT):
        df.to_csv(f,index=False,header=False)
t4 = time.time()
print('Using filename',t2-t1)
print('Using filehandle',t4-t3)

gives output

Using filename 0.35850977897644043
Using filehandle 0.2669696807861328

Observe that 2nd way take around 75% time of 1st way, so whilst it is faster it is still same order of magnitude.