televerde-broadcom email perf formatting.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "aa639f60",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import os\n",
    "\n",
    "# Read the CSV file as a DataFrame\n",
    "file_path = 'C:/Users/LuqmanJunaida/Desktop/broadcom_lead pool_06-07-2023.csv'\n",
    "file_name = os.path.splitext(os.path.basename(file_path))[0]  # Extract the file name without extension\n",
    "df = pd.read_csv(file_path, header=None, dtype=str)\n",
    "\n",
    "# Extract the header row\n",
    "header_row = df.iloc[1]\n",
    "\n",
    "# Set the header row as the column names\n",
    "df.columns = header_row\n",
    "\n",
    "# Remove the first two rows (header row and the original first row)\n",
    "df = df.iloc[2:]\n",
    "\n",
    "# Save 'Contact Info' data\n",
    "contact_info_df = df.iloc[:, :14].copy()  # Make a copy to avoid the SettingWithCopyWarning\n",
    "\n",
    "# Initialize an empty DataFrame to store the combined data\n",
    "combined_df = pd.DataFrame()\n",
    "\n",
    "# Process 'Email 1' to 'Email 6' data\n",
    "for i in range(1, 7):\n",
    "    email_start_column = 14 + (i - 1) * 21  # Calculate the starting column index for the current email\n",
    "    email_end_column = email_start_column + 21  # Calculate the ending column index for the current email\n",
    "    email_df = df.iloc[:, email_start_column:email_end_column].copy()  # Extract the columns for the current email and make a copy\n",
    "    email_df.columns = email_df.columns.astype(str)  # Convert column names to string type\n",
    "    \n",
    "    # Append 'Bounced' column to contact_info_df\n",
    "    contact_info_df['Bounced'] = email_df['Bounced']\n",
    "    \n",
    "    variations = ['v1', 'v2', 'v3', 'v4']\n",
    "    combined_email_df = pd.DataFrame()\n",
    "\n",
    "    for variation in variations:\n",
    "        variation_columns = [col for col in email_df.columns if col.endswith(variation)]\n",
    "        variation_data = email_df[variation_columns]\n",
    "        variation_data.columns = [col.split()[0].replace(variation, '') if ' ' in col else col.replace(variation, '') for col in variation_columns]\n",
    "        combined_variation_df = pd.concat([contact_info_df, variation_data], axis=1)\n",
    "        combined_email_df = pd.concat([combined_email_df, combined_variation_df], axis=0)\n",
    "        variation_data['Variation'] = 'V' + variation[1]\n",
    "        combined_variation_df = pd.concat([contact_info_df, variation_data], axis=1)\n",
    "        combined_email_df = pd.concat([combined_email_df, combined_variation_df], axis=0)\n",
    "    \n",
    "    # Insert 'Bounced' column back into combined DataFrame\n",
    "    combined_email_df['Bounced'] = contact_info_df['Bounced']\n",
    "    \n",
    "    email_df['Email'] = 'Email ' + str(i)\n",
    "    combined_email_df['Email'] = 'Email ' + str(i)\n",
    "    combined_email_df = pd.concat([combined_email_df, email_df], axis=0)\n",
    "    combined_df = pd.concat([combined_df, combined_email_df], axis=0)\n",
    "\n",
    "# Remove rows where 'Variation' column is blank\n",
    "combined_df = combined_df[combined_df['Variation'].notnull()]\n",
    "\n",
    "# Determine the Email Campaign Name based on the file name\n",
    "if 'rally' in file_name:\n",
    "    email_campaign_name = 'Broadcom Rally Starter Edition'\n",
    "elif 'lead pool' in file_name:\n",
    "    email_campaign_name = 'Broadcom Lead Pool Nurture'\n",
    "else:\n",
    "    email_campaign_name = ''\n",
    "\n",
    "# Add 'Email Campaign Name' column and assign the determined value\n",
    "combined_df['Email Campaign Name'] = email_campaign_name\n",
    "\n",
    "# Add 'Email Name & Number' column by concatenating 'Email Campaign Name' and 'Email' column\n",
    "combined_df['Email Name & Number'] = combined_df['Email Campaign Name'] + ' ' + combined_df['Email']\n",
    "\n",
    "# Select the desired columns\n",
    "selected_columns = ['Job Title', 'Company Name', 'Country', 'Industry', 'Num Employees Range', 'Revenue Range',\n",
    "                    'Person Score', 'Ranking Type', 'Original Responder Type', 'Original Responder Detail',\n",
    "                   'Most Recent Responder Detail', 'Email Address', 'Solution Area', 'NLWC', 'Sent', 'Opened',\n",
    "                    'Clicked', 'Unsubscribed', 'Delivered', 'Bounced', 'Email', 'Variation', 'Email Campaign Name',\n",
    "                    'Email Name & Number']\n",
    "                \n",
    "#selected_columns = ['Job Title', 'Company Name', 'Country', 'Industry', 'Num Employees Range', 'Revenue Range',\n",
    "#                    'Person Score', 'Ranking Type', 'Email Address', 'Solution Area', 'Sent', 'Opened',\n",
    "#                    'Clicked', 'Unsubscribed', 'Delivered', 'Bounced', 'Email', 'Variation', 'Email Campaign Name',\n",
    "#                    'Email Name & Number']                \n",
    "\n",
    "# Filter the combined DataFrame with the selected columns\n",
    "combined_df = combined_df[selected_columns]\n",
    "\n",
    "# Save the combined DataFrame to a CSV file with the modified file name\n",
    "output_file_path = f'C:/Users/LuqmanJunaida/Downloads/{file_name}_output.csv'\n",
    "combined_df.to_csv(output_file_path, index=False)\n",
    "\n",
    "print(\"CSV file saved successfully.\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "25957340",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Append files\n",
    "\n",
    "import pandas as pd\n",
    "\n",
    "# Read the first CSV file\n",
    "df1 = pd.read_csv('C:/Users/LuqmanJunaida/Downloads/broadcom_rally_22-06-2023_output.csv', dtype=str)\n",
    "\n",
    "# Read the second CSV file\n",
    "df2 = pd.read_csv('C:/Users/LuqmanJunaida/Downloads/broadcom_lead pool_22-06-2023_output.csv', dtype=str)\n",
    "\n",
    "# Append the two DataFrames vertically\n",
    "appended_df = pd.concat([df1, df2], ignore_index=True)\n",
    "\n",
    "# Save the appended DataFrame to a new CSV file\n",
    "appended_df.to_csv('C:/Users/LuqmanJunaida/Downloads/broadcom_email performance_22-06-2023.csv', index=False)\n",
    "\n",
    "print(\"CSV files appended successfully.\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "15cd29f5",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}