Hedaro

Search

SearchSearch

Data Scientist - The Package Detectives

Sep 02, 2024, 7 min read

Note

Description:

Hey there, data scientist! Have you ever wondered what happens when packages go missing in transit? A logistics company is facing this exact issue, with a significant number of packages vanishing without a trace. The company has collected data on its shipments, but needs your expertise to unravel the mystery. Can you use your Pandas skills to help the company identify the root cause of the problem and recover the missing packages?

Tasks:

  • Package Profiling: Create a summary of package shipments by region, including the number of packages sent, received, and missing.
  • Route Analysis: Identify the most common routes where packages go missing, and calculate the average distance for these routes.
  • Carrier Comparison: Compare the performance of different carriers, including their package loss rates and average delivery times.
# import libraries
import pandas as pd
import numpy as np
import sys
 
print('Python version ' + sys.version)
print('Pandas version ' + pd.__version__)
print('Numpy version ' + np.__version__)
Python version 3.11.7 | packaged by Anaconda, Inc. | (main, Dec 15 2023, 18:05:47) [MSC v.1916 64 bit (AMD64)]
Pandas version 2.2.1
Numpy version 1.26.4

The Data

The dataset contains information about packages shipped by a logistics company, including the package ID, region, carrier, shipment and delivery dates, route ID, and a ā€œmissingā€ column indicating the packages missing in each destination city. The dataset also includes a separate table with route information, including origin and destination cities, and distance.

Columns:

packages:

  • package_id (int): Unique package identifier
  • route_id (int): Foreign key from the routes dataset
  • region (str): Region where package was shipped from
  • carrier (str): Carrier responsible for shipment
  • shipment_date (datetime): Date package was shipped
  • delivery_date (datetime): Date package was delivered (if not missing)
  • missing (bool): Indicator of whether package is missing

routes:

  • route_id (int): Unique route identifier
  • origin (str): City where route originates
  • destination (str): City where route terminates
  • distance (float): Distance of route in miles
# set the seed
np.random.seed(0)
 
packages = pd.DataFrame({
    'package_id': range(1000),
    'region': np.random.choice(['North', 'South', 'East', 'West'], size=1000),
    'carrier': np.random.choice(['CarrierA', 'CarrierB', 'CarrierC'], size=1000),
    'shipment_date': pd.date_range('2022-01-01', periods=1000),
    'route_id': np.random.choice(range(100), size=1000)
})
 
# generate delivery dates
packages['delivery_date'] = packages.apply(lambda x: x['shipment_date'] + pd.Timedelta(days=np.random.randint(1, 60)), axis=1)
 
routes = pd.DataFrame({
    'route_id': range(100),
    'origin': np.random.choice(['CityA', 'CityB', 'CityC'], size=100),
    'destination': np.random.choice(['CityD', 'CityE', 'CityF'], size=100),
    'distance': np.random.uniform(100, 1000, size=100)
})
 
# merge the dataframes
df = packages.merge(routes, on='route_id')
 
# force a pattern to the missing packages
df['missing'] = np.where(
    df['destination'] == 'CityF', 
    np.random.choice([True, False], size=1000, p=[0.15, 0.85]),
    np.where(
        df['destination'] == 'CityD', 
        np.random.choice([True, False], size=1000, p=[0.2, 0.8]),
        np.where(
            df['destination'] == 'CityE', 
            np.random.choice([True, False], size=1000, p=[0.8, 0.2]),
            np.random.choice([True, False], size=1000, p=[0.1, 0.9]) # other
        )
    )
)
 
# make sure delivery date is null if the package is missing
missing = df['missing'] == True
df.loc[missing, 'delivery_date'] = np.nan
 
df.head()
package_idregioncarriershipment_dateroute_iddelivery_dateorigindestinationdistancemissing
00NorthCarrierC2022-01-01752022-01-19CityACityF508.947668False
11WestCarrierA2022-01-02562022-01-22CityACityF984.760182False
22SouthCarrierA2022-01-03432022-01-23CityCCityF850.541203False
33NorthCarrierC2022-01-04242022-01-06CityCCityE357.532844False
44WestCarrierA2022-01-05812022-02-02CityBCityF556.871923False

Letā€™s look at the data types to ensure all of the column are of the correct type.

df.info()
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 1000 entries, 0 to 999
Data columns (total 10 columns):
 #   Column         Non-Null Count  Dtype         
---  ------         --------------  -----         
 0   package_id     1000 non-null   int64         
 1   region         1000 non-null   object        
 2   carrier        1000 non-null   object        
 3   shipment_date  1000 non-null   datetime64[ns]
 4   route_id       1000 non-null   int32         
 5   delivery_date  605 non-null    datetime64[ns]
 6   origin         1000 non-null   object        
 7   destination    1000 non-null   object        
 8   distance       1000 non-null   float64       
 9   missing        1000 non-null   bool          
dtypes: bool(1), datetime64[ns](2), float64(1), int32(1), int64(1), object(4)
memory usage: 67.5+ KB

Package Profiling:

Create a summary of package shipments by region, including the number of packages sent, received, and missing.

Take note that we are able to filter the rows of a column before applying the aggregate function.

lambda x: x[x==True]

  • We find all of the rows where the missing column is equal to True
  • After we have filtered the data, we count the remaining items

This functionality allows us to determine how many packages are missing per region.

# create group object
group = df.groupby('region')
 
# get summary stats
result = group.agg(
     sent=pd.NamedAgg(column="shipment_date", aggfunc="count"),
     received=pd.NamedAgg(column="delivery_date", aggfunc="count"),
     missing=pd.NamedAgg(column="missing", aggfunc=lambda x: x[x==True].count())
)
 
# add percentage missing for clarity
result['pct_missing'] = result['missing'] / result['sent']
result
sentreceivedmissingpct_missing
region
East241156850.352697
North255159960.376471
South2531411120.442688
West2511491020.406375

Based on the results above, we can see that the southern region has the most missing packages, but we should continue analyzing the data before we determine the southern region is the root cause of the missing packages.

Route Analysis:

Identify the most common routes where packages go missing, and calculate the average distance for these routes.

# calculate the days in transit
df['time_in_transit'] = (df['delivery_date'] - df['shipment_date']).dt.days
 
# missing packages
missing = df['missing'] == True
df_missing = df[missing]
 
df_missing.head()
package_idregioncarriershipment_dateroute_iddelivery_dateorigindestinationdistancemissingtime_in_transit
77WestCarrierA2022-01-0818NaTCityBCityE875.626127TrueNaN
99WestCarrierC2022-01-1089NaTCityACityF580.740419TrueNaN
1010SouthCarrierB2022-01-1129NaTCityACityE515.475425TrueNaN
1313WestCarrierC2022-01-1440NaTCityCCityE994.064699TrueNaN
1515NorthCarrierC2022-01-1611NaTCityCCityE238.850000TrueNaN

Now that we have all of the routes that are missing, let us calculate the average distance for these routes.

# create group object
group = df_missing.groupby('route_id')
 
# average distance for these routes
group.mean(numeric_only=True)['distance']
route_id
0     524.497660
1     732.105228
3     272.413912
4     695.956811
5     866.191094
         ...    
93    460.459417
94    469.824161
97    187.122897
98    900.785922
99    155.660305
Name: distance, Length: 85, dtype: float64

Another perspective we can use to tackle this task:

  • Compare the average distance for the routes that went missing and the routes that made it to their final destination.

Can you find a correlation?

# create group object
group = df.groupby('missing')
 
# average distance for these routes
group.mean(numeric_only=True)['distance'].plot.barh();

The routes that went missing do seem to be the longest routes. Does this mean distance is a factor in why the packages are going missing? Maybe, but let us continue exploring.

Carrier Comparison:

Compare the performance of different carriers, including their package loss rates and average delivery times.

# create group object
group = df.groupby('carrier')
 
# average distance for these routes
group.agg(
     loss_rate=pd.NamedAgg(column="missing", aggfunc=lambda x: x[x==True].count() / x.count()),
     avg_delivery_times=pd.NamedAgg(column="time_in_transit", aggfunc="mean")
)
loss_rateavg_delivery_times
carrier
CarrierA0.38575729.101449
CarrierB0.42565630.578680
CarrierC0.37187529.343284

The chart above should tell us if any of the carriers is mainly responsible for he missing packages. Unfortunately, from the data, it does not seem like the carrier has much to do with the missing packages.

Let us see if the destination city has anything to do with these packages that are vanishing mysteriously.

# create group object
group = df.groupby('destination')
 
# average distance for these routes
group.agg(
     loss_rate=pd.NamedAgg(column="missing", aggfunc=lambda x: x[x==True].count() / x.count())
)
loss_rate
destination
CityD0.201439
CityE0.832370
CityF0.135638

Aha! Do you see what I see?

80% of the packages that had a destination to CityE went missingā€¦

This is a significant data point that may help us solve the mistery. Something seems to be happening to packages that are in route to CityE. What could the issue be?

I want to make sure there are no other variables that we need to consider.

If we compare the destination city against the following columns, do we still see the missing packages pattern?

  • region
  • carrier
  • origin
import matplotlib.pyplot as plt
 
# create a grid of charts
fig, axs = plt.subplots(nrows=1, ncols=3, figsize=(15, 5))
 
# plot each column in the pivot table in a separate chart
for ax, col in zip(axs.flatten(), ['region','carrier','origin']):
    df.pivot_table(index='destination', columns=col, values='missing', aggfunc=lambda x: x[x==True].count() / x.count()).plot.bar(ax=ax)
 
# customize the layout
plt.tight_layout()
plt.show()

We can clearly see that regardless of the origin city, the region, or the carrier. Packages in route to CityE have a very high chance of going missing. I would recommend the logistics company immediately stop sending packages to this city and do a thorough investigation to determine the root cause.

Summary

Based on the analysis, it was determined that:

  • Packages that went missing had longer routes on average, suggesting distance might be a factor.
  • Carrier performance did not seem to be a significant factor in missing packages.
  • However, a significant finding was that 80% of packages destined for CityE went missing, suggesting a specific issue with deliveries to that city.

In summary, further analysis showed that this pattern persisted regardless of origin city, region, or carrier, leading to the recommendation for the logistics company to stop sending packages to CityE and investigate the root cause.

Graph View

Backlinks

  • No backlinks found