partial data in rows

[berndParticipant]

Hello,

I have a csv, which starts with some columns, which are dimensions (Date, year, month, day, weekday) and afterwards 24 columns are following, which are defining / the data of a “curve”.

The header: Date,year,month,day,weekday,cost00,cost01,cost02,cost03,cost04,cost05,cost06,cost07,cost08,cost09,cost10,cost11,cost12,cost13,cost14,cost15,cost16,cost17,cost18,cost19,cost20,cost21,cost22,cost23,

The values are the cost for power per each hour of the day. I want to show the 24 columns in one row as a curve or bargraph and use the first columns as dimensions or filter.

Excuse me, I think the answer is somewhere, but I did not find it with my searching words.

 

Thanks in advance for help!

Greetings Bernd

 

[dgteamModerator]

Hi Bernd,

We recommend transforming the data to a “long” dataset as the plotting command will connect data in a column, not across columns. Transforming the data will also allow you to use the pivot command. Depending on how many days of data you have, the pivot command can be very useful to aggregate the data across weeks or months.

To illustrate, we made a three-day simple dataset with the format you specified.  Here, we selected all the columns with the hourly data. Click the column headers for the first and last of these columns while holding down the shift key.

Next, select  Data > Flatten columns.  You can read more about this option here.

That creates a long dataset. We also sorted the data by control-clicking on the Date column, as shown.

You will want a column of hours as numeric values.  You can remove the text from the category column as follows:

1 – Click the “Category” column header to select.

2 – Select Edit > Copy from the menu.

3 – Select Edit > Paste special, while the column is still selected.

This will open the following window.  Click the boxes as shown and hit the Paste button.

Now, the text is stripped out of the column, and you can Control-click on the column header and convert it to a number column.

After these “data wrangling” steps, you have data in a format that works well with the Pivot command, Or you can use the Plot command.  It depends on whether you want to calculate statistics over some range or you only want to plot the data. For the simple data we created, the Plot command looks like this, where the lines are connected only if x is increasing.  This results in one line per day. You can add a mask if you want different colors.

Feel free to let us know more about how you want to visualize the data and hopefully, we can help you get to a final graph you are happy with.

 

[berndParticipant]

Hello!

 

I followed your introduction, works perfect. I changed my data reading in that way, that the flattened kind of the data is written. Now I’m fine with.

Here two example graphs, which are useful for viewing informations and finding structs.

 

[dgteamModerator]

Glad that worked.  The graph looks great! Excellent use of the Scalar field.

[Author]

Posts