transform_dates.py script

Overview The transform_dates.py script is designed to transform the date column in a CSV file from the MMM-yy format (e.g., Jan-23) to the ISO yyyy-MM format (e.g., 2023-01). This script reads the CSV file, applies the transformation, and saves the result to a new CSV file. Requirements Python 3.x pandas library You can install the pandas library using pip: sh Copy code pip install pandas Usage Set the file paths: file_path: Path to the input CSV file. output_file_path: Path to the output CSV file. Run the script: If using a standalone Python script, run the following command in your terminal: sh Copy code python transform_dates.py If using a Jupyter notebook, create a new cell, paste the code, and execute the cell. Code Explanation python Copy code import pandas as pd from datetime import datetime # Load your CSV file file_path = r"C:\Users\Filipi Soares\Programing\ConabDataMonthMerged_v.3.csv" df = pd.read_csv(file_path) The pandas library and datetime module are imported. The CSV file is loaded into a DataFrame using pd.read_csv(). python Copy code # Function to transform date def transform_date(value): try: if pd.isnull(value) or value.strip() == "": return None else: # Parse the date using the format 'MMM-yy' parsed_date = datetime.strptime(value.strip(), "%b-%y") # Format the date to ISO format 'yyyy-MM' return parsed_date.strftime("%Y-%m") except ValueError: return None The transform_date function checks if the date value is null or empty. If so, it returns None. Otherwise, it parses the date using the MMM-yy format and converts it to the yyyy-MM format. If the value cannot be parsed, it returns None. python Copy code # Apply the transformation to the 'date' column df['date'] = df['date'].apply(transform_date) The apply method is used to apply the transform_date function to each value in the date column of the DataFrame. python Copy code # Save the DataFrame to a new CSV file output_file_path = r"C:\Users\Filipi Soares\Programing\transformed_ConabDataMonthMerged_v.3.csv" df.to_csv(output_file_path, index=False) print(f"Transformed data saved to {output_file_path}") The transformed DataFrame is saved to a new CSV file specified by output_file_path using to_csv(). A message is printed to indicate that the transformed data has been saved. Example Ensure you have the following file paths correctly set in the script: Input CSV file path: file_path = r"C:\Users\Filipi Soares\Programing\ConabDataMonthMerged_v.3.csv" Output CSV file path: output_file_path = r"C:\Users\Filipi Soares\Programing\transformed_ConabDataMonthMerged_v.3.csv" Run the script to transform the dates and save the result to the specified output file. This script ensures that the date column in your CSV file is correctly transformed from MMM-yy format to yyyy-MM format.