I have this dataframe which shows the kind of gems, shapes, sizes, quality, date of purchase, and price paid.
| Gem | shape | size | quality | date | price |
|---|---|---|---|---|---|
| Ruby | Oval | 2 | 1 | 01-01-2021 | 2500 |
| Emerald | Heart | 3 | 2 | 09-06-2020 | 2500 |
| Ruby | Oval | 2 | 1 | 15-01-2021 | 1500 |
| Ruby | Heart | 2 | 1 | 03-01-2021 | 3000 |
| Ruby | Oval | 2 | 1 | 20-02-2021 | 2500 |
| Emerald | Oval | 2 | 2 | 01-01-2021 | 2500 |
| Ruby | Oval | 2 | 1 | 20-06-2021 | 1000 |
| Emerald | Oval | 3 | 3 | 01-01-2021 | 2500 |
What I wanted to do is to group the gem according to shape, size, quality and find the minimum price up to the date another gem that was acquired which belongs to the same group, like below:
| Gem | shape | size | quality | date | price | minimum price to date |
|---|---|---|---|---|---|---|
| Ruby | Oval | 2 | 1 | 01-01-2021 | 2500 | 2500 |
| Ruby | Oval | 2 | 1 | 03-01-2021 | 1500 | 1500 |
| Ruby | Oval | 2 | 1 | 20-02-2021 | 2500 | 1500 |
| Ruby | Oval | 2 | 1 | 20-06-2021 | 1000 | 1000 |
| Ruby | Heart | 2 | 1 | 03-01-2021 | 3000 | 3000 |
df['minimum price to date'] = df.groupby(['gem','shape','size','quality'])['price'].min() gives me the absolute minimum prices of the gems of groups. I can't figure out how to find the rolling minimum price. Any suggestion?
