<jupyter_start><jupyter_text>Collect Data<jupyter_code>df = pd.read_excel('Raw_data.xlsx')
tiers = pd.read_excel('Tiers.xlsx')<jupyter_output><empty_output><jupyter_text>Assess Data<jupyter_code>Tier1 = tiers[tiers['DSC_TIER'] == 'TIER1'].index
Tier2 = tiers[tiers['DSC_TIER'] == 'TIER2'].index
Tier3 = tiers[tiers['DSC_TIER'] == 'TIER3'].index
Tier4 = tiers[tiers['DSC_TIER'] == 'TIER4'].index
T1 = []
T2 = []
T3 = []
T4 = []
for i in Tier1:
    T1.append(tiers['DSC_CITY'][i])
for i in Tier2:
    T2.append(tiers['DSC_CITY'][i])
for i in Tier3:
    T3.append(tiers['DSC_CITY'][i])
for i in Tier4:
    T4.append(tiers['DSC_CITY'][i])
def to_tier(row):
    if row['Region'] in T1:
        return 'Tier1'
    elif row['Region'] in T2:
        return 'Tier2'
    elif row['Region'] in T3:
        return 'Tier3'
    elif row['Region'] in T4:
        return 'Tier4'
region = tiers['DSC_CITY'].unique()
city = tiers['DSC_REGION'].unique()
df.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
RangeIndex: 304392 entries, 0 to 304391
Data columns (total 42 columns):
Tracking #                  304392 non-null object
Hub                         304392 non-null object
Aging                       303573 non-null float64
Schedule date               23182 non-null float64
Location                    228 non-null object
# of attempts               304392 non-null int64
Client name                 234249 non-null object
Client last name            287923 non-null object
Phone                       287608 non-null object
Street                      287595 non-null object
Region                      267145 non-null object
City                        253853 non-null object
Locality                    0 non-null float64
Post-code                   3529 non-null object
Amount to Collect           223567 non-null float64
Collected amount            221899 non-null float64
Notes                       48061 non-null object
Service Code                30[...]<jupyter_text>Clean<jupyter_code># keep only reasons with first 4 letters and ignore nulls
reason = list(df[df['Last failure reason'].notnull()].index)
for i in reason:
    df['Last failure reason'][i] = df['Last failure reason'][i][:4]
df['Last failure reason'].tail()
for i in range(len(df)):
    if df['Region'][i] not in region:
        df['Region'] = df['Region'].drop(i)
df['Region'].nunique()
df['Tier'] = df.apply(lambda row: to_tier(row), axis = 1)<jupyter_output><empty_output><jupyter_text>Answers  1- Find how many orders have been Delivered or Completed by each services provider at each Tier.<jupyter_code>df[df['Package Status'] == 'COMPLETED'].groupby(['Hub', 'Tier'])['Tier'].count()<jupyter_output><empty_output><jupyter_text>2- Find how many Jumia Express orders have been delivered   within and out of Delivery Timeline at each Tier  I couldn't resolve the `Delivery date ` column values  3- Find how many orders Pending at each hub more than 5 days without attempt   and what is the total number of packages for 3PL & CF Teams   (any hub contains fleet word consider as CF)<jupyter_code>df[df['Package Status'] == 'PENDING'].groupby(['Tier', '# of attempts'])['Tier'].count()<jupyter_output><empty_output><jupyter_text>4 Find how many orders not delivered yet and passed Our Delivery Timeline at each hub I couldn't resolve the `Delivery date ` column values  5- Find the highest hub canceled packages by hubs with CA35 & CA52 reasons<jupyter_code>df[df['Last failure reason'] == 'CA35'].groupby(['Hub', 'Package Status'])['Hub'].max()
df[df['Last failure reason'] == 'CA52'].groupby(['Hub', 'Package Status'])['Hub'].count()<jupyter_output><empty_output><jupyter_text>6- Suppose the order was Jumia express Tier1 and created before 2:00 PM   we promised  the customer to delivery the packages Next Day,    if not one day after based on the previous scenario,   find how many orders placed before 2:00 PM and delivered within our promise. I couldn't resolve the `Delivery date ` column values  7.1 - use the raw data to share with us a new interesting insights, after applying Data analysis process.  7.2 - list data analysis process should be used by any data analyst to perform his work.     1. Pose questions    2. Collect data    3. Clean data    4. Explore data    5. Draw conclusions    6. Communicate results<jupyter_code>#cols = ['Tracking #', 'Hub', 'Location', '# of attempts', 'Client name', 'Client last name', 'Amount to Collect',
#        'Collected amount', 'Service Code', 'Service Code Description',
#        'Payment Method', 'Package Status', 'Last failure reason',
#        'Order number', 'Shipment service', 'Airway bill number', 'Refunded', 'Delayed']

#dates = ['Schedule date', 'Operation Date',
#         'Collected date #', 'Delivered date #',
#         'Package date #', 'Service date #', 'Order date'
        
#        ]
#botcol = ['Locality', 'Notes', 'Phone', 'Post-code', 'Street', 'Aging',
#          'Movable unit #', 'Runsheet name', 'Runsheet driver', 'Attempt user name', 'Company']

#tier = ['Region', 'City']

#packcol = ['Weight', 'Length', 'Width', 'Height']<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.12531769305962853, 'fraction_numerical': 0.03577712609970674, 'mean_word_length': 3.42560553633218, 'pattern_counts': {'":': 0, '<': 29, '<?xml version=': 0, '>': 29, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '7', 'dir': 'jupyter-structured-clean-dedup', 'id': '513463', 'license': 'MIT', 'n_tokens_mistral': 1690, 'n_tokens_neox': 1574, 'n_words': 521, 'path': 'jumia analysis.ipynb', 'repo_name': 'AhmedEweed/jumia-analysis', 'size': '5115'}