Iterate this program to perform ETL jobs in python

Question

I have a bunch of .json files in a folder which follow a the pattern file00.json, file01.json, etc. I tried to iterate the below code for all the .json files. But it is not working. So I am manually doing this for each file. Any help here is appreciated.

    for filename in os.listdir(os.getcwd()):  
    root, ext = os.path.splitext(filename)  
    if ext == '.json':  
        with open('file-00.json') as json_file:
            data = json.load(json_file)
            header = ['Pair', 'Rank', 'Suite'] 
        with open("file-00.csv", 'w') as file:
            dw = csv.DictWriter(file, delimiter=',',fieldnames=header)
            dw.writeheader()
        with open('batch-00.csv', 'a', newline= '') as f:
            for i in data:
                data1 = [i, i[:-1], i[-1]]
                write = csv.writer(f)
                write.writerow(data1)
                print(data1)
# reading the csv file
df = pd.read_csv("file-00.csv")
df
  
#updating the column value/data
df['Rank'] = df['Rank'].replace({'K': '10'})
df['Rank'] = df['Rank'].replace({'Q': '10'})
df['Rank'] = df['Rank'].replace({'J': '10'})
df['Rank'] = df['Rank'].replace({'A': '1'})
df
  
#writing into the file
df.to_csv("file-00.csv", index=False)
  
#print(df)

Since you mention that the filenames follow a pattern(file00.json, file01.json...), use that to read them — Manimaran Paneerselvam, Jan 03 '22 at 04:52

score 0 · Answer 1 · answered Jan 03 '22 at 04:56

This is pretty close to ideal case for use of the glob module.

>>> import glob
>>> JPEGs = glob.glob("*jpg")
>>> for f in JPEGs:
...     print(f)
...
20211229_071856725_iOS.jpg
20211224_083126904_iOS.jpg
20211224_200953667_iOS.jpg
20211229_071900593_iOS.jpg

In your case, you said you're looking for JSON files, so you'd use a pattern like

>>> JSONs = glob.glob("*json")

(This assumes that your process is in the same working directory as the files you want to operate on).

Once you've got the list of files, you can iterate through them, process them however you like and then write out the results as a csv file. You can do that yourself, or using the csv module.

https://stackoverflow.com/questions/16923281/writing-a-pandas-dataframe-to-csv-file is useful here — James McPherson, Jan 03 '22 at 04:57

Iterate this program to perform ETL jobs in python

1 Answers1