Clean first row of data in python

user_18620224 0
Viewed 58

Actually, I have 1000 files but those 1000 files will have a format similar to the 3 csv files that I mentioned as Input Files, but their first row is quite different. If you see file 1, in first row there is ","comma, in file 2, the first row there is "sep=," and in file 3, it is totally fine(no weird characters). I am writing a python file where I want to clean the first row of data. So, in my example File 1 and File 2 should clean and File 3 should remain untouched should not require any cleaning. Just to note, every csv has different headers.

Input CSV files look like this(Actual -1000 files, but format of 1000 files will fall in any of these files categories)

File1.csv

,
Serial Num,Reading #,Date,Time,Label
640905,1,2018-04-55,7:45:58,
840806,2,1096-04-22,7:47:19,20

File2.csv

sep=,
SNum,Reading,Date,Time,La,,Check
640905,1,2028-04-55,18:45:58,10,,g
8840806,2,2096-04-22,20:47:19,20,,b

File3.csv

Date,Time,A_iop,b_m,c_om
12/10/2025,07:59:54,10,5559,89
21/10/2025,07:49:55,20,4545,90

My Python code is till now this:

from pathlib import Path
import pandas as pd
import time
import argparse

parser = argparse.ArgumentParser(description='Process')

parser.add_argument('path',help='define the directory to folder/file')

start = time.time()
def main(path_name):   
    if path_name.is_file():
        fnames = [path_name]
    else:
        fnames = list(Path(path_name).glob("*.csv")) + list(Path(path_name).glob("*.xls"))       
    for fn in fnames:
        if "csv" in str(fn):
            df = pd.read_csv(str(fn))   
            #df = pd.read_csv(str(fn),index_col=False)
            #df = pd.read_csv(str(fn),sep=',', index_col=False, dtype='unicode', header=1)   
            print(df)               
  
if __name__== "__main__":
    start = time.time()
    args = parser.parse_args()
    path = Path(args.path)
    main(path)
    print("Processed time: ", time.time()-start)

I want Output CSV look like this:

File1.csv(No Change)

Serial Num,Reading #,Date,Time,Label
640905,1,2018-04-55,7:45:58,
840806,2,1096-04-22,7:47:19,20

File2.csv(No Change)

SNum,Reading,Date,Time,La,,Check
640905,1,2028-04-55,18:45:58,10,,g
8840806,2,2096-04-22,20:47:19,20,,b

File3.csv(Same as Input)

Date,Time,A_iop,b_m,c_om
12/10/2025,07:59:54,10,5559,89
21/10/2025,07:49:55,20,4545,90

I am not able to formulate a logic in python how to clean those files before doing any further analysis? any help?

pandas python
1 Answers

pandas.read_csv() reads a CSV-like file (can have any delimiter) to a DataFrame object and has an optional parameter skiprows which can be used to ignore the first (or other) lines of the csv file.

skiprows: list-like, int or callable, optional

Line numbers to skip (0-indexed) or number of lines to skip (int) at the start of the file.

If callable, the callable function will be evaluated against the row indices, returning True if the row should be skipped and False otherwise. An example of a valid callable argument would be lambda x: x in [0, 2].

Then you can use DataFrame.to_csv() method to save it back to a file (again, you have some options there).

So the following code will do what you want:

import pandas as pd

df1 = pd.read_csv('File1.csv', skiprows=1)
df2 = pd.read_csv('File2.csv', skiprows=1)
df3 = pd.read_csv('File3.csv')


df1.to_csv('File1_NEW.csv')
df2.to_csv('File2_NEW.csv')
df3.to_csv('File3_NEW.csv')

(of course, there is no need to read and save the third file, but I included it just for the shake of completeness)

Edit: After the clarification that there are 1000s of files that belong to one of those three cases.

Assuming all of your files belong to exactly the cases you described above, simply open the files as text and read the first two lines. In each one, count the number of commas. If they match, you are in case 3 (parse the whole file as CSV). Otherwise you are in one of the first two cases (so parse using skiprows=1).

This is the modified code:

    for fn in fnames:
        if "csv" in str(fn):
            with open(str(fn)) as fn_test:
                first_line             = fn_test.readine()
                second_line            = fn_test.readline()
                num_commas_first_line  = len(first_line.split(','))
                num_commas_second_line = len(second_line.split(','))
            
            if num_commas_first_line == num_commas_second_line: # Case 3 
                df = pd.read_csv(str(fn))
            else: # Cases 1, 2
                df = pd.read_csv(str(fn), skiprows=1)
user_69429180
Related