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

  • A+
Category:Languages

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?

 


  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 

Comment

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: