In [1]:
import pandas as pd
from collections.abc import Iterator, Iterable, Generator, Hashable
from collections import defaultdict, namedtuple
import re
import os
import datetime
from sqlalchemy.types import Integer, Text, DateTime, Float, VARCHAR
from sqlalchemy import Table, MetaData, Column
import itertools as it
import pickle
from tools import deco_logging
import numpy as np
# add tools funcs
from tools.func_abc import *
MHS_SHEET_NAME = 'PAR'
SHEET_DF = {}
DEFAULT_DATA_PATH = ('data\\')
EXCEL_DIR = 'data//EPlus_2025OTT Analysis.xlsx'
SHEEF_FOOT_SKIP_NUM = 0
log_ipy = deco_logging(user='jupyter')
In [4]:
arc_col = ['Origin', 'Destination', 'TypeIn', 'TypeOut', 'FltIn', 'FltOut',
'ULDIn', 'ULDOut', 'ALDT', 'AIBT', 'AUST', 'ASPT', 'AMDT', 'AMIT',
'AUOT', 'APUT', 'APLT', 'AUCT', 'AMOT', 'AMPT', 'ASDT', 'ALFT', 'AOBT', 'ATOT']
arc_col_select = ['ID', 'Origin', 'Destination', 'TypeIn', 'TypeOut', 'FltIn', 'FltOut',
'ULDIn', 'ULDOut', 'AMIT']
In [2]:
# df_to_pickle(df=arc_data_df, path_file=DEFAULT_DATA_PATH, file_name='arc.pkl')
# arc_pkl_data_df = pd.read_pickle('data\\arc.pkl')
In [11]:
# pd.to_pickle(obj=arc_pkl_data_df,path='data\\arc.xz', compression='xz')
In [2]:
arc_df = pd.read_pickle(path='data\\arc.xz', compression='xz')
In [5]:
selec_arc_df = arc_df.loc[:, arc_col_select]
selec_arc_df.sample(7)
Out[5]:
In [6]:
selec_arc_df = arc_df.loc[:, arc_col_select]
flyid_uld_df = selec_arc_df.groupby(by=['TypeIn', 'FltIn']).ULDIn.apply(set).apply(len).to_frame()
flyid_uld_df = flyid_uld_df.reset_index()
flyid_uld_df = flyid_uld_df.rename_axis({'ULDIn': 'ULD_NUM'}, axis=1)
flyid_uld_df.sample(2)
# df_to_excel(df=flyid_uld_df, file_name='flyid_uld_con',sheet='flyid_uld_con')
Out[6]:
In [11]:
selec_arc_df = arc_df.loc[:, arc_col_select]
uld_amit_par_df = selec_arc_df.groupby(by=['TypeIn', 'FltIn', 'ULDIn', 'AMIT']).ID.apply(set).apply(len).to_frame()
uld_amit_par_df = uld_amit_par_df.reset_index()
uld_amit_par_df = uld_amit_par_df.rename_axis({'ID': 'PAR_NUM'}, axis=1)
uld_amit_par_df = pd.merge(left=uld_amit_par_df, right=flyid_uld_df, on=['TypeIn', 'FltIn'], how='left')
uld_amit_par_sum_df = uld_amit_par_df.groupby(by=['TypeIn', 'FltIn']).PAR_NUM.sum().to_frame()
uld_amit_par_sum_df = uld_amit_par_sum_df.rename_axis({'PAR_NUM': 'PAR_SUM'}, axis=1)
uld_amit_par_sum_df = uld_amit_par_sum_df.reset_index()
uld_amit_par_df = pd.merge(left=uld_amit_par_df, right=uld_amit_par_sum_df, on=['TypeIn', 'FltIn'], how='left')
uld_amit_par_df.query("FltIn=='INF802'").head(15)
# pd.pivot_table(data=uld_df.head(14), index=['FltIn'], columns=['ID'], aggfunc=np.sum)
# df_to_excel(df=uld_amit_par_df, file_name='arc_uld_amit_par_con',sheet='arc_uld_amit_par_con')
Out[11]: