How can I iterate through different CSV files with Python/Pandas by first column and item value?
Question: I have a folder which has over 100 CSV files each have more than 40k rows. I
am trying to iterate through these files by first column, which has the ID
numbers. My purpose is to find the rows that have the same ID numbers across
the CSV files and then create a new CSV file by concatenate/putting together
the rows that have the same ID number.

I skip the first 4 rows because they have irrelevant data.

My current code is:

# Enters the folders in the directory

    
    
    for root, dirs, files in os.walk(csv_directory):
        for item in files:
            if item.endswith(".csv"):
                date_string = item.split(".")[1]
                year_string = date_string[:4]
                file_directory = os.path.join(root,item) 
                list_csv = []
                print "Reading %s ..." % item
    
                # Reads the .csv files  
                with open(file_directory , 'rb') as file:
                    reader = csv.reader(file, delimiter = ',')
                    next(reader)
                    next(reader)
                    next(reader)
                    next(reader)
    
                    # Takes all rows for ID, col2 ,col3 in the directory
                    for row in reader:
                        index = [0,1,8]
                        list_csv.append(row[i] for i in index)
                        list_csv.append(date_string)
                        list_total.append(list_csv)
    
                print len(list_total) , "rows are added."
    
    
    print "Total Number of Rows: " , len(list_total)
    

Any help would be much appreciated!!


Answer: You could use something along the following lines.

    
    
    import pandas as pd
    from os import listdir
    from os.path import join
    source_path, dst_path = 'source/path', 'dst/path'
    

Get all `.csv` files:

    
    
    files = [f for f in listdir(source_path) if f.endswith('.csv')]
    

Read all `.csv` files and use `pd.concat()` to combine - with ~100 files at
40K rows each you'd have ~4m rows which should be manageable unless each file
has a large number of columns:

    
    
    all_files = pd.concat([pd.read_csv(join(source_path, f_name), skiprows=4) for f_name in files])
    

Use `.groupby()` to group all files by `id` (assumed to be found in
`'id_column'`), and save all same-id files back to `.csv`:

    
    
    files_by_id = all_files.groupby('id_column')
    for id, data in files_by_id:
        data.to_csv(join(dst_path, 'file_{}.csv'.format(id)))
    

