incrementally correcting for missing rows & columns

I have a system that creates 9 groups of aggregated data from a single table–so I can further define specific values for each of those 9 groups based on months&years. Works great.

each of the 9 groups outputs the below data, which I can then chart and present online via google data studio

My problem lies in some of the 9 groups not having data for particular months&years, such as seen below. The current max rows are 42, with some data sets having only half that number of rows–since there was no data for the progressive months&years.

What I want to do is “force” all 9 output tables to have the same number of rows (in this example 42) and column headers–within the tables coming up short on months&years blank. But the added rows must be in chronological order, not simply appended at the end of the table using node “add empty rows”. I can then use a node to either use prior values or averaged values to replace the blank data cells within the inserted chronological rows.

I need to force all 9 tables to have the same number of rows and columns and column headers, with any of the 9 sets coming up short of the current maximum of 42 “auto populating” missing rows in the order based on months&years as you see in the output tables.

thanks!

Hi @creedsmith , if I’m understanding your problem correctly, you have data for years and months (one row per year-month). Some of the months are missing and you need to insert rows for just the missing months.

The attached gives one idea on this.

Here I’ve just hand coded a couple of “seed” tables. One for year and the other for months.

These initial tables could be generated, but for this it seems simpler to just provide the initial years.

A cross join gives all months for all years.
I assumed there was some sort of cutoff that would be determined, so I’ve just included that as a manual cutoff but this data could be fed from maybe a “max” derived from your data.

After that, the Joiner (Labs) node is used to join the data to the calendar. Unlike the regular Joiner node, this gives the ability to return us just the rows from the calendar that don’t match, and this data set of “missing periods” is then concatenated with the sample data (you’ll want to make sure the column names for “year” and “month” exactly match those from your data), and then it’s sorted on year,month.

Sample Data:

Output:

Insert missing periods.knwf (27.7 KB)

To include in a workflow, the initial setting up of the “calendar” is performed once:
image

The Joiner (Labs) and beyond occurs once per dataset to be “fixed”
image

2 Likes

Wow, thanks for all of your work here. Let me experiment and see if I can make this all work. And yes, your assumption of my need was exactly right.

2 Likes

Hi @creedsmtih, You are welcome, and I hope it works for you.

This was one of those cases where I could see a use for a generic solution for in-filling missing periods, or even for creating a “period calendar”, so I packaged the above into a component.

If you are interested, you can find it here, where you can drop it into your workflow, configure the required period and columns (see the help doc on the component)

image

It is available here, and any feedback or issues, please let me know.

2 Likes

Yes, I adapted your initial solution into my process, removing and/or rearranging my original flow to incorporate your solution, then ended up using node “missing value”/moving average to create data in the table’s empty cells.

I will certainly grab your node and see if it makes things even cleaner.

Once again, thank you

1 Like

Just went to look at your new node download–and see 16 downloads already in its first day of existence. So you are correct, the need is certainly there.

1 Like

Yes, your shiny new node works perfectly, with me getting the exact same results in my end table after replacing the node series below with your single node solution. Nice work!

3 Likes

I played around with python and for anyone who looks for a way fill in missing dates in the future and stumbles over this thread might find this helpful too (sorry if I overstepped and misused this topic here)
my flow looked like that.
s1


s4
br

2 Likes

I’m sure your Python system here will help those smarter than me and know how to actually program :smiley: . In every situation it’s best to get several options and determine which, or if a combination of multiple options works best. Thanks

1 Like

For some strange reasons I have never seen the “one size fits all” solution until now even though it’s what everyone is always lookin for :laughing:
br

This topic was automatically closed 182 days after the last reply. New replies are no longer allowed.