# Data Merging Guide

Dataset is too large to merge using Excel due to size constraints, here are alternative approaches to manage and merge the data efficiently:

***

## **Option 1: Merge Using Power BI**

Power BI can handle large datasets and is ideal for merging multiple files.

1. **Load Data**:
   * Open Power BI Desktop.
   * Load all five CSV files into Power BI by navigating to _Home > Get Data > Text/CSV_.
2. **Transform Data**:
   * Go to the _Power Query Editor_.
   * Use the _Append Queries_ option to combine the files into one unified table.
   * Ensure columns align correctly across all datasets.
3. **Process Data**:
   * Perform preliminary preprocessing (e.g., removing duplicates, renaming columns).
   * Save the merged table as a single file using _Export Data_ or save it in a database for further analysis.

***

## **Option 2: Merge Using Python**

Python can handle large datasets and merge them efficiently without memory issues.

1.  Install necessary libraries:

    ```bash
    pip install pandas  
    ```
2.  Use the following script:

    ```python
    import pandas as pd  
    import glob  

    # Define the path to your CSV files
    path = "path_to_your_csv_files/"
    all_files = glob.glob(path + "*.csv")

    # Read and concatenate all CSV files
    data_frames = [pd.read_csv(file) for file in all_files]
    merged_data = pd.concat(data_frames, ignore_index=True)

    # Save the merged dataset
    merged_data.to_csv("merged_pickup_data.csv", index=False)
    ```

    This will create a unified dataset as a CSV file for further processing.

***

## **Option 3: Use a Database (Recommended for Scalability)**

Load the data into a database like MySQL, PostgreSQL, or SQLite, and merge the files using SQL queries.

1. **Import Files into the Database**: Use bulk import functionality of your database for each CSV file.
2.  **Merge Using SQL**:

    ```sql
    CREATE TABLE merged_data AS
    SELECT * FROM city1
    UNION ALL
    SELECT * FROM city2
    UNION ALL
    SELECT * FROM city3
    UNION ALL
    SELECT * FROM city4
    UNION ALL
    SELECT * FROM city5;
    ```
3. Export the merged dataset for further processing.

***

## **Option 4: Divide and Conquer with Excel**

If you must use Excel:

1. Split the data into manageable chunks for each file (e.g., by filtering rows).
2. Use Power Query to process chunks separately, then append the results incrementally.
3. Save each processed file as a part of the final dataset.

***

#### Recommendation

For your scenario, **Power BI** or **Python** would be the easiest and most efficient. If you’re limited to Excel, consider moving to Power Query or splitting the task into smaller steps.
