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

pandas dataframe filter by date
pandas between time
pandas between dates
pandas merge between dates
pandas time between dates
pandas fill between two dates
pandas select dates in range
pandas time between timestamps

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

Pandas GroupBy: Your Guide to Grouping Data in Python – Real , How do you find the date between two dates in Python? Select initial periods of time series based on a date offset. last. Select final periods of time series based on a date offset. DatetimeIndex.indexer_between_time. Get just the index locations for values between particular times of the day.


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']
)

Pandas DataFrame: isin() function, () and pass the name of the column you want to group on, which is "state" . Then, you use ["last_name"] to specify the columns on which you want to perform the actual aggregation. You can pass a lot more than just a single column name to . Select Pandas dataframe rows between two dates. We can perform this using a boolean mask. First, lets ensure the 'birth_date' column is in date format. df['birth_date'] = pd.to_datetime(df['birth_date']) next, set the desired start date and end date to filter df with. -- these can be in datetime (numpy and pandas), timestamp, or string format.


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

() function is used to check each element in the DataFrame is contained in values or not. The result will only be true at a location if all the labels match. If values is a Series, that's the index. If values is a dict, the keys must be the column names, which must match. Normalize start/end dates to midnight before generating date range. name str, default None. Name of the resulting DatetimeIndex. closed {None, ‘left’, ‘right’}, optional. Make the interval closed with respect to the given frequency to the ‘left’, ‘right’, or both sides (None, the default). **kwargs. For compatibility.


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;

import pandasql as ps sqlcode = ''' select A.cusip from A inner join B on A.cusip=​B.ncusip import pandas as pd from datetime import datetime presidents = pd. Python Exercise: Get a list of dates between two dates Last update on February 26 2020 08:09:19 (UTC/GMT +8 hours)


To select DataFrame value between two dates, you can simply use pandas. date_range function. Python's list indexing, e.g. seq[start : end] includes start but not end index, Pandas df. loc[start_date:end_date] includes both end-points in the result if they are in the index. Select Date And Time Ranges. 20 Dec 2017. Preliminaries # Load library import pandas as pd. Create pandas Series Time Data # Create data frame df = pd. DataFrame ()


Select Pandas dataframe rows between two dates. import modules. import pandas as pd import numpy as np. We can perform this using a boolean mask. First, lets ensure the 'birth_date' column is in date format. next, set the desired start date and end date to filter df with. next, set the mask -- we can then apply this to Generating Rows by Month for Date Ranges in Power Query writing some SQL in my data warehouse, joining my date dimension on the start and end dates for the year, and grabbing out the records


Create pandas Series Time Data Select Time Range (Method 1) Select observations between two datetimes df[(df['date'] > '2002-1-1  Python | pandas.date_range() method Python is a great language for doing data analysis, primarily because of the fantastic ecosystem of data-centric python packages. Pandas is one of those packages and makes importing and analyzing data much easier.