How can I melt the dataframe getting all dates between a range?

Question

I have a dataset like this:

    import pandas as pd    
    pd.DataFrame({'col1': [1, 2], 'start date': ['1/3/2019', '1/10/2019'],
                                  'end date':['1/5/2019','1/12/2019']} )

I want to get a row for each of the values between Start Date and End Date.

    pd.DataFrame({'col1': [1,1,1,2,2,2],
                  'date': ['1/3/2019','1/4/2019','1/5/2019',
                           '1/10/2019', '1/11/2019', '1/12/2019'],

                  'start date': ['1/3/2019', '1/3/2019', '1/3/2019',
                           '1/10/2019', '1/10/2019', '1/10/2019'],

                  'end date':['1/5/2019','1/5/2019','1/5/2019',
                           '1/12/2019','1/12/2019','1/12/2019']} )

Edit: Range overlap is allowed.

I am trying to use melt but I can't get what I want.

@QuangHoang At the moment 254, it will change weekly. The ranges are about 20 days long. — Luan Vieira, May 27 '19 at 17:46
@jeschwar Yes. There are 10 more columns, I just want all other values to repeat. — Luan Vieira, May 27 '19 at 18:24
Related [question](https://stackoverflow.com/questions/53218931/how-to-unnest-explode-a-column-in-a-pandas-dataframe). — Quang Hoang, May 27 '19 at 18:25

score 2 · Accepted Answer · answered May 27 '19 at 19:24

I would use apply with with a custom function return a series from pd.date_range

def create_range(x):
    s = pd.Series(pd.date_range(start=x['start date'], end=x['end date'])).dt.strftime('%m/%d/%Y')
    return s

Using the custom function above to apply, stack, reset_index and join with df

df[['start date', 'end date']].apply(create_range, axis=1).stack().reset_index(level=-1, drop=True).to_frame('date').join(df)


Out[1742]:
         date  col1   end date start date
0  01/03/2019     1   1/5/2019   1/3/2019
0  01/04/2019     1   1/5/2019   1/3/2019
0  01/05/2019     1   1/5/2019   1/3/2019
1  01/10/2019     2  1/12/2019  1/10/2019
1  01/11/2019     2  1/12/2019  1/10/2019
1  01/12/2019     2  1/12/2019  1/10/2019

score 1 · Answer 2 · answered May 27 '19 at 18:37

Here is something to consider which uses pd.date_range and a groupby.apply:

df = pd.DataFrame({'col1': [1, 2], 'start date': ['1/3/2019', '1/10/2019'], 'end date':['1/5/2019','1/12/2019']} )
df.set_index(['col1', 'start date', 'end date'], inplace=True)
df_result = df.groupby(['col1', 'start date', 'end date']).apply(lambda df_: pd.date_range(*df_.index[0][1:], freq='D').to_frame(index=True, name='date'))
df_result.reset_index(inplace=True)
df_result.reindex(['col1', 'date', 'start date', 'end date'], axis=1)

# produces the following:

   col1       date start date   end date
0     1 2019-01-03   1/3/2019   1/5/2019
1     1 2019-01-04   1/3/2019   1/5/2019
2     1 2019-01-05   1/3/2019   1/5/2019
3     2 2019-01-10  1/10/2019  1/12/2019
4     2 2019-01-11  1/10/2019  1/12/2019
5     2 2019-01-12  1/10/2019  1/12/2019

score 1 · Answer 3 · answered May 27 '19 at 18:39

I would use range_index to build the list of dates between start date and end date, build a partial dataframe from each row and then concat them all:

pd.concat(
    [pd.DataFrame(row.to_dict(), index = pd.date_range(
        start = pd.to_datetime(df.loc[i, 'start date']),
        end = pd.to_datetime(df.loc[i, 'end date']),
        freq = 'D')).rename_axis('date').reset_index()
     for i, row in df.iterrows()], ignore_index=True)

How can I melt the dataframe getting all dates between a range?

3 Answers3

Linked