Find difference (delta) between all columns over multiple indexes using Pandas dataframes

user_5595081 0
Viewed 110

Takes the following for example:

import datetime
import pandas as pd

data = [
    {"date": datetime.date(2020, 1, 1), "product": "product_1", "price": 90, "quantity": 100, "code": "code-1.1"},
    {"date": datetime.date(2020, 1, 1), "product": "product_2", "price": 80, "quantity": 80, "code": "code-2.1"},
    {"date": datetime.date(2020, 1, 1), "product": "product_3", "price": 80, "quantity": 80, "code": "code-3.1"},

    {"date": datetime.date(2020, 1, 2), "product": "product_1", "price": 90, "quantity": 80, "code": "code-1.1"},
    {"date": datetime.date(2020, 1, 2), "product": "product_2", "price": 80, "quantity": 80, "code": "code-2.1"},
    {"date": datetime.date(2020, 1, 2), "product": "product_3", "price": 80, "quantity": 80, "code": "code-3.1"},

    {"date": datetime.date(2020, 1, 3), "product": "product_1", "price": 90, "quantity": 80, "code": "code-1.1"},
    {"date": datetime.date(2020, 1, 3), "product": "product_2", "price": 80, "quantity": 80, "code": "code-2.2"},

    {"date": datetime.date(2020, 1, 4), "product": "product_1", "price": 80, "quantity": 70, "code": "code-1.1"},
    {"date": datetime.date(2020, 1, 4), "product": "product_2", "price": 70, "quantity": 80, "code": "code-2.2"},
    {"date": datetime.date(2020, 1, 4), "product": "product_3", "price": 80, "quantity": 80, "code": "code-3.1"},
]

df = pd.DataFrame(data)
df = df.set_index(["date", "product"])

Want I want to be able to do is produce a difference/delta on each none-index column and only output the changed columns and don't output days where nothing has changed, but also identify when days have been missed and then output the all the columns for that date/product the following day.

An example output would be:

{"date": datetime.date(2020, 1, 1), "product": "product_1", "price": 90, "quantity": 100, "code": "code-1.1"},
{"date": datetime.date(2020, 1, 1), "product": "product_2", "price": 80, "quantity": 80, "code": "code-2.1"},
{"date": datetime.date(2020, 1, 1), "product": "product_3", "price": 80, "quantity": 80, "code": "code-3.1"},
{"date": datetime.date(2020, 1, 2), "product": "product_1", "price": None, "quantity": 80, "code": None},
{"date": datetime.date(2020, 1, 3), "product": "product_2", "price": None, "quantity": None, "code": "code-2.2"},
{"date": datetime.date(2020, 1, 4), "product": "product_1", "price": 80, "quantity": 70, "code": None},
{"date": datetime.date(2020, 1, 4), "product": "product_2", "price": 70, "quantity": None, "code": None},
{"date": datetime.date(2020, 1, 4), "product": "product_3", "price": 80, "quantity": 80, "code": "code-3.1"},

To clarify the above:

For product_1: Date 2020-01-03 is missing due to nothing has changed in the columns (price, quantity) - whereas for the date 2020-01-02, the price is None due to it not changing.

For product_2: Date 2020-01-01 is present due to being the first datapoint, the code changes on 2020-01-03, and the price is updated on the 2020-01-04.

For product_3: Date 2020-01-01 is present due to being the first datapoint, date 2020-01-04 is present due to the previous date (2020-01-03) we didn't have any data for that day.

I have tried iterating on the dataframe but fear that this will be slow scaling up. I feel something like pct_change would work, but from reading this doesn't work on multi-indexes.

Also as answers have reflected diff would work on int columns, however I ideally need this to work on also str columns as well.

data-science pandas dataframe python-3.x python
1 Answers

For a single product

diffs = df.diff()
df[diffs == 0] = None
df[~(diffs == 0).all(axis=1)]

        price   quantity
date    product     
2020-01-01  product_1   90.0    100.0
2020-01-02  product_1   NaN     80.0
2020-01-04  product_1   80.0    70.0

For multiple products

def show_diffs(df):
    diffs = df.diff()
    df[diffs == 0] = None
    return df[~(diffs == 0).all(axis=1)]

df.groupby('product').apply(show_diffs).sort_values('date')  

                                    price   quantity
product     date        product     
product_1   2020-01-01  product_1   90.0    100.0
product_2   2020-01-01  product_2   80.0    80.0
product_1   2020-01-02  product_1   NaN     80.0
            2020-01-04  product_1   80.0    70.0
product_2   2020-01-04  product_2   70.0    NaN
user_76485780
Related