following problem: I have a big time series dataframe with total returns for a stock index. When a stock drops out of the index the value will be set constant until the last date in the dataframe. I want to set the values to NaN as soon as they are constant (as soon as the drop out of the index). How can I do that?
1 Answers
You can take diff and replace the values with np.nans where diff equals to 0 with where (note that it's kind of reversed, "take values from df if it's not equal to 0, otherwise np.nan):
df.where(df.diff().ne(0), np.nan)
For example:
df = pd.DataFrame({
'x': [1, 2, 3, 3, 3, 3, 4, 5],
'y': [1, 1, 1, 2, 3, 3, 3, 4],
})
df.where(df.diff().ne(0), np.nan)
Output:
x y
0 1.0 1.0
1 2.0 NaN
2 3.0 NaN
3 NaN 2.0
4 NaN 3.0
5 NaN NaN
6 4.0 NaN
7 5.0 4.0
Update To only remove the values that are the same until the end of the series, we can find the interval to be replaced with np.nans with diff and cumsum:
df = pd.DataFrame({
'x': [1, 2, 3, 3, 3, 3, 3, 3],
'y': [1, 1, 1, 2, 3, 3, 3, 4],
})
df.where(
df.diff().ne(0)[::-1].cumsum().ne(0)[::-1],
np.nan)
Output:
x y
0 1.0 1
1 2.0 1
2 3.0 1
3 NaN 2
4 NaN 3
5 NaN 3
6 NaN 3
7 NaN 4
Related