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

user_10261639 0
Viewed 505

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?

pandas dataframe datetime sql python
4 Answers

Comprehension

pd.DataFrame(
    [t + [d] for *t, s, e in df.itertuples(index=False)
     for d in pd.date_range(s, e)],
    columns=df.columns[:-2].tolist() + ['S_DT']
)

    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

Alternate tuple iteration

pd.DataFrame(
    [t + [d] for *t, s, e in zip(*map(df.get, df))
     for d in pd.date_range(s, e)],
    columns=df.columns[:-2].tolist() + ['S_DT']
)

If two date columns aren't at the end, do this ahead of time.

cols = ['S_ST_DT', 'S_END_DT']
df = df.drop(cols, 1).join(df[cols])

for Legacy Python (<=2.7)

pd.DataFrame(
    [t[:-2] + (d,) for t in zip(*map(df.get, df))
     for d in pd.date_range(*t[-2:])],
    columns=df.columns[:-2].tolist() + ['S_DT']
)
user_23366540
  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
user_49090870

Borrowed cold's setting up for v

from collections import ChainMap
d=dict(ChainMap(*map(dict.fromkeys, v, df.index)))
#df=df.reindex(d.values()).assign(DT=d.keys()).sort_index()
df.reindex(d.values()).assign(DT=d.keys()).sort_index()
Out[281]: 
   S_ID  S_QTY S_PRD         DT
0     1    223    AA 2018-06-03
0     1    223    AA 2018-06-04
0     1    223    AA 2018-06-02
1     2    442    FO 2018-05-10
1     2    442    FO 2018-05-12
1     2    442    FO 2018-05-11
2     3    771    WA 2018-07-09
2     3    771    WA 2018-07-08
2     3    771    WA 2018-07-07
2     3    771    WA 2018-07-10
3     4    663   AAG 2018-03-02
3     4    663   AAG 2018-03-03
user_79645270

DB2:

with a (S_ID, S_QTY, S_PRD, S_DT, S_END_DT) as (
select S_ID, S_QTY, S_PRD, S_ST_DT, S_END_DT from sales
  union all
select S_ID, S_QTY, S_PRD, S_DT + 1 day, S_END_DT from a where S_DT<S_END_DT
)
select S_ID, S_QTY, S_PRD, S_DT 
from a 
order by S_ID, S_DT;
user_104182640
Related