how to map the values from csv to columns in pandas with filenames

user_5304058 0
Viewed 450

I have multiple bz.2 files in a folder . I have to extract them which gives me csv files and load them in database. These files do not have headers. I have to create headers in pandas and map these columns along with custom columns . I have written below code to loop through bz.2 files and extract the csv files in a dataframe.

        filenames are : 
        1.error_file_20210625_Events_00001.error.csv.bz2
        2.error_file_20210625_Events_00002.error.csv.bz2

after we extract csv data looks like :

error_file_20210625_Events_00001.error.csv:
20210625,111|112|246, apple, 20210625_error,new,,,,,D000000,true


error_file_20210625_Events_00002.error.csv:
20210625,111|222|246, pear, 20210625_error,new,,,,,C000000,false

code to extract the csv from these bz2:

    error_folder="c:\\"
        Error_filenames = glob.glob(error_folder+"error_file_"+date+"_Events_*.error.csv.bz2")
        if Error_filenames:
             df_error_generator = (pd.read_csv(f, lines=True, compression={'method' : 'bz2'}) for f in Error_filenames)
             df_error = pd.concat(df_error_generator, ignore_index=True)
             filename="error_file_"+ date +"_Events_error.csv"
             df_error.to_csv("c:\\chits\"+filename)
             df_error_csv=pd.DataFrame(pd.read_csv("c:\\chits\"+filename))

headers=["filedate","errorcode","errorROEID","ROEID","type","rawrecord","filename"]

now I have to map the data into headers:

filedate  errorcode     errorROEID  ROEID            type  rawrecord                                                           filename
20210625  111|112|246   apple       20210625_error   new   20210625,111|112|246, apple, 20210625_error,new,,,,,D000000,true    error_file_20210625_Events_00001.error.csv
20210625  111|222|246   pear        20210625_error   new   20210625,111|222|246, pear, 20210625_error,new,,,,,C000000,false    error_file_20210625_Events_00002.error.csv

Here,

filedate is a date from the file
errorcode is second column 
errorROEID is the third column 
ROEID is the fourth column 
type is fifth column 
rawrecord is the whole row 
filename is the filename

I am not understanding how do i extract filename and filedate and inserting whole row in rawrecord column. Can anyone please help me?

pandas csv python
1 Answers

Here is a possible solution using just the built in Python libraries:

from operator import itemgetter
import glob
import bz2
import csv

header = ["filedate", "errorcode", "errorROEID", "ROEID", "type", "rawrecord", "filename"]
req_cols = itemgetter(0, 1, 2, 3, 4, 9, 10)

with open("output.csv", "w", newline="") as f_output:
    csv_output = csv.writer(f_output)
    csv_output.writerow(header)
    
    for error_filename in glob.glob("*.bz2"):
        with bz2.open(error_filename, "rt", encoding="utf-8") as f_error_file:
            csv_input = csv.reader(f_error_file, skipinitialspace=True)
            
            for orig_row in csv_input:
                row = req_cols(orig_row)
                csv_output.writerow([row[0], row[1], row[2], row[3], row[4], ",".join(orig_row), error_filename])

A dataframe could be constructed instead as follows:

import pandas as pd
from operator import itemgetter
import glob
import bz2
import csv

header = ["filedate", "errorcode", "errorROEID", "ROEID", "type", "rawrecord", "filename"]
data = []
req_cols = itemgetter(0, 1, 2, 3, 4, 9, 10)

for error_filename in glob.glob("*.bz2"):
    with bz2.open(error_filename, "rt", encoding="utf-8") as f_error_file:
        csv_input = csv.reader(f_error_file, skipinitialspace=True)
        
        for orig_row in csv_input:
            row = req_cols(orig_row)
            data.append([row[0], row[1], row[2], row[3], row[4], ",".join(orig_row), error_filename])

df = pd.DataFrame(data, columns=header)
print(df)

Giving you:

   filedate    errorcode errorROEID           ROEID type                                                       rawrecord                                        filename
0  20210625  111|112|246      apple  20210625_error  new  20210625,111|112|246,apple,20210625_error,new,,,,,D000000,true  error_file_20210625_Events_00001.error.csv.bz2
1  20210625  111|222|246       pear  20210625_error  new  20210625,111|222|246,pear,20210625_error,new,,,,,C000000,false  error_file_20210625_Events_00002.error.csv.bz2
user_49857330
Related