python - Pandas - 返回日期范围的单个日期并匹配工作日二进制值

Question

数据集:

下面的数据集应该复制旅游公司的时间表数据集(例如通过火车、公共(public)汽车或飞机等的路线)

df = pd.DataFrame({'operator': ['op_a', 'op_a', 'op_a', 'op_a', 'op_b', 'op_b', 'op_b', 'op_b', 'op_c', 'op_c', 'op_c', 'op_c', 'op_d', 'op_d'],
                   'from': ['a', 'a', 'a', 'a', 'c', 'c', 'c', 'c', 'a', 'a', 'a', 'a', 'x', 'x'], 
                   'to': ['b', 'b', 'b', 'b', 'd', 'd', 'd', 'd', 'b', 'b', 'b', 'b', 'y', 'y'], 
                   'valid_from': ['13/11/2018', '13/11/2018', '13/11/2018', '13/11/2018', '13/11/2018', '13/11/2018', '13/11/2018', '13/11/2018', '15/02/2019', '15/02/2019', '15/02/2019', '15/02/2019', '20/05/2019', '21/05/2019'],
                   'valid_to': ['20/11/2018', '20/11/2018', '19/11/2018', '19/11/2018', '19/11/2018', '19/11/2018', '21/11/2018', '21/11/2018', '21/02/2019', '21/02/2019', '20/02/2019', '20/02/2019', '30/05/2019', '29/05/2019'], 
                   'day_of_week': ['0101010', '0100010', '0111100', '1101100', '0101010', '0100010', '0111100', '1101100', '0001101', '1110000', '0000000', '0000001', '1000000', '1000001']})
    print(df)

operator - 运营公司，例如ABC 航空公司、DEF 火车公司

from - 出发地，例如伦敦、纽约、纳尼亚

to - 目的地，例如巴黎

valid_from - 运营商可以购买路线的日期范围的开始(可以是一周中的任何一天)，例如2019-11-01

valid_to - 运营商可以购买路线的日期范围结束日期(可以是一周中的任何一天)，例如2019-11-12

day_of_week - 表示周日到周六的可用性的二进制文件，例如0101010 表示路线在该日期范围内的周一、周三和周五可用

必填:

输出数据集，将日期范围转换为单个日期及其从 day_of_week 字段派生的可用性。主要目标是获得一个干净的数据集，然后将其加载到 Tableau 中，然后构建一个可以轻松显示路线可用性的报告。

所需输出:

dfout = pd.DataFrame({'operator': ['op_a', 'op_a', 'op_a', 'op_a', 'op_a', 'op_a', 'op_a'], 'from': ['a', 'a', 'a', 'a', 'a', 'a', 'a'], 'to': ['b', 'b', 'b', 'b', 'b', 'b', 'b'], 'date': ['13/11/2018', '14/11/2018', '15/11/2018', '16/11/2018', '17/11/2018', '18/11/2018', '19/11/2018'], 'available': [1, 1, 1, 1, 0, 1, 1]})
print(dfout)

因此，这将是日期范围 2018-11-13< 的路线 a 到 b 的 op_a 的输出 至 2018-11-19。

数据集很奇怪。日期范围可能非常随机，但 day_of_week 将始终显示该日期范围内一周中几天的可用性。某些相同的日期范围甚至可能具有不同的 day_of_week 二进制组合，但本质上，如果在任何时候 day_of_week 指示给定日期范围、路线和运营商的可用性，则它将被视为在该日期可用。

我尝试做的事情:

使用以下内容寻求帮助:Pandas: decompress date range to individual dates

import pandas as pd

df = pd.DataFrame({'operator': ['op_a', 'op_a', 'op_a', 'op_a', 'op_b', 'op_b', 'op_b', 'op_b', 'op_c', 'op_c', 'op_c', 'op_c', 'op_d', 'op_d'],
                   'from': ['a', 'a', 'a', 'a', 'c', 'c', 'c', 'c', 'a', 'a', 'a', 'a', 'x', 'x'], 
                   'to': ['b', 'b', 'b', 'b', 'd', 'd', 'd', 'd', 'b', 'b', 'b', 'b', 'y', 'y'], 
                   'valid_from': ['13/11/2018', '13/11/2018', '13/11/2018', '13/11/2018', '13/11/2018', '13/11/2018', '13/11/2018', '13/11/2018', '15/02/2019', '15/02/2019', '15/02/2019', '15/02/2019', '20/05/2019', '21/05/2019'],
                   'valid_to': ['20/11/2018', '20/11/2018', '19/11/2018', '19/11/2018', '19/11/2018', '19/11/2018', '21/11/2018', '21/11/2018', '21/02/2019', '21/02/2019', '20/02/2019', '20/02/2019', '30/05/2019', '29/05/2019'], 
                   'day_of_week': ['0101010', '0100010', '0111100', '1101100', '0101010', '0100010', '0111100', '1101100', '0001101', '1110000', '0000000', '0000001', '1000000', '1000001']})

df.set_index(['operator', 'from','to'], inplace=True)

df['valid_from'] = pd.to_datetime(df['valid_from'])
df['valid_to'] = pd.to_datetime(df['valid_to'])

df['row'] = range(len(df))
starts = df[['valid_from', 'day_of_week', 'row']].rename(columns={'valid_from': 'date'})
ends = df[['valid_to', 'day_of_week', 'row']].rename(columns={'valid_to':'date'})

df_decomp = pd.concat([starts, ends])
df_decomp = df_decomp.set_index('row', append=True)
df_decomp.sort_index()

df_decomp = df_decomp.groupby(level=[0,1,2,3]).apply(lambda x: x.set_index('date').resample('D').fillna(method='pad'))

结果看起来很有希望。我最后的想法是:

1. 添加一个 weekday 列，返回以 Sunday 开头的 date 的工作日 0
2. 添加一个 available 列，该列使用 weekday 作为位置索引返回 day_of_week 中的二进制值
3. 最后，以某种方式删除重复的operator、from和to行，并保持可用 1 并删除那些 0 或如果这些运算符没有 1'/from's/to's 然后将其保留为 0...

疯狂...对于啰嗦表示歉意，我希望我说得有道理。任何对此的帮助将不胜感激。

编辑:

• 更新了上面的“我尝试做的事情”部分。
• 稍微更新了数据集，以包含更多种类的日期(仍然是同一数据集，只是调整了 valid_to 日期)

Answer 1

如果你不太关心速度，可以使用 iterrows() 和 df.at[]:

import pandas as pd

df = pd.DataFrame({'operator': ['op_a', 'op_a', 'op_a', 'op_a', 'op_b', 'op_b', 'op_b', 'op_b', 'op_c', 'op_c', 'op_c', 'op_c', 'op_d', 'op_d'], 'from': ['a', 'a', 'a', 'a', 'c', 'c', 'c', 'c', 'a', 'a', 'a', 'a', 'x', 'x'], 'to': ['b', 'b', 'b', 'b', 'd', 'd', 'd', 'd', 'b', 'b', 'b', 'b', 'y', 'y'], 'valid_from': ['13/11/2018', '13/11/2018', '13/11/2018', '13/11/2018', '13/11/2018', '13/11/2018', '13/11/2018', '13/11/2018', '15/02/2019', '15/02/2019', '15/02/2019', '15/02/2019', '01/05/2019', '01/05/2019'], 'valid_to': ['19/11/2018', '19/11/2018', '19/11/2018', '19/11/2018', '19/11/2018', '19/11/2018', '19/11/2018', '19/11/2018', '21/02/2019', '21/02/2019', '21/02/2019', '21/02/2019', '10/05/2019', '11/05/2019'], 'day_of_week': ['0101010', '0100010', '0111100', '1101100', '0101010', '0100010', '0111100', '1101100', '0001101', '1110000', '0000000', '0000001', '1000000', '1000001']})

df['valid_from'] = pd.to_datetime(df['valid_from'])
df['valid_to'] = pd.to_datetime(df['valid_to'])
df['day'] = (df['valid_from']+pd.to_timedelta(1, unit='d')).dt.weekday # gives weekdays : ) = Sunday
print df.head()


df_out = pd.DataFrame(columns=['available', 'date', 'from', 'operator', 'to'])

idx = 0
for i, row in df.iterrows():
    daterange = row['valid_to'] - row['valid_from']
    print daterange.days

    daystring = 52 * (row['day_of_week'])  # extend string to allow going through multiple weeks

    for j in range(daterange.days+1):
        df_out.at[idx, ['available', 'date', 'from', 'operator', 'to']] = [ # replaced set_value with df.at[]
            int(daystring[j + row['day']]), # use day of the week as starting position
            row['valid_from']+pd.to_timedelta(j, unit='d'),
            row['from'],
            row['operator'],
            row['to']
            ]

        # row['day_of_week'][j]
        idx += 1

df_out.drop_duplicates(inplace=True) # drop all duplicates
df_0 = df_out[df_out['available']==0]
df_1 = df_out[df_out['available']==1]
df_out = df_0.merge(df_1, how='outer', left_on=['date', 'from', 'operator', 'to'], right_on=['date', 'from', 'operator', 'to'])
df_out.fillna(0, inplace=True)

df_out['available'] = df_out['available_x'] + df_out['available_y']
df_out.drop(['available_x', 'available_y'], axis=1, inplace=True)
df_out.sort_values(by='date',inplace=True)
print df_out