Q:

How to group rows based on first and last review date columns for each ID and forward fill N/A values using python?

I have the following data frame:
Data frame
Here both Hotel_id and Chef_Id are unique(both can act as a primary key).
I need to fill the missing rows in Month_Year column(should add consecutive month_year between first and last date for each id and then forward fill other corresponding column values, I need something like this:
Expected data frame
Here I have explained for a few id's but I need to apply this concept for every id (nearly 7000) in the data frame.
Please note I have the first_review_date and last_review_date columns for each id. So that using some function I can forward fill the rows between the first_review_date and last_review_date for each id.
PS:
This data set was extracted from March-2015 to February-2020.
So I created another data frame having Month_Year column from March-2015 to February-2020 for each id, then I did outer join with the above-mentioned data frame(in the 1st image - named data frame).
Somehow I got the consecutive month_year for each id but I didn't find a solution how to exactly forward fill the values between first_review_date and last_review_date for each id to fill the N/A values during that particular period and eliminating the rows which don't fall under first_review_date and last_review_date for each id after doing an outer
join.
So please let me know the solution.

@r-beginners, Please find the below data for your reference:
Hotel_id    Month_Year      last_review_date
2400614     May-2015        March-2016
2400614     June-2015       March-2016
2400614     December-2015   March-2016
2400614     January-2016    March-2016
2400614     March-2016      March-2016
2400133     April-2016      May-2017
2400133     June-2016       May-2017
2400133     August-2016     May-2017
2400133     January-2017    May-2017
2400133     April-2017      May-2017
2400133     May-2017        May-2017
2400178     June-2015       April-2018
2400178     July-2016       April-2018
2400178     August-2016     April-2018
2400178     January-2017    April-2018
2400178     March-2017      April-2018
2400178     April-2018      April-2018

A:

Use GroupBy.transform with GroupBy.last:
df['last_review_date'] = df.groupby('Hotel_id')['Month_Year'].transform('last')
print (df)
    Hotel_id     Month_Year last_review_date
0    2400614       May-2015       March-2016
1    2400614      June-2015       March-2016
2    2400614  December-2015       March-2016
3    2400614   January-2016       March-2016
4    2400614     March-2016       March-2016
5    2400133     April-2016         May-2017
6    2400133      June-2016         May-2017
7    2400133    August-2016         May-2017
8    2400133   January-2017         May-2017
9    2400133     April-2017         May-2017
10   2400133       May-2017         May-2017
11   2400178      June-2015       April-2018
12   2400178      July-2016       April-2018
13   2400178    August-2016       April-2018
14   2400178   January-2017       April-2018
15   2400178     March-2017       April-2018
16   2400178     April-2018       April-2018

Another idea is convert values to datetimes and return maximal values per groups
:
df['Month_Year'] = pd.to_datetime(df['Month_Year'], format='%B-%Y')

df['last_review_date'] = df.groupby('Hotel_id')['Month_Year'].transform('max')
print (df)
    Hotel_id Month_Year last_review_date
0    2400614 2015-05-01       2016-03-01
1    2400614 2015-06-01       2016-03-01
2    2400614 2015-12-01       2016-03-01
3    2400614 2016-01-01       2016-03-01
4    2400614 2016-03-01       2016-03-01
5    2400133 2016-04-01       2017-05-01
6    2400133 2016-06-01       2017-05-01
7    2400133 2016-08-01       2017-05-01
8    2400133 2017-01-01       2017-05-01
9    2400133 2017-04-01       2017-05-01
10   2400133 2017-05-01       2017-05-01
11   2400178 2015-06-01       2018-04-01
12   2400178 2016-07-01       2018-04-01
13   2400178 2016-08-01       2018-04-01
14   2400178 2017-01-01       2018-04-01
15   2400178 2017-03-01       2018-04-01
16   2400178 2018-04-01       2018-04-01

If need original format of datetimes:
dates = pd.to_datetime(df['Month_Year'], format='%B-%Y')

df['last_review_date'] = dates.groupby(df['Hotel_id']).transform('max').dt.strftime('%B-%Y')
print (df)
    Hotel_id     Month_Year last_review_date
0    2400614       May-2015       March-2016
1    2400614      June-2015       March-2016
2    2400614  December-2015       March-2016
3    2400614   January-2016       March-2016
4    2400614     March-2016       March-2016
5    2400133     April-2016         May-2017
6    2400133      June-2016         May-2017
7    2400133    August-2016         May-2017
8    2400133   January-2017         May-2017
9    2400133     April-2017         May-2017
10   2400133       May-2017         May-2017
11   2400178      June-2015       April-2018
12   2400178      July-2016       April-2018
13   2400178    August-2016       April-2018
14   2400178   January-2017       April-2018
15   2400178     March-2017       April-2018
16   2400178     April-2018       April-2018

EDIT:
If need add all existing months datetimes per groups use:
df['Month_Year'] = pd.to_datetime(df['Month_Year'], format='%B-%Y')

df1 = (df.set_index('Month_Year')
         .groupby('Hotel_id')
         .resample('1M')
         .ffill()
         .reset_index(level=0, drop=True)
         .reset_index())

print (df1)
   Month_Year  Hotel_id
0  2016-04-30   2400133
1  2016-05-31   2400133
2  2016-06-30   2400133
3  2016-07-31   2400133
4  2016-08-31   2400133
5  2016-09-30   2400133
6  2016-10-31   2400133
7  2016-11-30   2400133
8  2016-12-31   2400133
9  2017-01-31   2400133
10 2017-02-28   2400133
11 2017-03-31   2400133
12 2017-04-30   2400133
13 2017-05-31   2400133
14 2015-06-30   2400178
15 2015-07-31   2400178
16 2015-08-31   2400178
17 2015-09-30   2400178
18 2015-10-31   2400178
19 2015-11-30   2400178
20 2015-12-31   2400178
21 2016-01-31   2400178
22 2016-02-29   2400178
23 2016-03-31   2400178
24 2016-04-30   2400178
25 2016-05-31   2400178
26 2016-06-30   2400178
27 2016-07-31   2400178
28 2016-08-31   2400178
29 2016-09-30   2400178
30 2016-10-31   2400178
31 2016-11-30   2400178
32 2016-12-31   2400178
33 2017-01-31   2400178
34 2017-02-28   2400178
35 2017-03-31   2400178
36 2017-04-30   2400178
37 2017-05-31   2400178
38 2017-06-30   2400178
39 2017-07-31   2400178
40 2017-08-31   2400178
41 2017-09-30   2400178
42 2017-10-31   2400178
43 2017-11-30   2400178
44 2017-12-31   2400178
45 2018-01-31   2400178
46 2018-02-28   2400178
47 2018-03-31   2400178
48 2018-04-30   2400178
49 2015-05-31   2400614
50 2015-06-30   2400614
51 2015-07-31   2400614
52 2015-08-31   2400614
53 2015-09-30   2400614
54 2015-10-31   2400614
55 2015-11-30   2400614
56 2015-12-31   2400614
57 2016-01-31   2400614
58 2016-02-29   2400614
59 2016-03-31   2400614

