Pandas column difference over time

user_4367371 0
Viewed 67

**Edit at bottom **

I have a data frame with inventory data that looks like the following:

d = {'product': [a, b, a, b, c], 'amount': [1, 2, 3, 5, 2], 'date': [2020-6-6, 2020-6-6, 2020-6-7, 
2020-6-7, 2020-6-7]}
df = pd.DataFrame(data=d)
df
 product  amount  date
0     a     1      2020-6-6
1     b     2      2020-6-6
2     a     3      2020-6-7
3     b     5      2020-6-7
4     c     2      2020-6-7

I would like to know what the inventory difference is month to month. The output would look like this:

df
 product   diff   isnew  date
0     a     nan   nan   2020-6-6
1     b     nan   nan   2020-6-6
2     a     2     False 2020-6-7
3     b     3     False 2020-6-7
4     c     2     True  2020-6-7

Sorry if I was not clear in the first example, In reality I have many months of data, so I am not just looking at doing the difference of one period vs the other. It would need to be a general case where it looks at the difference of month n vs n-1 and then n-1 and n-2 and so on.

What's the best way to do this in Pandas?

pandas dataframe datetime python
2 Answers

you can try groupby on the column product and diff the column amount for the column 'diff'. Then use duplicated for the column 'isnew'.

df['diff'] = df.groupby('product')['amount'].diff()
df['isnew'] = ~df['product'].duplicated()
print (df)
  product  amount      date  diff  isnew
0       a       1  2020-6-6   NaN   True
1       b       2  2020-6-6   NaN   True
2       a       3  2020-6-7   2.0  False
3       b       5  2020-6-7   3.0  False
4       c       2  2020-6-7   NaN   True
user_92747320

I guess the key here is to find the isnew:

# new products by `product`
new_prods = df['date'] != df.date.min()
duplicated = df.duplicated('product')

# first appearance of new products
# or duplicated *old* products
valids = new_prods ^ duplicated
df.loc[valids,'is_new'] = ~ duplicated

# then the difference:
df['diff'] = (df.groupby('product')['amount'].diff()           # normal differences
                  .fillna(df['amount'])         # fill the first value for all product
                  .where(df['is_new'].notna())  # remove the first month
             )

Output:

  product  amount      date is_new  diff
0       a       1  2020-6-6    NaN   NaN
1       b       2  2020-6-6    NaN   NaN
2       a       3  2020-6-7  False   2.0
3       b       5  2020-6-7  False   3.0
4       c       2  2020-6-7   True   2.0
user_42384080
Related