每天在两列范围内向数据框添加行 [英] Add rows to dataframe each day between a range of two columns

问题描述

我需要知道按房型保留了多少预订.我有这个 DataFrame 带有每个预订的开始和结束及其房间类型:

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_type
0   2017-08-07  2017-08-12  SUI
1   2017-08-09  2017-08-11  TWN
2   2017-08-09  2017-08-11  QUA
3   2017-08-07  2017-08-11  QUA
4   2017-08-09  2017-08-11  QUA
5   2017-08-09  2017-08-11  QUA
6   2017-08-09  2017-08-11  DBL
7   2017-08-08  2017-08-11  FAM
8   2017-08-08  2017-08-16  INDP
9   2017-08-09  2017-08-11  QUA

我需要这样的东西:

    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
5   2017-08-10 TWN
...

所以，我可以像这样创建一个数据透视表:

So, I can create a pivot table like this:

df = df.pivot_table(
    values=['room_type'],
    index='stay_date',
    aggfunc=len
)

然后像这样返回它:

room_type__code DBL FAM SUI TRP TWIN
stay_date                   
2017-07-01      61  20  9   19  39
2017-07-02      49  10  7   11  28
2017-07-03      61  14  4   14  40
2017-07-04      65  13  2   11  37
2017-07-05      66  17  2   11  38

推荐答案

使用 itertuples 和 date_range 频率 D 和 concat 用于创建新的扩展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
...
...

这篇关于每天在两列范围内向数据框添加行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！