Can I convert a tall column (thousands) into a series of columns, by subcategory?

[Casual]

I have a year’s worth of hourly data, so almost 9,000 rows. I’d like to make charts showing one week’s data – with multiple weeks superimposed, so several lines on the same chart. That would mean converting all the, for example, “Monday 7am” rows to a single row. Now there would be many more columns: first week, second week, etc.

The data won’t change, so I could use a single process instead of a formula. I could do it manually, cutting and pasting 53 segments, each row about 168 rows, but of course I don’t really want to (and there will be other places to apply this tool if it exists). I can use the date and time entries to create various fields for organizing.

Is there anything that might help with this? I don’t have access to a good database tool.

[EarnestAl]

If you don’t want to use Base, then it sounds like a candidate for a pivot table. There are more days than hours so I suggest to have hours by columns.
Make sure that your dates are dates and not text.

• Click in you table and then click Data > Pivot table > Insert or Edit. Click OK
• A Pivot Table Layout dialogue will appear. For now drag the Date into the Row Fields and data into Data Fields. OK
• Click on a cell in the Date column and then click Data > Group & Outline > Group. In the dialogue tick Hours and Days. OK
• Right click the pivot table and drag Date to Column Fields. OK

ConvertColumnIntoSubCategoryColumns.ods (20.6 KB)

[Casual]

Thanks! I will try that. And I literally forgot Base exists, since I never use or install it … maybe I should try that, too.

Edit: oh.

[Villeroy]

If you never used any database tools, you will be disappointed by Base and databases in general, although this would be the tool of choice for most things that today’s spreadsheet users try to do on sheets.
The pivot table is the most database-ish tool Calc has to offer. However, grouping a pivot table by weeks is not trivial. See attachement, second sheet
Pivot_ByWeek_Product.ods (114.7 KB)

[EarnestAl]

It very much depends on your data on how you need to handle it, I assumed telemetry data and that could still be grouped by 7 days but you might prefer to use average rather than sum for Data Fields.

There is more information on pivot tables in the Calc Guide, downloadable from English documentation | LibreOffice Documentation - LibreOffice User Guides. Of course there is also the Base Guide too.

[Casual]

I have to admit I have never used pivot tables. They are a common punchline, but in my head I was just picturing transposed data. Not trivial, as you say, but as a tool in general seems to be worth learning, so I may as well practice it on this.