Filling in Missing Years By Group

Hello,

I have a dataset that has groupings based on Genre and Year. I would like to show all years between 2013 and 2020. In some cases, there are missing years in the dataset. I want to add these years so that I can impute the values for them for an analysis. These years also need to be added by group. I am thinking I am needing to use the generate rows tool some how, but I am not sure how to accomplish this. Below is an example of input data. The desired output would show rows for Action 2013 - Action 2020 and Comedy 2013 - Comedy 2020. Rows that were added can either have a NULL sales value or 0. These will be updated via imputation for each group. Thank you for your help in advance.

Genre	Year	Sales
Action	2013	1
Action	2014	5
Action	2016	12
Action	2017	0
Comedy	2014	15
Comedy	2015	2
Comedy	2016	0
Comedy	2017	12
Comedy	2018	5

Help

Accepted answers

Here is how you can do it

Workflow:

1. Using summarize grouby genre to get unique genres.

2. On each genre generate 2013-2020 year rows.

3. Using join multiple tool doing an outer join using genre and year as key. Join multiple by default does outer join hence its handy.

4. Using imputation tool to impute 0's for Nulls

Hope this helps 🙂

All comments

Here is how you can do it

Workflow:

1. Using summarize grouby genre to get unique genres.

2. On each genre generate 2013-2020 year rows.

3. Using join multiple tool doing an outer join using genre and year as key. Join multiple by default does outer join hence its handy.

4. Using imputation tool to impute 0's for Nulls

Hope this helps 🙂

@atcodedog05 This is perfect. It ended up being me over thinking it. Thank you for your help.

Happy to help 🙂 @Link86

Quick Links

Tags

This months top contributors

atcodedog05 19598

Qiu 15867

MarqueeCrew 13708

apathetichell 13703