# 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?

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?

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)) ) )`