Add Rows To Dataframe Each Day Between A Range Of Two Columns

I need to know how many reservations stay by room type. I have this DataFrame with the start and the end of each reservation and its room type: date_from date_to room_typ

Solution 1:

Use itertuples and date_range with frequency D with concat for creating new expanding DataFrame:

#convert to datetime if necessary 
df['date_from'] = pd.to_datetime(df['date_from'])
#remove one day from to date
df['date_to'] = pd.to_datetime(df['date_to']) - pd.Timedelta(1, unit='d')


df1 = pd.concat([pd.Series(r.room_type, 
                           pd.date_range(r.date_from, r.date_to, freq='D')) 
                           for r in df.itertuples()]) \
        .reset_index()
df1.columns = ['stay_date','room_type']
print (df1)
    stay_date room_type
0  2017-08-07       SUI
1  2017-08-08       SUI
2  2017-08-09       SUI
3  2017-08-10       SUI
4  2017-08-11       SUI
5  2017-08-09       TWN
6  2017-08-10       TWN
7  2017-08-09       QUA
8  2017-08-10       QUA
9  2017-08-07       QUA
10 2017-08-08       QUA
11 2017-08-09       QUA
12 2017-08-10       QUA
13 2017-08-09       QUA
...
...