I am aware of many Questions related to the comparrison of Pandas Dataframes. I hope my question is no dubilcate but i could not find the ansewer.
As a preface, i am not sure if my way to tackle the problem is actually advisable so i am open to suggestions as well.
The Problem: I get Data on a regular basis and it gets loaded into a Datawarehouse. I would like to be able to quickly check the content of the data in terms of its consitency when compared to older data deliveries. For now i want to just check the original Flat/CSV file. The data looks like this:
Data from: 2022-08-01
|Country|Vendor | ID | Desc |Price| Deilevery_Status |Product_group|
|-------|-------|-----|----------|-----|------------------|-------------|
| GB | Nike |1234 |White/Red | 65 | 4-5 Days | Sneaker |
| ... | ... |... |... | ... | ... | ... |
In this Dataset for a given Product ID (ID in the table above) ther can be more vendors. Think Adidas also has a Product with number 1234. So only the Combination ID+Vendor+Cuntry is unique.
Each Month/Week i get new Data But The Price and the Delivery Status can change. The Product group can not change. I want to detect unusaly Price movements, calculate aggregates of procuctgroups and analyse them over time and track changes in the delivery Status etc.... So it is basically Timeseries Analysis.
Furthermore, it is not sure that i get data for each ID every Month,Id´s can fade out, new ID´s can come up and i want to track this as well.
I am not how to combine the Data from individual Deliveries into a meaningfull Datastructure for Pandas. I was considerning to create a DataFrame like this, where i create a new Price Column for each Price:
Data from: 2022-08-01 + Data from 2022-09-01
|Country|Vendor | ID | Desc |Price_09| Deilevery_Status_09 |Price_08| Deilevery_Status_08 |Product_group|
|-------|-------|-----|----------|--------|---------------------|--------|---------------------|--------------
| GB | Nike |1234 |White/Red | 65 | 4-5 Days | 69 | 10 Days | Sneaker |
| ... | ... .....
In this case i do not really understand how to join/ merge the data on multiple columns as i would have to Picewise join the Data based on identical VEndor ID and Country Columns.
Or would it be a better idea to Create my Data frame like this, where each Observation gets its own row:
Data from: 2022-08-01 + Data from: 2022-09-01
|PriceData |Country|Vendor | ID | Desc |Price| Deilevery_Status |Product_group|
|----------|-------|-------|-----|----------|-----|------------------|--------------|
|2022-08-01| GB | Nike |1234 |White/Red | 65 | 4-5 Days | Sneaker |
|2022-08-01| ... | ... |... |... | ... | ... | ... |
|2022-09-01| GB | Nike |1234 |White/Red | 69 | 10 Days | Sneaker |
In this case there is alot of redundant data. OR is there a concept of Normalization (3NF) for data in Pandas?
My Goal in the end is to calculate various aggregate Statistic and detect unusual Price/DeleiveryStauts movements in individual Groupings. The "Time-Series" will not have more then 4 sucessive month.... for now
Thank you in advance