Is there a simple way to include the value before the first in dataframe last?

user_1142881 0
Viewed 51

I'm using the very convenient pandas dataframe last(..) function. In my use case I'm doing the following to get all the samples belonging to the last month of data (I'm working on daily data, used 2D frequency not to pollute the question):

import pandas as pd
import numpy as np

df = pd.DataFrame({'A': np.random.rand(10)}, 
                  index=pd.date_range('2020-06-23', periods=10, freq='2D'))
print(df)
                   A
2020-06-23  0.893443
2020-06-25  0.256981
2020-06-27  0.544561
2020-06-29  0.712149
2020-07-01  0.500871
2020-07-03  0.948928
2020-07-05  0.816448
2020-07-07  0.939283
2020-07-09  0.760055
2020-07-11  0.394204

print(df.last('1M'))
                   A
2020-07-01  0.500871
2020-07-03  0.948928
2020-07-05  0.816448
2020-07-07  0.939283
2020-07-09  0.760055
2020-07-11  0.394204

I basically want the last result plus the last sample from the month before the last:

                   A
2020-06-29  0.712149
2020-07-01  0.500871
2020-07-03  0.948928
2020-07-05  0.816448
2020-07-07  0.939283
2020-07-09  0.760055
2020-07-11  0.394204

I need the last month plus a single sample before (due to computing returns from the first day of the last month need also the t-1 on the first date). I can work out a more convoluted way to do this but was wondering whether there would be an idiomatic and elegant way to do that rather than scrapping the nice last use-case.

pandas dataframe python
2 Answers

I don't think there is any built-in pandas method or parameter that would do this as convenient as pd.DataFrame.last, however, I think a functional way to do it is using the dataframe index and tail:

df = pd.DataFrame({'A': np.random.rand(10)}, 
                  index=pd.date_range('2020-06-23', periods=10, freq='2D'))

last_month = df.last('1M')

last_month = pd.concat([df[~df.index.isin(last_month.index)].tail(1),
                        last_month])

last_month

Output:

                   A
2020-06-29  0.782871
2020-07-01  0.695212
2020-07-03  0.704208
2020-07-05  0.058842
2020-07-07  0.632520
2020-07-09  0.746908
2020-07-11  0.867645
user_63615310

I worked out a different way and had to switch to daily frequency for it to work:

import pandas as pd
import numpy as np
df = pd.DataFrame({'A': np.random.rand(10)}, 
                  index=pd.date_range('2020-06-25', periods=10, freq='1D'))
print(df)
                   A
2020-06-25  0.011542
2020-06-26  0.165026
2020-06-27  0.414716
2020-06-28  0.385021
2020-06-29  0.615932
2020-06-30  0.967423
2020-07-01  0.383592
2020-07-02  0.336468
2020-07-03  0.610473
2020-07-04  0.569487

custom_last_m_idx = df.last('1M').index.insert(0, df.last('1M').index[0] + pd.DateOffset(days=-1))
print(df.loc[custom_last_m_idx])
                   A
2020-06-30  0.967423
2020-07-01  0.383592
2020-07-02  0.336468
2020-07-03  0.610473
2020-07-04  0.569487
user_11428810
Related