1

add rows for all dates between two columns?

ID     Initiation_Date  Step    Start_Date   End_Date    Days

P-03    29-11-2018        3      2018-11-29  2018-12-10  11.0
P-04    29-11-2018        4      2018-12-03  2018-12-07   4.0
P-05    29-11-2018        5      2018-12-07  2018-12-07   0.0
santhosh v
  • 19
  • 3
  • 2
    Hi. Please take the time to read this post on [how to provide a great pandas example](http://stackoverflow.com/questions/20109391/how-to-make-good-reproducible-pandas-examples) as well as how to provide a [minimal, complete, and verifiable example](http://stackoverflow.com/help/mcve) and revise your question accordingly. These tips on [how to ask a good question](http://stackoverflow.com/help/how-to-ask) may also be useful. – jezrael Jan 10 '19 at 12:23
  • 1
    What do you want? – Mohit Motwani Jan 10 '19 at 12:33
  • 1
    question not clear – LMSharma Jan 10 '19 at 13:16

1 Answers1

2

Use:

mydata = [{'ID' : '10', 'Entry Date': '10/10/2016', 'Exit Date': '15/10/2016'},
          {'ID' : '20', 'Entry Date': '10/10/2016', 'Exit Date': '18/10/2016'}]

df = pd.DataFrame(mydata)

#convert columns to datetimes
df[['Entry Date','Exit Date']] = df[['Entry Date','Exit Date']].apply(pd.to_datetime)

#repeat index by difference of dates
df = df.loc[df.index.repeat((df['Exit Date'] - df['Entry Date']).dt.days + 1)]
#add counter duplicated rows to day timedeltas to new column
df['Date'] = df['Entry Date'] + pd.to_timedelta(df.groupby(level=0).cumcount(), unit='d')
#default RangeIndex
df = df.reset_index(drop=True)
print (df)
   Entry Date  Exit Date  ID       Date
0  2016-10-10 2016-10-15  10 2016-10-10
1  2016-10-10 2016-10-15  10 2016-10-11
2  2016-10-10 2016-10-15  10 2016-10-12
3  2016-10-10 2016-10-15  10 2016-10-13
4  2016-10-10 2016-10-15  10 2016-10-14
5  2016-10-10 2016-10-15  10 2016-10-15
6  2016-10-10 2016-10-18  20 2016-10-10
7  2016-10-10 2016-10-18  20 2016-10-11
8  2016-10-10 2016-10-18  20 2016-10-12
9  2016-10-10 2016-10-18  20 2016-10-13
10 2016-10-10 2016-10-18  20 2016-10-14
11 2016-10-10 2016-10-18  20 2016-10-15
12 2016-10-10 2016-10-18  20 2016-10-16
13 2016-10-10 2016-10-18  20 2016-10-17
14 2016-10-10 2016-10-18  20 2016-10-18
jezrael
  • 822,522
  • 95
  • 1,334
  • 1,252
  • ** TypeError: Cannot cast array data from dtype('float64') to dtype('int64') according to the rule 'safe'** `data = data.loc[data.index.repeat((data['End_Date'] - data['Start_Date']).dt.days + 1)]` – santhosh v Jan 11 '19 at 05:24