Duplicate value at last date of month to all previous dates in that month

user_15329147 0
Viewed 40

This is how the data looks

PERMNO is the ticker for different stocks. I want to extract the RET value for each stock at the last date in each month and duplicate it to the other dates in that month. Say RET = 0.01 for PERMNO 10006 30.06.1928, then all RET values from 01.06.1928-30.06.1928 should show 0.01 as well. This is to be done for all dates and all stocks. I have tried with groupby, loops and dateranges, but run into problems..

Any help is much appreciated!

pandas duplicates datetime python
1 Answers

First convert to datetimes ans sorting and then use GroupBy.last in GroupBy.transform:

df['Date'] = pd.to_datetime(df['Date'])
df = df.sort_values(['PERMNO','Date'])
df['new'] = df.groupby(['PERMNO', df['date'].dt.year, df['date'].dt.month])['RET'].transform('last')
user_29010020
Related