How do I transform raw time series data to a form that would suit a time series chart with lines named for values in the data itself?

Question

This the data I have -

A           B            C
12/15/2019  Apple       37
12/15/2019  Banana      11
12/15/2019  Carrot       1
12/15/2019  Damson       4
12/15/2019  Fig          1
12/16/2019  Apple       48
12/16/2019  Banana       6
12/16/2019  Carrot       3
12/16/2019  Eggplant     1
12/16/2019  Fig          5

It goes on for years. I have missing data points - See Damson and Eggplant don't exist for both dates.

I don't know if Google SpreadSheets can turn that data into a time line chart as is, of wether I'd need to transform into a table with all of A-F represented as columns on their own and missing entries set to zero explicitly.

I could probably write some Python to make a CSV representation as described, like that (even without the fancy libraries), but I wonder if it is possible in a s/sheet tech.

Is there a simple formula? What is the name for this transform?

Answer 1

Since your ultimate goal is to make a chart, you may want to pivot the data so that each fruit is in its own column.

Assuming the data is in Sheet1!A2:B, choose Insert > New sheet and enter this array formula in cell A2 of the new sheet:

=query( 
  Sheet1!A2:C, 
  "select A, max(C) 
   where A is not null 
   group by A 
   pivot B", 
  0 
)

Then create a chart based on the data in the new sheet.

If you really need to expand the data so that every date and fruit has a value, and that value is zero when no value appears in the source data, insert yet another sheet and enter this array formula in cell A2 of the new sheet:

=arrayformula( query( split( flatten( unique(trim(Sheet1!A2:A)) & "µ" & transpose(unique(Sheet1!B2:B)) ), "µ" ), "where Col1 is not null and Col2 is not null", 0 ) )

...and this formula in cell C2:

=arrayformula( iferror( vlookup(A2:A & B2:B, { Sheet1!A2:A & Sheet1!B2:B, Sheet1!C2:C }, 2, false), if(Sheet1!A2:A, 0, iferror(1/0)) ) )