I have 36 CSV files in a folder that contain uneven numbers of columns (no. of columns range from 90 to 255 in each file). In a single CSV file, the maximum number of rows is 300, however, the columns can have zero to 300 rows. An example of a CSV file is as follows:
Row col1 col2 col3 ........................ col200
1 2 3 4 ......................... 25
2 1 8 .......................... 0.2
3 5 2 ........................... 5
. . . .......................... .
. . . ........................... .
. . . ........................... .
. . . ........................... .
. . . ........................... .
. . . ........................... .
300 3 12 .......................... 1
I want to convert these CSV files to get the following properties using Python:
All the converted
CSVfiles must have the same columns (same order and size). The columns are the union of columns (non-repeated) in all originalCSVfiles.The columns of the converted
CSVfile must be in order. i.e.3rd columnofc1.csvmust be the3rd columnof other remainingCSVfiles too.If any of the union columns is absent in any original
CSVfile, it's convertedCSVfile will have the missing columns added with default value in the rows (a fixed value in all 300 rows).If any column is presented in both the union columns and an original
CSVfile:(a) If the numbers of rows in this column are 300, copy as it is.
(b) If the numbers of rows in this column are less than 300, fill the remaining rows by an average of available values in this row.
To achieve the above-mentioned properties, I have written the following code in python:
import pandas as pd
import csv
import glob
import os
path = r'FILE PATH TO ORIGINAL FILES' # file path to original files
all_files = glob.glob(path + "/*.csv")
combined_csv = pd.concat([pd.read_csv(f) for f in all_files]) #To get common columns
master_set =list(combined_csv.columns)
for file in all_files:
filtered_df = pd.read_csv(file)
for cols in master_set:
if(cols in filtered_df):
if(filtered_df[cols].count()>300): pass
elif (filtered_df[cols].count()<300):
total = sum(value for value in filtered_df[cols])
avg = total/filtered_df[cols].count()
i = filtered_df[cols].count()
while i<301:
filtered_df.at[i,cols] = avg
i+=1
else:
filtered_df[cols] = 10
file_name = os.path.split(file)[-1] #Select individual file (eg. c1.csv)
file_name_path = os.path.join('FILE PATH TO CONVERTED FILES' + file_name)
filtered_df.to_csv(file_name_path)
After running the above code, I get 36 converted CSV files with a common set of columns. The rows in added columns (columns that are in union columns but not in individual files) are filled with the default values. However, the following properties are still not fulfilled with the above code.
- The order of columns in newly created
CSVfiles does not match. - The above-mentioned property 4(b) is not achieved. i.e. any column of an original file that is present in union columns but has less than 300 values (rows <300) is not filled by the interpolated value.
I will update/edit my question for any further clarity.
Any help, please!