How to get in-between dates from a date range in SQL and Pandas

Question

Below is the Sales table which has following data:

Sales:

S_ID    S_QTY   S_PRD   S_ST_DT     S_END_DT
1       223     AA      2018-06-02  2018-06-04
2       442     FO      2018-05-10  2018-05-12
3       771     WA      2018-07-07  2018-07-10
4       663     AAG     2018-03-02  2018-03-03

I am trying to get the dates between the S_ST_DT and S_END_DT. Expecting the following O/P in DB2-SQL and Pandas:

Expected O/P:

S_ID    S_QTY   S_PRD   S_DT        
1       223     AA      2018-06-02
1       223     AA      2018-06-03
1       223     AA      2018-06-04
2       442     FO      2018-05-10
2       442     FO      2018-05-11
2       442     FO      2018-05-12
3       771     WA      2018-07-07
3       771     WA      2018-07-08
3       771     WA      2018-07-09
3       771     WA      2018-07-10
4       663     AAG     2018-03-02
4       663     AAG     2018-03-03

Any suggestions here?

Answer 1

1. Use pop and extract the last two columns
2. Compute the date range using pd.date_range
3. Reshape your data using np.repeat
4. Create the dataFrame, flatten the dates list and assign to the newly created df

from itertools import chain
v = [pd.date_range(x, y) 
         for x, y in zip(df.pop('S_ST_DT'), df.pop('S_END_DT'))]

df = (pd.DataFrame(df.values.repeat([len(u) for u in v], axis=0), 
                  columns=df.columns)
        .assign(S_DT=list(chain.from_iterable(v))))
print(df)

   S_ID S_QTY S_PRD       S_DT
0     1   223    AA 2018-06-02
1     1   223    AA 2018-06-03
2     1   223    AA 2018-06-04
3     2   442    FO 2018-05-10
4     2   442    FO 2018-05-11
5     2   442    FO 2018-05-12
6     3   771    WA 2018-07-07
7     3   771    WA 2018-07-08
8     3   771    WA 2018-07-09
9     3   771    WA 2018-07-10
10    4   663   AAG 2018-03-02
11    4   663   AAG 2018-03-03