# Pickup Data Preprocessing

Here is a detailed description of all the steps in the Python data preprocessing pipeline for the **pickup dataset**:

***

## **Step-by-Step Description**

**1. Import Libraries**

**Description:**\
We begin by importing necessary Python libraries:

* `pandas` for data manipulation.
* `numpy` for numerical computations.
* `datetime` for handling date and time formats.

These libraries form the foundation for handling, transforming, and analyzing data.

***

**2. Load and Merge Datasets**

**Description:**\
The dataset is spread across five CSV files for different cities. We:

1. **Load each file** into a pandas DataFrame.
2. **Add a `city` column** to track the origin of each record (if not already present).
3. **Merge all files** into a single DataFrame using `pd.concat()` to enable unified processing.

***

**3. Handle Missing and Inconsistent Values**

**Description:**\
Missing or inconsistent data can distort analyses. Here’s how we address them:

1. **Identify missing values** using `.isnull().sum()` to assess data health.
2. **Fill categorical data** (`aoi_type`, `city`) with placeholders like "Unknown".
3. **Replace missing numerical values** like `lng` and `lat` with mean values as approximations.
4. **Drop rows with critical missing data** in columns such as `accept_time` and `pickup_time`.

***

**4. Remove Duplicate Records**

**Description:**\
Duplicates can skew insights. Using the unique `package_id`, we:

1. Check for duplicate rows.
2. Retain only one record for each `package_id` using `drop_duplicates()`.

This ensures data integrity.

***

**5. Convert Time Columns to Datetime Format**

**Description:**\
Time-based calculations require a consistent format. We:

1. Convert time columns (`accept_time`, `pickup_time`, etc.) to Python’s `datetime` format.
2. Use `errors='coerce'` to handle invalid entries, setting them as `NaT` (Not a Time).
3. Investigate columns with invalid entries for corrective actions.

***

**6. Calculate Derived Features**

**Description:**\
Derived features add analytical depth and predictive value:

1. **Time Window Duration:**\
   Compute the delivery window length: time\_window\_duration=time\_window\_end−time\_window\_start\text{time\\\_window\\\_duration} = \text{time\\\_window\\\_end} - \text{time\\\_window\\\_start}
2. **Task Duration:**\
   Calculate the time from task acceptance to pickup: task\_duration=pickup\_time−accept\_time\text{task\\\_duration} = \text{pickup\\\_time} - \text{accept\\\_time}
3. **Distance Between Acceptance and Pickup:**\
   Using the Euclidean formula: distance=(pickup\_gps\_lng−accept\_gps\_lng)2+(pickup\_gps\_lat−accept\_gps\_lat)2\text{distance} = \sqrt{(\text{pickup\\\_gps\\\_lng} - \text{accept\\\_gps\\\_lng})^2 + (\text{pickup\\\_gps\\\_lat} - \text{accept\\\_gps\\\_lat})^2}

***

**7. Identify and Filter Outliers**

**Description:**\
Outliers can result from errors or unusual occurrences. To handle them:

1. Calculate the **interquartile range (IQR)** for `task_duration`.
2. Define boundaries:
   * Lower Bound = Q1−1.5×IQRQ1 - 1.5 \times \text{IQR}
   * Upper Bound = Q3+1.5×IQRQ3 + 1.5 \times \text{IQR}
3. Filter out data points falling outside these bounds.

***

**8. Save the Cleaned Dataset**

**Description:**\
The final dataset is saved as `cleaned_pickup_data.csv`. This ensures all preprocessing efforts are preserved for subsequent steps.

***

#### **Why Each Step is Important**

1. **Merging datasets** consolidates fragmented data for efficient handling.
2. **Handling missing values** ensures no essential analysis is hindered by gaps.
3. **Removing duplicates** avoids inflating insights with repeated data.
4. **Datetime conversion** prepares the data for temporal analysis.
5. **Derived features** enhance the predictive and analytical power of the dataset.
6. **Outlier removal** improves data quality by addressing anomalies.

This systematic approach ensures a robust, clean, and insightful dataset ready for further analysis or machine learning applications.



## Python-Based Preprocessing

***

#### **Python Code for Data Preprocessing**

**1. Import Libraries**

```python
import pandas as pd
import numpy as np
from datetime import datetime
```

***

**2. Load and Merge Datasets**

```python
# Load datasets for all cities
city1 = pd.read_csv("city1_pickup_data.csv")
city2 = pd.read_csv("city2_pickup_data.csv")
city3 = pd.read_csv("city3_pickup_data.csv")
city4 = pd.read_csv("city4_pickup_data.csv")
city5 = pd.read_csv("city5_pickup_data.csv")

# Add a city column (if missing)
city1['city'] = 'City1'
city2['city'] = 'City2'
city3['city'] = 'City3'
city4['city'] = 'City4'
city5['city'] = 'City5'

# Merge all datasets
merged_data = pd.concat([city1, city2, city3, city4, city5], ignore_index=True)
```

***

**3. Handle Missing and Inconsistent Values**

```python
# Check for missing values
print(merged_data.isnull().sum())

# Fill missing values for categorical columns
merged_data['aoi_type'].fillna('Unknown', inplace=True)
merged_data['city'].fillna('Unknown City', inplace=True)

# Replace missing coordinates with regional averages
merged_data['lng'].fillna(merged_data['lng'].mean(), inplace=True)
merged_data['lat'].fillna(merged_data['lat'].mean(), inplace=True)

# Drop rows with critical missing timestamps
merged_data.dropna(subset=['accept_time', 'pickup_time'], inplace=True)
```

***

**4. Remove Duplicate Records**

```python
# Remove duplicates based on package_id
merged_data.drop_duplicates(subset='package_id', inplace=True)
```

***

**5. Convert Time Columns to Datetime**

```python
# Convert time columns to datetime format
time_columns = ['accept_time', 'accept_gps_time', 'pickup_time', 'pickup_gps_time', 'time_window_start', 'time_window_end', 'ds']

for col in time_columns:
    merged_data[col] = pd.to_datetime(merged_data[col], errors='coerce')

# Check for invalid dates
print(merged_data[time_columns].isnull().sum())
```

***

**6. Calculate Derived Features**

1.  **Time Window Duration**:

    ```python
    merged_data['time_window_duration'] = (merged_data['time_window_end'] - merged_data['time_window_start']).dt.total_seconds()
    ```
2.  **Acceptance to Pickup Duration**:

    ```python
    merged_data['task_duration'] = (merged_data['pickup_time'] - merged_data['accept_time']).dt.total_seconds()
    ```
3.  **Distance Between Acceptance and Pickup**:

    ```python
    # Define a function for Euclidean distance
    def calculate_distance(lng1, lat1, lng2, lat2):
        return np.sqrt((lng2 - lng1)**2 + (lat2 - lat1)**2)

    merged_data['distance'] = calculate_distance(
        merged_data['accept_gps_lng'], merged_data['accept_gps_lat'],
        merged_data['pickup_gps_lng'], merged_data['pickup_gps_lat']
    )
    ```

***

**7. Identify and Filter Outliers**

```python
# Remove outliers based on task_duration (e.g., duration should be reasonable)
q1 = merged_data['task_duration'].quantile(0.25)
q3 = merged_data['task_duration'].quantile(0.75)
iqr = q3 - q1
lower_bound = q1 - 1.5 * iqr
upper_bound = q3 + 1.5 * iqr

merged_data = merged_data[(merged_data['task_duration'] >= lower_bound) & (merged_data['task_duration'] <= upper_bound)]
```

***

**8. Save the Cleaned Dataset**

```python
# Save the preprocessed dataset
merged_data.to_csv("cleaned_pickup_data.csv", index=False)
```

***

#### **Summary of Steps**

1. **Load and merge** all city datasets into one.
2. **Handle missing values** by replacing or removing them.
3. **Remove duplicates** to ensure data integrity.
4. Convert time columns to the **datetime format**.
5. **Calculate derived features**, including durations and distances.
6. **Filter outliers** to ensure quality.
7. Save the **cleaned dataset** for further analysis.

## Excel and BI Data Preprocessing

Here’s a detailed explanation of how to preprocess the **Pickup Dataset** using **Excel** and **Power BI**, broken down:

***

#### **Steps for Preprocessing in Excel**

**Step 1: Consolidate Files**

1. Open Excel.
2. Create a **Master Workbook**.
3. Import all five CSV files into separate sheets in this workbook.
   * Go to `Data > Get Data > From Text/CSV`.
   * Load each file into separate tabs.
4. Add a new column `City` in each sheet (if not present), specifying the city name corresponding to the file.
5. Use **Copy-Paste** or **Power Query** (from `Data > Get Data > Combine Queries`) to consolidate the data from all sheets into a single worksheet.

***

**Step 2: Handle Missing Values**

1. Use **Conditional Formatting** (`Home > Conditional Formatting > Highlight Cells Rules > Blanks`) to identify blank cells in critical columns (`accept_time`, `pickup_time`).
2. Address missing values:
   * **Numerical Columns** (e.g., `lng`, `lat`): Fill with the column average using `=AVERAGE(range)`.
   * **Categorical Columns** (e.g., `aoi_type`): Fill with "Unknown".
3. Remove rows where critical columns like `accept_time` or `pickup_time` are missing.

***

**Step 3: Remove Duplicate Records**

1. Highlight the `package_id` column.
2. Go to `Data > Remove Duplicates` and select all columns to ensure complete row duplication is considered.

***

**Step 4: Convert Time Columns**

1. Ensure all time columns (`accept_time`, `pickup_time`) are formatted correctly:
   * Select the column.
   * Go to `Home > Number > Custom` and choose a date-time format like `dd/mm/yyyy hh:mm:ss`.
2. Investigate rows with incorrect or incomplete time data manually.

***

**Step 5: Calculate Derived Features**

1. **Time Window Duration:**
   * Add a new column `Time Window Duration`.
   * Use `=time_window_end - time_window_start` to calculate the duration.
2. **Task Duration:**
   * Add a new column `Task Duration`.
   * Use `=pickup_time - accept_time`.
3. **Distance Between Acceptance and Pickup:**
   * Add a new column `Distance`.
   * Use the formula: =SQRT((pickup\_gps\_lng−accept\_gps\_lng)2+(pickup\_gps\_lat−accept\_gps\_lat)2)=SQRT((pickup\\\_gps\\\_lng - accept\\\_gps\\\_lng)^2 + (pickup\\\_gps\\\_lat - accept\\\_gps\\\_lat)^2)

***

**Step 6: Identify and Filter Outliers**

1. Calculate **Q1 (25th percentile)** and **Q3 (75th percentile)** for `Task Duration` using the `PERCENTILE.INC` function.
2. Compute the **IQR (Interquartile Range)**: IQR=Q3−Q1IQR = Q3 - Q1
3. Identify outliers:
   * Lower Bound = Q1−1.5×IQRQ1 - 1.5 \times IQR
   * Upper Bound = Q3+1.5×IQRQ3 + 1.5 \times IQR
4. Filter out rows where `Task Duration` falls outside these bounds.

***

**Step 7: Save the Cleaned Dataset**

1. Save the final preprocessed data as `cleaned_pickup_data.xlsx`.

***

#### **Steps for Preprocessing in Power BI**

**Step 1: Import and Merge Data**

1. Open Power BI Desktop.
2. Go to `Home > Get Data > Text/CSV` and load each city’s dataset.
3. Use the **Power Query Editor**:
   * Go to `Transform Data`.
   * Use `Append Queries` to combine all datasets into one.
   * Add a `City` column to distinguish the origin.

***

**Step 2: Handle Missing Values**

1. In Power Query, select `Transform > Replace Values`:
   * Replace null values in numerical columns with the column mean (`Transform > Statistics > Average`).
   * Replace null values in categorical columns with "Unknown".
2. Filter out rows with critical missing data in `accept_time` or `pickup_time`.

***

**Step 3: Remove Duplicates**

1. Select the `package_id` column in Power Query.
2. Use `Remove Duplicates` from the toolbar.

***

**Step 4: Transform Time Columns**

1. Convert columns like `accept_time` and `pickup_time` to date-time format using `Transform > Data Type > Date/Time`.
2. Create custom columns to calculate derived metrics (e.g., task duration, time window duration).

***

**Step 5: Add Derived Features**

1. **Time Window Duration:**
   *   Use `Add Column > Custom Column` with:

       ```
       [time_window_end] - [time_window_start]
       ```
2. **Task Duration:**
   *   Add a custom column:

       ```
       [pickup_time] - [accept_time]
       ```
3. **Distance Between Acceptance and Pickup:**
   *   Use:

       ```
       sqrt(pow([pickup_gps_lng] - [accept_gps_lng], 2) + pow([pickup_gps_lat] - [accept_gps_lat], 2))
       ```

***

**Step 6: Identify Outliers**

1. Create a measure for **Q1**, **Q3**, and **IQR** using DAX functions.
2. Use DAX filters to exclude rows outside the lower and upper bounds.

***

**Step 7: Export Cleaned Dataset**

1. Once preprocessing is complete, export the dataset from Power BI:
   * Go to `Home > Export > Export Data`.

***
