Pandas: Split DF into multiple csv

user_15379514 0
Viewed 122

I have a CSV file that looks like this:

|100|Header1|Header2|
|120| xxx   | xxx   |
|120| yyy   | yyy   |
|120| zzz   | zzz   |
|200|Header3|Header4|
|220| xxx   | xxx   |
|220| yyy   | yyy   |
|220| zzz   | zzz   |
|300|Header5|Header6|
|320| xxx   | xxx   |
|320| yyy   | yyy   |
|320| zzz   | zzz   |

All headers start at a series of 100 which need to be split into it's individual csv file. So in this case there will be 3 csv files, with one having rows 100 and 120s, the second csv file with 200 and 220s and third with 300 and 320s.

The 100, 200 and 300 rows indicate the start of a new table. is there an efficient way to do this in pandas without having to loop through and create a new csv file if it encounters 100, 200 etc?

pandas csv python
2 Answers

Using groupby:

df = pd.read_csv('test.txt',sep = '|',header=None)
df.drop([0,4],axis = 1,inplace = True)
for i,sd in df.groupby(df[1]//100):
    sd.to_csv(f'test{i*100}.csv',index=False,header=False)

test.txt contains raw data provided by OP:

|100|Header1|Header2|
|120| xxx   | xxx   |
|120| yyy   | yyy   |
|120| zzz   | zzz   |
|200|Header3|Header4|
|220| xxx   | xxx   |
|220| yyy   | yyy   |
|220| zzz   | zzz   |
|300|Header5|Header6|
|320| xxx   | xxx   |
|320| yyy   | yyy   |
|320| zzz   | zzz   |

Output:

It would create 3 files test100.csv,test200.csv and test300.csv

test100.csv

100,Header1,Header2
120, xxx   , xxx   
120, yyy   , yyy   
120, zzz   , zzz

test200.csv

200,Header3,Header4
220, xxx   , xxx   
220, yyy   , yyy   
220, zzz   , zzz

test300.csv

300,Header5,Header6
320, xxx   , xxx   
320, yyy   , yyy   
320, zzz   , zzz
user_147849120

I would consider using a pure python approach

with open('test.csv') as fh:
    current_file_handle = None
    current_file_name = None
    for line in fh.readlines():
        p0 = line.find('|') + 1
        p1 = line[p0:].find('|') + p0
        if current_file_name != f'{line[p0]}00':
            current_file_name = line[p0:p1]
            current_file_handle = open(f'test_{current_file_name}.csv', 'w')
        current_file_handle.write(line)
    current_file_handle.close()
user_23366540
Related