Published June 25, 2024 | Version v1
Software Open

transform_dates.py script

Authors/Creators

  • 1. University of Twente
  • 2. Universidade de São Paulo

Description

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

  1. Set the file paths:

    • file_path: Path to the input CSV file.
    • output_file_path: Path to the output CSV file.

  2. 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.

Files

Files (960 Bytes)

Name Size Download all
md5:2e721e8f22a214f1b8ba43c9a94dbfee
960 Bytes Download

Additional details

Funding

Fundação de Amparo à Pesquisa do Estado de São Paulo
A metadata model for interoperability of agricultural databases 21/15125-0