How to identify the records (order id + product id combination) present in data1 but missing in data2 (specify the number of records missing in your answer)

by

Identifying Missing Records

To identify the records present in data1 but missing in data2 (based on the combination of order id and product id), you can use various programming languages or database queries. Here, we will explain the process with an example in Python. 

    
import pandas as pd

# Assuming data1 and data2 are two DataFrame objects containing the data
data1 = pd.DataFrame({'order_id': [1, 2, 3, 4],
                      'product_id': [101, 102, 103, 104]})

data2 = pd.DataFrame({'order_id': [2, 3, 4],
                      'product_id': [102, 103, 104]})

# Identifying missing records
missing_records = data1[~data1.apply(tuple, 1).isin(data2.apply(tuple, 1))]

# Getting the count of missing records
num_missing_records = len(missing_records)

print("Missing Records:")
print(missing_records)
print("Number of missing records:", num_missing_records)

In the above Python code, we assume that data1 and data2 are two DataFrame objects, which represent the data containing the order and product information. We use the pandas library to perform the operations.

data1:

order_id product_id
1 101
2 102
3 103
4 104

data2:

order_id product_id
2 102
3 103
4 104

By using the apply function in combination with tuple, we convert each row of both dataframes into tuples for comparison. Then, we use the isin function to check if the tuples from data1 are present in data2. Finally, we use the logical negation operator ~ to retrieve the rows of missing_records.

The missing_records DataFrame will contain the missing records:

order_id product_id
1 101

The num_missing_records variable will give you the number of missing records, which in this case is 1.

Leave a comment