Python: Split Start and End Date into All Days Between Start and End Date

Question

I've got data called 'Planned Leave' which includes 'Start Date', 'End Date', 'User ID' and 'Leave Type'.

I want to be able to create a new data-frame which shows all days between Start and End Date, per 'User ID'.

So far, I've only been able to create a date_list which supplies a range of dates between start and end date, but I cannot find a way to include this for each 'User ID' and 'Leave Type'.

Here is my current function:

def datesplit(data):
    x = pd.DataFrame(columns=['Date'])
    for i in plannedleave.iterrows():
        start = data['Start Date'][i]
        end = data['End Date'][i]
        date_list = [start + dt.timedelta(days=x) for x in range((end-start).days)]
    x.append(date_list)
    return x

>>> datesplit(plannedleave)
>>> Value Error: Can only Tuple-index with a MultiIndex

Here's what the data looks like:

>>> plannedleave.dtypes
>>>
    Employee ID                      int64
    First Name                      object
    Last Name                       object
    Leave Type                      object
    Start Date              datetime64[ns]
    End Date                datetime64[ns]
dtype: object

I'd be forever grateful if you could find a solution here! :-)

Can you create [minimal, complete, and verifiable example](http://stackoverflow.com/help/mcve) ? — jezrael, Jun 16 '20 at 06:55
You can check [how to provide a great pandas example](http://stackoverflow.com/questions/20109391/how-to-make-good-reproducible-pandas-examples). — jezrael, Jun 16 '20 at 07:02

jezrael · Answer 1 · 2020-06-16T09:23:37.710

Here are necessary loops, so I prefer DataFrame.itertuples more like DataFrame.iterrows for performance in list comprehension:

def datesplit(df):
    df1 = df.rename(columns={'Start Date':'sdate','End Date':'edate', 'Employee ID':'ID'})
    return  (pd.concat([pd.Series(r.ID,pd.date_range(r.sdate, r.edate)) 
                        for r in df1.itertuples()])
               .rename_axis('Date')
               .reset_index(name='Employee ID'))

df = datesplit(plannedleave)
print (df)
         Date  Employee ID
0  2020-05-10         1001
1  2020-05-11         1001
2  2020-05-12         1001
3  2020-05-13         1001
4  2020-05-14         1001
5  2020-05-15         1001
6  2020-05-18         1002
7  2020-05-19         1002
8  2020-05-20         1002
9  2020-05-21         1002
10 2020-05-22         1002

Performance with 200 rows:

plannedleave = pd.concat([plannedleave] * 100, ignore_index=True)


def datesplit(df):
    df1 = df.rename(columns={'Start Date':'sdate','End Date':'edate', 'Employee ID':'ID'})
    return  (pd.concat([pd.Series(r.ID,pd.date_range(r.sdate, r.edate)) 
                        for r in df1.itertuples()])
               .rename_axis('Date')
               .reset_index(name='Employee ID'))


def datesplitvb(data):
    parts = []
    for idx, row in data.iterrows():
        parts.append(pd.DataFrame(row['Employee ID'], columns=['Employee ID'],
            index=pd.date_range(start=row['Start Date'], end=row['End Date'],
                name='Date')))
    return pd.concat(parts).reset_index()



In [152]: %timeit datesplit(plannedleave.copy())
98.2 ms ± 4.96 ms per loop (mean ± std. dev. of 7 runs, 10 loops each)

In [153]: %timeit datesplitvb(plannedleave.copy())
193 ms ± 30.5 ms per loop (mean ± std. dev. of 7 runs, 10 loops each)

This gives only date + one single column (Employee ID). How can I add all columns into the resulting dataframe? — Vega, Feb 16 '21 at 12:23
@Vega - Use `.Index` like [here](https://stackoverflow.com/questions/59717430/inner-join-pandas-dataframes-with-date-range-columns/59717690#59717690) — jezrael, Feb 16 '21 at 12:24

Valdi_Bo · Accepted Answer · 2020-06-16T08:27:53.073

In my opinion, Date column alone is not enough. Your output DataFrame should also contain at least Employee ID, to know which person is on leave at the given date.

To do your task, define the following function:

def datesplit(data):
    parts = []
    for idx, row in data.iterrows():
        parts.append(pd.DataFrame(row['Employee ID'], columns=['Employee ID'],
            index=pd.date_range(start=row['Start Date'], end=row['End Date'],
                name='Date')))
    return pd.concat(parts).reset_index()

This function:

For each source row collects "partial DataFrames", for now:
- the only column is the Employee ID,
- the index is the date range between start and end date,
- the given Employee ID (a single value) is actually broadcast for all rows (each day the current employee in on leave).
After the loop, concatenates them and converts the index (Date) into a "regular" column.

Then call it:

result = datesplit(plannedleave)

To test my code, I used as a source DataFrame (plannedleave):

   Employee ID First Name Last Name Leave Type Start Date   End Date
0         1001       John     Brown       Xxxx 2020-05-10 2020-05-15
1         1002      Betty     Smith       Yyyy 2020-05-18 2020-05-22

The result, for the above data, is:

         Date  Employee ID
0  2020-05-10         1001
1  2020-05-11         1001
2  2020-05-12         1001
3  2020-05-13         1001
4  2020-05-14         1001
5  2020-05-15         1001
6  2020-05-18         1002
7  2020-05-19         1002
8  2020-05-20         1002
9  2020-05-21         1002
10 2020-05-22         1002

Do you think is fair use your sample data for my solution? – jezrael Jun 16 '20 at 08:31 — jezrael, Jun 16 '20 at 08:31

Python: Split Start and End Date into All Days Between Start and End Date

2 Answers2

Linked