Query to fill in missing dates with value from previous date

user_5625896 0
Viewed 91

I want to build a continuous table of dates for each customer.

lets suppose I have this data frame 

 con = pyodbc.connect (....)

The reason why I taking dateadd(day,-1,getdate()) is beacuse there is no data in the table for getdate() only for yesterday.

SQL_Until_Today = pd.read_sql_query("Select date, customer,value from account where date < convert(date,dateadd(day,-1,getdate()))", con)

    account  = pd.dataframe(SQL_Until_Today , columns = ['date','customer','value'])

SQL_Today = pd.read_sql_query("Select date, customer,value from account where date = convert(date,dateadd(day,-1,getdate()))",con)
    account_Today = pd.dataframe(SQL_Today,columns =
    ['date', 'customer','value'])

    account = account.append(account_Today)

So From those two I end up with a dataframe named account who looks something like this:

date         customer value
2019-06-27    100       40
2019-06-28    100       30
2019-06-30    100       20
2019-07-01    100       10
2019-07-02    100       18
2019-06-21    200       460
2019-06-23    200       430
2019-06-24    200       410
2019-06-25    200       130
2019-06-26    200       210
2019-06-27    200       410
2019-06-28    200       310
2019-06-30    200       210
2019-07-01    200       110
2019-07-02    200       118

I need to create a continuous table of dates for each customer starting from the min_date he has in the table.

For example: 

customer = 100 --> 2019-06-27
customer = 200 --> 2019-06-21

so my desired output for account dataframe will be : 

date         customer value
2019-06-27    100       40
2019-06-28    100       30
2019-06-29    100       30 *************** The most closer value before!
2019-06-30    100       20
2019-07-01    100       10
2019-07-02    100       18
2019-07-03    100       18 **************** The most closer value before!
2019-06-21    200       460
2019-06-22    200       460 *************** The most closer value before!
2019-06-23    200       430
2019-06-24    200       410
2019-06-25    200       130
2019-06-26    200       210
2019-06-27    200       410
2019-06-28    200       310
2019-06-29    200       310 *************** The most closer value before!
2019-06-30    200       210
2019-07-01    200       110
2019-07-02    200       118
2019-07-03    200       118 *************** The most closer value before!

If there will be a gap of two dates, still I will want to take the value from the most closer date.

Any help how can I perform it effectively?

pandas pycharm pyodbc sql python
1 Answers

A common approach is to use a separate "date table" containing one row for each valid date that covers (or exceeds) the range over which you need to query. For example, in this particular case a table like the following would suffice:

date_table

date      
----------
2019-06-15
2019-06-16
2019-06-17
2019-06-18
2019-06-19
2019-06-20
2019-06-21
2019-06-22
2019-06-23
2019-06-24
2019-06-25
2019-06-26
2019-06-27
2019-06-28
2019-06-29
2019-06-30
2019-07-01
2019-07-02
2019-07-03
2019-07-04
2019-07-05

Given your exsting data

account

date        customer  value
----------  --------  -----
2019-06-27       100     40
2019-06-28       100     30
2019-06-30       100     20
2019-07-01       100     10
2019-07-02       100     18
2019-06-21       200    460
2019-06-23       200    430
2019-06-24       200    410
2019-06-25       200    130
2019-06-26       200    210
2019-06-27       200    410
2019-06-28       200    310
2019-06-30       200    210
2019-07-01       200    110
2019-07-02       200    118

you would start with a query that includes every actual_date for each customer

SELECT date_table.date AS actual_date, cust.customer
FROM 
    date_table,
    (SELECT DISTINCT account.customer FROM account) cust
WHERE 
    date_table.date >= (SELECT MIN(account.date) FROM account)
    AND
    date_table.date <= (SELECT MAX(account.date) FROM account)

Next, wrap the above as a subquery (named cust_date) to determine the reference_date for each customer/actual_date

SELECT cust_date.actual_date AS actual_date, cust_date.customer, MAX(acc.date) AS reference_date
FROM 
    (
        SELECT date_table.date AS actual_date, cust.customer
        FROM 
            date_table,
            (SELECT DISTINCT account.customer FROM account) cust
        WHERE 
            date_table.date >= (SELECT MIN(account.date) FROM account)
            AND
            date_table.date <= (SELECT MAX(account.date) FROM account)
    ) cust_date
    INNER JOIN 
    account acc 
        ON acc.customer = cust_date.customer AND acc.date <= cust_date.actual_date
GROUP BY cust_date.actual_date, cust_date.customer

Finally, wrap that as a subquery (named ref_date) to extract the reference_value based on the reference_date

SELECT ref_date.actual_date, ref_date.customer, acc.value
FROM
    (
        SELECT cust_date.actual_date AS actual_date, cust_date.customer, MAX(acc.date) AS reference_date
        FROM 
            (
                SELECT date_table.date AS actual_date, cust.customer
                FROM 
                    date_table,
                    (SELECT DISTINCT account.customer FROM account) cust
                WHERE 
                    date_table.date >= (SELECT MIN(account.date) FROM account)
                    AND
                    date_table.date <= (SELECT MAX(account.date) FROM account)
            ) cust_date
            INNER JOIN 
            account acc 
                ON acc.customer = cust_date.customer AND acc.date <= cust_date.actual_date
        GROUP BY cust_date.actual_date, cust_date.customer
    ) ref_date
    INNER JOIN
    account acc
        ON acc.customer = ref_date.customer AND acc.date = ref_date.reference_date
ORDER BY ref_date.customer, ref_date.actual_date

which produces

actual_date  customer  value
-----------  --------  -----
2019-06-27        100     40
2019-06-28        100     30
2019-06-29        100     30
2019-06-30        100     20
2019-07-01        100     10
2019-07-02        100     18
2019-06-21        200    460
2019-06-22        200    460
2019-06-23        200    430
2019-06-24        200    410
2019-06-25        200    130
2019-06-26        200    210
2019-06-27        200    410
2019-06-28        200    310
2019-06-29        200    310
2019-06-30        200    210
2019-07-01        200    110
2019-07-02        200    118
user_21443900
Related