notebooks/maersk_temp_merge_after_2023_02_16.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true,
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "from datetime import datetime\n",
    "import ast"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [
    "# This notebook is temporary. It is used to merge old dataframes with the new dataframe\n",
    "# Main challenge is to get the older dataframe format in the new combined dataframe format\n",
    "# This notebook will be deleted once every old dataframe is merged\n",
    "# Around 1-11 the csv's are moved location"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [
    "date_csv = '2023-04-19'\n",
    "old_df = pd.read_csv(f'../data/maersk_daily/csv_runs/connections_{date_csv }.csv',index_col=0)"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [
    "display(old_df)"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [
    "new_list = []\n",
    "\n",
    "for index, row in old_df.iterrows():\n",
    "    origin = row['Origin']\n",
    "    destination =  row['Destination']\n",
    "    departure_time =  row['LocalEstimatedDepartureTime']\n",
    "    arrival_time = row['LocalEstimatedArrivalTime']\n",
    "    est_transit_days = row['EstimatedTotalTransitTimeDays']\n",
    "    ports = row['Ports']\n",
    "    vessels = row['Vessels']\n",
    "    dates = row['Dates']\n",
    "    scraping_date = row['ScrapingDate']\n",
    "    scraping_site = row['ScrapingSite']\n",
    "\n",
    "    if index == 0:\n",
    "        print()\n",
    "        print('voor processen')\n",
    "        print(origin)\n",
    "        print(destination)\n",
    "        print(departure_time)\n",
    "        print(arrival_time)\n",
    "        print(ports)\n",
    "        print(vessels)\n",
    "        print(dates)\n",
    "        print('einde')\n",
    "        print()\n",
    "\n",
    "    origin_name = origin\n",
    "    destination_name = destination\n",
    "\n",
    "    # Remove the \"datetime.datetime\" prefix from the string representation\n",
    "    dates = dates.replace(\"datetime.datetime\", \"\")\n",
    "\n",
    "    # Replace opening and closing brackets to create a valid list literal\n",
    "    dates = \"[\" + dates[1:-1] + \"]\"\n",
    "\n",
    "    # Use eval() to evaluate the string as a Python expression\n",
    "    dates = eval(dates)\n",
    "\n",
    "    # Convert the tuples to datetime objects\n",
    "    datetime_objects = [datetime(*date) for date in dates]\n",
    "\n",
    "    # Convert the datetime objects to dates in the desired format\n",
    "    new_dates = [date.strftime(\"%Y-%m-%d %H:%M:%S\") for date in datetime_objects]\n",
    "\n",
    "    ports = ast.literal_eval(ports)\n",
    "\n",
    "    if len(new_dates) < len(ports)*2-2:\n",
    "        print()\n",
    "        print('Warning at row ', index, 'Not enough dates for the amount of ports')\n",
    "        print('dates', new_dates)\n",
    "        print('ports', ports)\n",
    "        print('appending empty dates')\n",
    "        inserted_dates = 0\n",
    "        for i in range(len(ports)-2):\n",
    "            new_dates.insert(i+2+inserted_dates,'')\n",
    "            inserted_dates += 1\n",
    "        print(new_dates)\n",
    "\n",
    "\n",
    "    vessels = ast.literal_eval(vessels)\n",
    "    new_vessels_list = []\n",
    "    for vessel in vessels:\n",
    "        for i in range(len(vessel)):\n",
    "            if vessel[i] == '-':\n",
    "                vessel[i] = ''\n",
    "            elif vessel[i] == 'unknown':\n",
    "                vessel[i] = ''\n",
    "        if len(vessel) < 6:\n",
    "            for i in range(6-len(vessel)):\n",
    "                vessel.append('')\n",
    "        new_vessels_list.append({'vessel_name': vessel[0], 'imo': vessel[1], 'flag': vessel[3], 'build_year_ship': vessel[5],'service': vessel[2], 'callsign': vessel[4]})\n",
    "\n",
    "    if len(new_vessels_list) < len(ports)-1:\n",
    "        print()\n",
    "        print('Warning at row ', index, 'Not enough vessels for the amount of ports')\n",
    "        print('vessels', new_vessels_list)\n",
    "        print('ports', ports)\n",
    "        print('appending barge')\n",
    "        for i in range(len(ports) - len(new_vessels_list) -1):\n",
    "            new_vessels_list.append({'vessel_name': 'barge', 'imo': '', 'flag': '', 'build_year_ship': '','service': '', 'callsign': ''})\n",
    "        print('new_vessels',new_vessels_list)\n",
    "        print()\n",
    "\n",
    "    legs = {}\n",
    "    for leg in range(len(ports)-1):\n",
    "        legs[f'{leg+1}'] = {'OriginName': ports[leg], 'DestinationName': ports[leg+1],'Vessel': new_vessels_list[leg],\n",
    "                                                        'EstimatedDepartureTime': new_dates[leg*2], 'EstimatedArrivalTime': new_dates[leg*2+1]}\n",
    "\n",
    "    est_transit_hours = ''\n",
    "    co2= ''\n",
    "    distance_meter = ''\n",
    "    cutoffs = ''\n",
    "    number_legs = ''\n",
    "\n",
    "    new_list.append([scraping_date,scraping_site,origin,destination,origin_name,destination_name,departure_time,arrival_time,est_transit_days,est_transit_hours,co2,distance_meter,cutoffs,number_legs,legs])"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [
    "columns = ['ScrapingDate','ScrapingSite','Origin','Destination','OriginName','DestinationName','EstimatedDepartureTime','EstimatedArrivalTime','EstimatedTotalTransitTimeDays','EstimatedTotalTransitTimeHours','TotalCO2EmissionsKg','TotalDistanceMeters','CutOffs','NumberOfLegs','Legs']\n",
    "new_df = pd.DataFrame(new_list, columns=columns)\n",
    "display(new_df)"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [
    "combined_df = pd.read_csv(f'../data/maersk_daily/combined/combined3.csv',index_col=0)\n",
    "new_combined_df = pd.concat([combined_df,new_df])\n",
    "new_combined_df = new_combined_df.reset_index(drop=True)\n",
    "new_combined_df.head()"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [
    "new_combined_df.to_csv(f\"../data/maersk_daily/combined/combined3.csv\")"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}