(python) Using diff() function in a DataFrame

Question

How can I use the func diff() resetting the result to zero if the date in the current row is different from the date in the previous?

For instance, I have the df below containing ts and value, when generating value_diff I can use:

df['value_diff'] = df.value.diff()

but in this case the row of index 4 will have value_diff = 200 and I need it to reset to zero because date has changed.

i  ts                       value  value_diff
0  2019-01-02 11:48:01.001  100    0
1  2019-01-02 14:26:01.001  150    50
2  2019-01-02 16:12:01.001  75    -75
3  2019-01-02 18:54:01.001  50    -25
4  2019-01-03 09:12:01.001  250   0
5  2019-01-03 12:25:01.001  310   60
6  2019-01-03 16:50:01.001  45    -265
7  2019-01-03 17:10:01.001  30    -15

I know I can build a loop for it, but I was wondering if it can be solved in a more fancy way, maybe using lambda functions.

Side note: "maybe using lambda functions" is not considered "fancier" in pandas, since that will still involve an implicit loop that will be slow. You want to think in terms of groupby — roganjosh
– roganjosh, Commented Feb 4, 2019 at 23:57

roganjosh · Accepted Answer · 2019-02-05 00:12:42Z

2

You want to use groupby and then fillna to get the 0 values.

import pandas as pd

# Reading your example and getting back to correct format from clipboard
df = pd.read_clipboard()
df['ts'] = df['i'] + ' ' + df['ts']
df.drop(['i', 'value_diff'], axis=1, inplace=True) # The columns get misaligned from reading clipboard

# Now we have your original
print(df.head())

# Convert ts to datetime
df['ts'] = pd.to_datetime(df['ts'], infer_datetime_format=True)

# Add a date column for us to groupby
df['date'] = df['ts'].dt.date

# Apply diff and fillna
df['value_diff'] = df.groupby('date')['value'].diff().fillna(0)

answered Feb 5, 2019 at 0:12

roganjosh

13.3k4 gold badges33 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jms Over a year ago

This is really very useful, not only because it perfectly does what I needed, but also is showing all that data manipulation.

Collectives™ on Stack Overflow

(python) Using diff() function in a DataFrame

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related