Python file to combine data.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 1. Add Prefix"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Renamed explore-causes-of-fatal-injury-table (4).csv to 56-65 explore-causes-of-fatal-injury-table (4).csv\n",
      "Renamed explore-distribution-by-age-table (4).csv to 56-65 explore-distribution-by-age-table (4).csv\n",
      "Renamed explore-distribution-by-sex-table (4).csv to 56-65 explore-distribution-by-sex-table (4).csv\n",
      "Renamed explore-fatal-map-table (4).csv to 56-65 explore-fatal-map-table (4).csv\n"
     ]
    }
   ],
   "source": [
    "import os\n",
    "\n",
    "def add_prefix_to_files(directory, prefix):\n",
    "    for filename in os.listdir(directory):\n",
    "     \n",
    "        if os.path.isfile(os.path.join(directory, filename)):\n",
    "          \n",
    "            new_filename = prefix + filename\n",
    "            \n",
    "            os.rename(os.path.join(directory, filename), os.path.join(directory, new_filename))\n",
    "            print(f\"Renamed {filename} to {new_filename}\")\n",
    "\n",
    "\n",
    "directory_path = r'56-65'  \n",
    "prefix = directory_path[-5:] + ' '  \n",
    "add_prefix_to_files(directory_path, prefix)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 2. Delete the redundant content in each file"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "\n",
    "# Directory containing the CSV files\n",
    "directory = '46-55' \n",
    "\n",
    "# Text to be removed (as extracted from the image)\n",
    "text_to_remove = '''Ages: 46 to 55\n",
    "Ethnicity: All Ethnicities\n",
    "Geographies: United States\n",
    "InjuryType: All Injury\n",
    "Intent: Suicide\n",
    "Mechanism: All Injury\n",
    "Metro: None Selected\n",
    "Outcome: Fatal\n",
    "Race: All Races\n",
    "RaceYear: 2001 - 2021 with No Race\n",
    "Sexes: Both Sexes\n",
    "Years: 2001 to 2021\n",
    "Ypll: 65\n",
    "Produced by: National Center for Injury Prevention and Control, CDC.'''\n",
    "\n",
    "# Split the extracted text into individual lines\n",
    "lines_to_remove = text_to_remove.strip().split('\\n')\n",
    "\n",
    "# Function to remove lines from a file\n",
    "def remove_lines_from_file(file_path, lines_to_remove):\n",
    "    # Read the file contents\n",
    "    with open(file_path, 'r') as file:\n",
    "        lines = file.readlines()\n",
    "\n",
    "    # Write the file contents, skipping lines that need to be removed\n",
    "    with open(file_path, 'w') as file:\n",
    "        for line in lines:\n",
    "            # Check if the line contains any of the text to be removed\n",
    "            if not any(remove_line in line for remove_line in lines_to_remove):\n",
    "                file.write(line)\n",
    "\n",
    "# Apply the function to each CSV file in the directory\n",
    "for filename in os.listdir(directory):\n",
    "    if filename.endswith('.csv'):\n",
    "        remove_lines_from_file(os.path.join(directory, filename), lines_to_remove)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 3. Add Age Group Column for Each file"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "\n",
    "directory_path = '56-65'\n",
    "\n",
    "# Function to add a column to the CSV file\n",
    "def add_column_to_csv(file_path, column_name, column_value):\n",
    "    df = pd.read_csv(file_path)\n",
    "    \n",
    "    df[column_name] = column_value\n",
    "    \n",
    "    df.to_csv(file_path, index=False)\n",
    "    print(f\"Added column '{column_name}' to {file_path}\")\n",
    "\n",
    "# Get all the CSV files in the directory\n",
    "csv_files = [file for file in os.listdir(directory_path) if file.endswith('.csv')]\n",
    "\n",
    "# Process each CSV file\n",
    "for csv_file in csv_files:\n",
    "    column_value = csv_file[:5]\n",
    "    file_path = os.path.join(directory_path, csv_file)\n",
    "    add_column_to_csv(file_path, 'Age group', column_value)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 4. Concatenate csv files based on the same column names"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "from pathlib import Path\n",
    "\n",
    "# Set the directory containing the CSV files\n",
    "directory_path = Path('map')\n",
    "\n",
    "# Find all CSV files in the directory\n",
    "csv_files = directory_path.glob('*.csv')\n",
    "\n",
    "# Initialize an empty list to store dataframes\n",
    "df_list = []\n",
    "\n",
    "# Loop over the CSV files and append to the dataframe list\n",
    "for csv_file in csv_files:\n",
    "    df = pd.read_csv(csv_file)  # Read each CSV file into a DataFrame\n",
    "    df_list.append(df)  # Append the DataFrame to the list\n",
    "\n",
    "# Concatenate all dataframes along the columns (axis=0)\n",
    "combined_df = pd.concat(df_list, axis=0)\n",
    "\n",
    "# Path for the new file\n",
    "new_file_path = directory_path / 'combined_map_file.csv'\n",
    "\n",
    "# Save the combined dataframe to a new CSV file\n",
    "combined_df.to_csv(new_file_path, index=False)\n",
    "\n",
    "# Returning the path to the new file for confirmation\n",
    "str(new_file_path)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "base",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}