How can I convert 1 record with a start and end date into multiple records for each day in DolphinDB?

winnie 0
Viewed 17

So I have a table with the following columns:

enter image description here

For each record in the above table (e.g., stock A with a ENTRY_DT as 2011.08.22 and REMOVE_DT as 2011.09.03), I’d like to replicate it for each day between the start and end date (excluding weekends). The converted records keep the same value of fields S_INFO_WINDCODE and SW_IND_CODE as the original record.

Table after conversion should look like this:

enter image description here

(only records of stock A are shown)

union-all cross-join create-table dolphindb missing-data
1 Answers

As the data volume is not large, you can process each record with cj(cross join), then use function unionAll to combine all records into the output table.

The table:

t = table(`A`B`C as S_INFO_WINDCODE, `6112010200`6112010200`6112010200 as SW_IND_CODE, 2011.08.22 1998.11.11 1999.05.27 as ENTRY_DT, 2011.09.03 2010.10.08 2011.09.30 as REMOVE_DT)

Solution:

def f(t, i) {
    windCode = t[i][`S_INFO_WINDCODE]
    code = t[i][`SW_IND_CODE]
    entryDate = t[i][`ENTRY_DT]
    removeDate = t[i][`REMOVE_DT]
    days = entryDate..removeDate
    days = days[weekday(days) between 1:5]
    return cj(table(windCode as S_INFO_WINDCODE, code as SW_IND_CODE), table(days as DT))
}
unionAll(each(f{t}, 1..size(t) - 1), false)
YaN0
Related