file_format.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "path = '/Users/bablidey/Documents/KOAA/explore'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import os\n",
    "import csv\n",
    "\n",
    "# Function to detect delimiter and inspect column count\n",
    "def inspect_csv(file_path):\n",
    "    with open(file_path, 'r') as csvfile:\n",
    "        lines = csvfile.readlines()\n",
    "        delimiter = csv.Sniffer().sniff(lines[7]).delimiter\n",
    "        num_cols = len(lines[7].split(delimiter))\n",
    "        return delimiter, num_cols, lines  # Return lines as well\n",
    "\n",
    "# Directory containing the CSV files\n",
    "directory_path = '/Users/babLidey/Documents/KOAA/explore'\n",
    "\n",
    "# Initialize an empty DataFrame to hold all the data\n",
    "all_data = pd.DataFrame()\n",
    "\n",
    "# Loop through each file in the directory\n",
    "for filename in os.listdir(directory_path):\n",
    "    if filename.endswith('.csv'):\n",
    "        file_path = os.path.join(directory_path, filename)\n",
    "        \n",
    "        # Detect the delimiter and number of columns, and get lines\n",
    "        delimiter, num_cols, lines = inspect_csv(file_path)\n",
    "        \n",
    "        try:\n",
    "            # Read the CSV file, skipping the metadata rows\n",
    "            df = pd.read_csv(file_path, delimiter=delimiter, header=None, skiprows=7)\n",
    "            \n",
    "            # Handle files with different column counts\n",
    "            if num_cols > 6:  # Assuming 6 is the correct number of useful data columns\n",
    "                df = df.iloc[:, :6]  # Limit to the first 6 columns if more are found\n",
    "            \n",
    "            # Extract the region name from the lines\n",
    "            region = lines[1].split('KOAA GA4 ')[1].strip()\n",
    "            \n",
    "            # Define the column names based on actual data columns present\n",
    "            df.columns = ['Month', 'Year', 'Active Users', 'Total Users', 'Sessions', 'Views'][:df.shape[1]]\n",
    "            \n",
    "            # Insert the 'Region' column\n",
    "            df.insert(0, 'Region', region)\n",
    "            \n",
    "            # Append to the all_data DataFrame\n",
    "            all_data = pd.concat([all_data, df], ignore_index=True)\n",
    "        \n",
    "        except Exception as e:\n",
    "            print(f\"Error processing file {filename}: {e}\")\n",
    "\n",
    "# Handle non-finite values before conversion\n",
    "all_data.fillna(0, inplace=True)\n",
    "\n",
    "# Convert columns to the correct datatype\n",
    "try:\n",
    "    all_data['Month'] = all_data['Month'].astype(int)\n",
    "    all_data['Year'] = all_data['Year'].astype(int)\n",
    "    all_data['Total Users'] = all_data['Total Users'].astype(int)\n",
    "    all_data['Sessions'] = all_data['Sessions'].astype(int)\n",
    "    all_data['Views'] = all_data['Views'].astype(int)\n",
    "except Exception as e:\n",
    "    print(f\"Error converting data types: {e}\")\n",
    "\n",
    "# Save the combined data to a new CSV file\n",
    "all_data.to_csv('combined_data.csv', index=False)\n",
    "\n",
    "print(\"Data combined and saved to 'combined_data.csv'.\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_csv('combined_data.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Removeing the first line from every region - i.e. Totals\n",
    "df = df[df.duplicated(subset=['Region'],keep='first')]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.sort_values(by=['Region','Month'],inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.reset_index(inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.drop(columns='index',inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# months = {\n",
    "#     1: 'Jan-24',\n",
    "#     2: 'Feb-24',\n",
    "#     3: 'Mar-24',\n",
    "#     4: 'Apr-24',\n",
    "#     5: 'May-24',\n",
    "#     6: 'Jun-24',\n",
    "#     7: 'Jul-24'\n",
    "# }"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# df['month'] = df['Month'].map(months)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# df.drop(columns = ['Month','Year'],inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_set = set(df['Region'].unique())\n",
    "col_set = set(['Rochester', 'Albany', 'Fairfield County', 'Buffalo',\n",
    "'Houston', 'Salt Lake', 'Ann Arbor/Detroit', 'St. Louis', 'Westchester',\n",
    "'Hudson Valley', 'Hartford', 'Denver', 'Austin', 'San Antonio',\n",
    "'Nashville', 'MSP', 'Atlanta', 'Phoenix', 'Research Triangle',\n",
    "'Charlotte', 'Dallas', 'DMV', 'Ft Worth', 'Midcities', 'Indianapolis',\n",
    "'Jacksonville', 'Kansas City', 'Memphis', 'Milwaukee',\n",
    "'Nassau / Long Island', 'Philadelphia', 'Pittsburgh', 'Providence',\n",
    "'Suffolk', 'Tampa', 'Toronto', 'Vancouver', 'Inland Empire',\n",
    "'Los Angeles', 'Orange County', 'San Diego', 'San Fernando',\n",
    "'San Francisco', 'San Jose', 'Portland', 'Seattle'])\n",
    "\n",
    "intersection = df_set and col_set\n",
    "need_to_rename = df_set - intersection"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "need_to_rename"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "rename_region = {\n",
    "    'Ann Arbor - Detroit': 'Ann Arbor/Detroit',\n",
    "    'Fort Worth' : 'Ft Worth',\n",
    "    'Long Island (Nassau)' : 'Nassau / Long Island',\n",
    "    'Philly': 'Philadelphia',\n",
    "    'Saint Louis':'St. Louis',\n",
    "    'Salt Lake City' : 'Salt Lake'\n",
    "}\n",
    "\n",
    "df['Region']=df['Region'].replace(rename_region)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#df.set_index('Region',inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "user_group=df.groupby(['Year','Month', 'Region'], as_index=False).sum()\n",
    "users=user_group.pivot(index='Month',columns='Region',values='Total Users')\n",
    "users=users.iloc[0:7,]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "session_group=df.groupby(['Year','Month', 'Sessions'], as_index=False).sum()\n",
    "sessions=session_group.pivot(index='Month',columns='Region',values='Sessions')\n",
    "sessions=sessions.iloc[0:7,]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "view_group=df.groupby(['Year','Month', 'Views'], as_index=False).sum()\n",
    "views=view_group.pivot(index='Month',columns='Region',values='Views')\n",
    "views=views.iloc[0:7,]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "users_per_month = pd.read_excel('final_report.xlsx',sheet_name='users_per_month')\n",
    "sessions_report = pd.read_excel('final_report.xlsx',sheet_name='sessions_report')\n",
    "view_report = pd.read_excel('final_report.xlsx',sheet_name='pageviews_report')\n",
    "users_per_year = pd.read_excel('final_report.xlsx',sheet_name='users_per_year')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "users_per_month.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "users_per_year"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from datetime import datetime\n",
    "\n",
    "users_per_month['Unnamed: 0'] = pd.to_datetime(users_per_month['Unnamed: 0']).dt.strftime('%b %Y')\n",
    "sessions_report['Unnamed: 0'] = pd.to_datetime(sessions_report['Unnamed: 0']).dt.strftime('%b %Y')\n",
    "view_report['Unnamed: 0']= pd.to_datetime(view_report['Unnamed: 0']).dt.strftime('%b %Y')\n",
    "users_per_year['Month'] = pd.to_datetime(users_per_year['Month']).dt.strftime('%b %Y')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "users_per_month"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "users_per_month.set_index('Unnamed: 0',inplace=True)\n",
    "sessions_report.set_index('Unnamed: 0',inplace=True)\n",
    "view_report.set_index('Unnamed: 0',inplace=True)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "users = users[users_per_month.columns]\n",
    "sessions=sessions[sessions_report.columns]\n",
    "views = views[view_report.columns]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "months = {\n",
    "    1: 'Jan 2024',\n",
    "    2: 'Feb 2024',\n",
    "    3: 'Mar 2024',\n",
    "    4: 'Apr 2024',\n",
    "    5: 'May 2024',\n",
    "    6: 'Jun 2024',\n",
    "    7: 'Jul 2024'\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "users.index = users.index.map(months)\n",
    "sessions.index = sessions.index.map(months)\n",
    "views.index = views.index.map(months)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "users_per_month = pd.concat([users_per_month,users])\n",
    "sessions_report = pd.concat([sessions_report,users])\n",
    "view_report = pd.concat([view_report,users])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "users"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sessions_report"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "with pd.ExcelWriter('KOAA_Final_Report.xlsx') as w:\n",
    "    users_per_month.to_excel(w,sheet_name='users_per_month', index = True)\n",
    "    sessions_report.to_excel(w,sheet_name='session_report', index = True)\n",
    "    view_report.to_excel(w, sheet_name='pageviews_report', index = True)\n",
    "    users_per_year.to_excel(w, sheet_name='users_per_year', index = True)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "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.11.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}