Panda's repeating rows fill out the date

user_9437569 0
Viewed 72

I'm stuck on a final piece of code.
I have a dataframe with a start_date, end_date and some more values.

import pandas as pd
from datetime import datetime

data = {'start_date':['2021-02-09','2021-02-12','2021-02-10','2021-02-09'], 
        'end_date':['2021-02-12','2021-02-13','2021-02-10','2021-02-11'],
        'name':['Fender','Gibson','PRS','Martin']}
source = pd.DataFrame(data)
source.start_date=pd.to_datetime(source.start_date)
source.end_date=pd.to_datetime(source.end_date)
print(source)

   start_date   end_date    name  
 0 2021-02-09 2021-02-12  Fender  
 1 2021-02-12 2021-02-13  Gibson  
 2 2021-02-10 2021-02-10     PRS  
 3 2021-02-09 2021-02-11  Martin

The goal is to have a dataframe where the dates are split up per day : (depending the range)

         date    name
0  2021-02-09  Fender
1  2021-02-10  Fender
2  2021-02-11  Fender
3  2021-02-12  Fender
4  2021-02-12  Gibson
5  2021-02-13  Gibson
6  2021-02-10     PRS
7  2021-02-09  Martin
8  2021-02-10  Martin
9  2021-02-11  Martin

I needed to duplicate each row x times depending on the amount of days between 2 dates. and create a new date column with the specific date in it... (later I need to filter out saturdays and sundays) This is what I already have, I guess I'm not that far off.

def splitspans(df):
    df['number'] = (df.end_date - df.start_date).astype('timedelta64[D]').astype('int')+1
    df = pd.DataFrame(df.values.repeat(df['number'], axis=0), columns=df.columns)
    df = df[['start_date','end_date','name']]
    return df

print(splitspans(source))

  start_date   end_date    name
0 2021-02-09 2021-02-12  Fender
1 2021-02-09 2021-02-12  Fender
2 2021-02-09 2021-02-12  Fender
3 2021-02-09 2021-02-12  Fender
4 2021-02-12 2021-02-13  Gibson
5 2021-02-12 2021-02-13  Gibson
6 2021-02-10 2021-02-10     PRS
7 2021-02-09 2021-02-11  Martin
8 2021-02-09 2021-02-11  Martin
9 2021-02-09 2021-02-11  Martin
pandas datetime python
2 Answers

You can first create a list of days in between start and end for each row, and then explode

df = source
df['days'] = (df['end_date'] - df['start_date']).dt.days
df['dates_between'] = df.apply(lambda row: [row['start_date'] + timedelta(days=d) for d in range(row['days']+1)], axis=1)
del df['days']
df.explode('dates_between')

to obtain


    start_date  end_date    name    dates_between
0   2021-02-09  2021-02-12  Fender  2021-02-09
0   2021-02-09  2021-02-12  Fender  2021-02-10
0   2021-02-09  2021-02-12  Fender  2021-02-11
0   2021-02-09  2021-02-12  Fender  2021-02-12
1   2021-02-12  2021-02-13  Gibson  2021-02-12
1   2021-02-12  2021-02-13  Gibson  2021-02-13
2   2021-02-10  2021-02-10  PRS     2021-02-10
3   2021-02-09  2021-02-11  Martin  2021-02-09
3   2021-02-09  2021-02-11  Martin  2021-02-10
3   2021-02-09  2021-02-11  Martin  2021-02-11
user_145514260

@piterbarg's solution is the inspiration for this; I used the pandas date_range:

(
    source.assign(dates=[ pd.date_range(start, end) 
                          for start, end 
                          in zip(source.start_date, source.end_date)]
                 )
          .explode("dates") # you can pass ignore_index=True here
)

  start_date   end_date    name      dates
0 2021-02-09 2021-02-12  Fender 2021-02-09
0 2021-02-09 2021-02-12  Fender 2021-02-10
0 2021-02-09 2021-02-12  Fender 2021-02-11
0 2021-02-09 2021-02-12  Fender 2021-02-12
1 2021-02-12 2021-02-13  Gibson 2021-02-12
1 2021-02-12 2021-02-13  Gibson 2021-02-13
2 2021-02-10 2021-02-10     PRS 2021-02-10
3 2021-02-09 2021-02-11  Martin 2021-02-09
3 2021-02-09 2021-02-11  Martin 2021-02-10
3 2021-02-09 2021-02-11  Martin 2021-02-11
user_71757130
Related