Split a CSV if specific string in row is found (Python/Pandas/Bash)

Question

I've got one crappy CSV file with multiple headers inside it. It looks like this:

File1:
    #HEADER COL1 COL2
    data
    data
    data
    #HEADER COL1 COL2 COL3
    data
    data
    data
    data
    data
    #HEADER COL1 COL2 COL3 COL4
    data
    data
    ...

Since I can't load it with pandas due to the in-the-file-headers, I'm looking to split the data at each header to a separate file (or separate data frames in pandas). Is there a way to do this?

This CSV is generated by sensors. If a sensor is added, the header will get a new column. This could also happen in the in-the-file-headers. So it is NOT a solution to delete those headers. (Clean wrong header inside Dataframe with Python/Pandas)

It would be really nice to do it in python/pandas, but I would also be happy with a bash command/script solution.

Expected output:

File1:
        #HEADER COL1 COL2
        data
        data
        data
File2:
        #HEADER COL1 COL2 COL3
        data
        data
        data
        data
        data
File3:
        #HEADER COL1 COL2 COL3 COL4
        data
        data
        ...

Thank you!

Are you able to show your expected output as well as what you've tried ? — Umar.H
– Umar.H, Commented Nov 13, 2019 at 15:37
Are those HEADER lines always the same? Can you post a realistic input? — RomanPerekhrest
– RomanPerekhrest, Commented Nov 13, 2019 at 15:37
No the HEADER lines can change (but they all start with "#"). So if I could locate "#", I could split the files — vince
– vince, Commented Nov 13, 2019 at 15:44

karakfa · Accepted Answer · 2019-11-13 15:57:34Z

1

awk to the rescue!

$ awk '/^#HEADER/{close(FILENAME "_" c); c++} {print > (FILENAME "_" c)}' file

will split input file into file_n parts, where n is the section counter.

edited Nov 13, 2019 at 15:57

answered Nov 13, 2019 at 15:42

karakfa

67.8k8 gold badges45 silver badges59 bronze badges

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

2 Comments

vince Over a year ago

in my case this just worked beautifully! awk '/^#/{close(FILENAME "_" c); c++} {print > (FILENAME "_" c)}' file

vince Over a year ago

last question: how can I export the result file to another folder?

RomanPerekhrest · Accepted Answer · 2019-11-13 16:17:30Z

1

With convenient csplit command (to split a file into sections by pattern):

csplit -b %d -f file -z input_file '/#HEADER.*/' '{*}'

Viewing results:

$ head file[0-9]
==> file0 <==
#HEADER COL1 COL2
data
data
data

==> file1 <==
#HEADER COL1 COL2 COL3
data
data
data
data
data

==> file2 <==
#HEADER COL1 COL2 COL3 COL4
data
data
...

answered Nov 13, 2019 at 16:17

RomanPerekhrest

93.1k4 gold badges75 silver badges112 bronze badges

Collectives™ on Stack Overflow

Split a CSV if specific string in row is found (Python/Pandas/Bash)

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related