Subjecting several CSV files in a folder to the same operations (Version 2.0)

user_13238222 0
Viewed 93

EDIT (v2.0): Added an f-string (as suggested by user @NissesSenap) at the beginning of the script that allows me to define a text ("snippet"?) that names the new files and header automatically.

I am new to Python (but very proud of my little script!). I have CSV files with data that I am subjecting to some math and reorganizing (eg. renaming and removing columns). I then exporting these as a new CSV file. Several CSV files in a common folder is the combined into one CSV file that I use for data analysis.

Step-by-step what my script(s) aim to do

  1. Subject data in CSV file to operations and reorganizing
  2. Export as new CSV in a common folder
  3. Combine several CSV files in folder based on common index

My issue is that I have to do step 1. and 2. on each sample file one at a time by going into the scrip, define filename using an f-string. Which is manageable but time consuming when I deal with a lot of files.

I would like to take all files in a folder, subject them to step 1. and 2. automatically, then export them into another folder for step 3.

Any and all feedback greatly appreciated!

---> Link to sample data

My code so far...

Script for step 1. and 2.

# Import modules and libraries

import pandas as pd
import numpy as np
from glob import glob

# Define filename

FILENAME = "FILENAME"

# Open CSV file

df = pd.read_csv(f"data/{FILENAME}_data.csv")

# Adds new columns and calulates length in picometer, length score and splaying score

df['Length (um)'] = df.loc[: , 'Length (pm)'] / 1000000
df['Length score'] = df.loc[: , 'Length (um)'] / df['Curvature points'] * 2
df['Splaying score'] = df.loc[: , 'Length score'] * df['Splaying profile']

# Saves safecopy as data and creates and opens new CSV as results 

df.to_csv(f"data/{FILENAME}_data.csv", index=False)
df.to_csv(f"results/{FILENAME}_results.csv", index=False)
df_results = pd.read_csv(f"results/{FILENAME}_results.csv")

# Removes unnecessary columns from results

df_results = df_results.drop(['Splaying profile', 'Curvature points', 'Length (pm)', 'Length score'],axis=1)

# Rename columns

df_results.rename(columns={'Length (um)' : f"{FILENAME} Average Length (um)", 'Splaying score' : f"{FILENAME} Average Splaying score"}, inplace=True)

# Group by objects and calculate average for each object then save to new CSV

df_results.groupby("Object").mean().to_csv(f"results/{FILENAME}_results.csv")
df_results = pd.read_csv(f"results/{FILENAME}_results.csv")

Script for step 3.

# Import modules and libraries

import pandas as pd
import numpy as np
from glob import glob

# Use glob to list all files in folder. * includes variable part of filename

stock_files = sorted(glob('results/*_results.CSV'))

# Turn list into dataframes and setting one header as index

dataframes = [pd.read_csv(stock_files, index_col ="Object") for stock_files in stock_files]

# Use concat to join based on index. axis=1 stacks columns and join=outer includes all data 

pd.concat(dataframes, axis=1, sort=False, join='outer').to_csv('results/summary_results.csv', index=True)
pandas csv python
1 Answers

I used pathlib.Path() in combination with glob.glob() to get all files from an inpurt directory. this works fine. Afterwards you can iterate over all files found with glob and read the csv to a pandas dataframe with pd.read_csv(file). After that you can check with an if-condition, utilizing the .stem property of pathlib to only get the file name (see docs), and check if the filename of a given input csv-file matches the pattern you'd expect (I just guessed it could be B1xxxx).

#!/usr/bin/env python3
import pandas as pd
from glob import glob
from pathlib import Path

def main():
    path_to_input = Path('C:/Test/Data')
    input_files = sorted(path_to_input.glob('*.csv'))
    for file in input_files:
        df = pd.read_csv(file)
        if file.stem.startswith('B1'):
            pass
            # Processing for file Type 1 (B1xx)
        elif file.stem.startswith('B2'):
            pass
            # Processing for file Type 2 (B2xx)
        else:
            print(f"File \"{file.parts[-1]}\" name is not recognized")
user_73184880
Related