what's the best way to make charts of spreadsheet data

classic Classic list List threaded Threaded
10 messages Options
Dave Howorth Dave Howorth
Reply | Threaded
Open this post in threaded view
|

what's the best way to make charts of spreadsheet data


I just made a chart of some data in LibreOffice Calc and was
disappointed both by the process (very slow) and by the results
(apparent lack of ability to modify the view).

I've used spreadsheets and LO Calc specifically sporadically over the
years to make calculations but wouldn't call myself an expert. I can't
remember the last time I tried to make a chart.

So I'm wondering if I've missed something in LO that would help me have
a better experience with this chart, or if there are better tools to
use to draw charts from spreadsheet data.

I'm running Version 6.1.3.2 of LibreOffice (actually the openSUSE
build for Leap 15.0). I'm running a machine with an i5-4460 CPU @
3.20GHz and 8 GB main memory. It's normally fast enough for whatever I
want to do.

My spreadsheet data is in columns. Column A is the dates and column B
is the times. Then ten columns of humidity data and eleven columns of
temperature data. There are currently just over 5000 rows of samples.

Making the chart was extremely slow. Every time I changed the Name of a
line of the chart I had to wait whilst it cycled through several steps
to make the change. Initially when I removed some empty columns from
the data range, it took an age to do so. etc.

Now I've created the chart and got some reasonable lines across it, but
what I hope are the dates and times along the X axis are just a mess and
I've no idea how to make them display usefully.

The Y-axis is reasonable from 0 to 120, although it would be nice to
trim the blank space at the top a bit, since the data can't exceed 100.

I haven't found any way to select individual points on the chart to
display the detailed value of that point or all the values at that
time. Nor do I see a way to temporarily hide individual data lines to
more clearly see other particular lines, for example.

If there's any way to do these things with an LO chart, and to speed it
up, I'd be grateful for any pointers. Or if there are better options
out there that can read .ods or .csv and display charts, that would also
be interesting.

TIA, Dave

--
To unsubscribe e-mail to: [hidden email]
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy

Luuk Luuk
Reply | Threaded
Open this post in threaded view
|

Re: what's the best way to make charts of spreadsheet data


On 2-1-2019 18:12, Dave Howorth wrote:
> The Y-axis is reasonable from 0 to 120, although it would be nice to
> trim the blank space at the top a bit, since the data can't exceed 100.

Right click on the Y-Axis, and choose 'Format Axis'

A popup will be schown where you can set Minimum- and Maximum value for
the Axis (and more)



--
To unsubscribe e-mail to: [hidden email]
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy
Windows7 / LibreOffice  3.3 330m19(Build:6)
Dave Howorth Dave Howorth
Reply | Threaded
Open this post in threaded view
|

Re: what's the best way to make charts of spreadsheet data

On Wed, 2 Jan 2019 21:31:02 +0100
Luuk <[hidden email]> wrote:

> On 2-1-2019 18:12, Dave Howorth wrote:
> > The Y-axis is reasonable from 0 to 120, although it would be nice to
> > trim the blank space at the top a bit, since the data can't exceed
> > 100.  
>
> Right click on the Y-Axis, and choose 'Format Axis'
>
> A popup will be schown where you can set Minimum- and Maximum value
> for the Axis (and more)

Thanks for that. It works well. Sadly the same operation apparently
does nothing to improve the X-axis legibility, which is a much worse
problem.

--
To unsubscribe e-mail to: [hidden email]
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy

Regina Henschel Regina Henschel
Reply | Threaded
Open this post in threaded view
|

Re: what's the best way to make charts of spreadsheet data

In reply to this post by Dave Howorth
Hi Dave,

Dave Howorth schrieb am 02-Jan-19 um 18:12:

>[..]
>
> My spreadsheet data is in columns. Column A is the dates and column B
> is the times. Then ten columns of humidity data and eleven columns of
> temperature data. There are currently just over 5000 rows of samples.
>
> Making the chart was extremely slow. Every time I changed the Name of a
> line of the chart I had to wait whilst it cycled through several steps
> to make the change. Initially when I removed some empty columns from
> the data range, it took an age to do so. etc.

Each data item produces an object, which is selectable in edit mode of
the chart. Your data produce 5000*(10+11)=105000 objects. That cannot be
fast.

What is the purpose of the chart? You do not need 105000 objects to
illustrate something.

With 5000 items in x-direction a lot of them will be on top of others.
My screen has only 1920 dots width and yours?

>
> Now I've created the chart and got some reasonable lines across it, but
> what I hope are the dates and times along the X axis are just a mess and
> I've no idea how to make them display usefully.

I guess you have used a line chart. That x-axis cannot display time
units, the smallest unit is day.
A line chart shows an axis label for each data item. That is likely not
suitable for your chart.

You should try using an XY-chart.

In case you need different positions inside one day, you should make a
separate column with added date and time values. Then use this column
for your x-axis.

>
> The Y-axis is reasonable from 0 to 120, although it would be nice to
> trim the blank space at the top a bit, since the data can't exceed 100.
>
> I haven't found any way to select individual points on the chart to
> display the detailed value of that point or all the values at that
> time.

Access to single points is only available in edit mode of the chart. The
information of the point is shown as tooltip.

You can display the value of data points as data label. Enable it in
edit mode in menu Insert > Data Label, or for a distinct point in its
context menu "Insert Single Data Label".

In case you use an XY-chart you can generate a text column for labels
Such are called 'category' in the dialogs. But there is only one text
per row possible.

  Nor do I see a way to temporarily hide individual data lines to
> more clearly see other particular lines, for example.

You can hide the associated column, then the line is removed from the
chart. When you show the column, the line is drawn again.

>
> If there's any way to do these things with an LO chart, and to speed it
> up, I'd be grateful for any pointers. Or if there are better options
> out there that can read .ods or .csv and display charts, that would also
> be interesting.

It seems you want to explore your data. Some people have pointed to the
R-project. But I have never used it. Or you might find suitable tools
with search term 'data mining'.

Kind regards
Regina


--
To unsubscribe e-mail to: [hidden email]
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy
Dave Howorth Dave Howorth
Reply | Threaded
Open this post in threaded view
|

Re: what's the best way to make charts of spreadsheet data

On Fri, 4 Jan 2019 18:54:47 +0100
Regina Henschel <[hidden email]> wrote:

> Hi Dave,
>
> Dave Howorth schrieb am 02-Jan-19 um 18:12:
> >[..]
> >
> > My spreadsheet data is in columns. Column A is the dates and column
> > B is the times. Then ten columns of humidity data and eleven
> > columns of temperature data. There are currently just over 5000
> > rows of samples.
> >
> > Making the chart was extremely slow. Every time I changed the Name
> > of a line of the chart I had to wait whilst it cycled through
> > several steps to make the change. Initially when I removed some
> > empty columns from the data range, it took an age to do so. etc.  
>
> Each data item produces an object, which is selectable in edit mode
> of the chart. Your data produce 5000*(10+11)=105000 objects. That
> cannot be fast.
>
> What is the purpose of the chart? You do not need 105000 objects to
> illustrate something.
>
> With 5000 items in x-direction a lot of them will be on top of
> others. My screen has only 1920 dots width and yours?

Thanks, but this answers itself. If my screen or window has less
pixels, then why create more objects and try to represent them? The
chart software should be sampling the data appropriately to the
resolution.

And sure if I want to explore the detail, then I should be able to
expand a section of the X-axis. I'm thinking of something like the
graph module in emoncms, which handles this task with reasonable speed.

> > Now I've created the chart and got some reasonable lines across it,
> > but what I hope are the dates and times along the X axis are just a
> > mess and I've no idea how to make them display usefully.  
>
> I guess you have used a line chart. That x-axis cannot display time
> units, the smallest unit is day.
> A line chart shows an axis label for each data item. That is likely
> not suitable for your chart.

Correct, I'm using a line chart (i.e. the data I have are a number of
time series). Thanks for the info about how it behaves.

> You should try using an XY-chart.

Thanks. Yes, that seems to work better. It's a lot faster as well :)

> In case you need different positions inside one day, you should make
> a separate column with added date and time values. Then use this
> column for your x-axis.

Yes, I'd created the column and will use it.

> > The Y-axis is reasonable from 0 to 120, although it would be nice to
> > trim the blank space at the top a bit, since the data can't exceed
> > 100.
> >
> > I haven't found any way to select individual points on the chart to
> > display the detailed value of that point or all the values at that
> > time.  
>
> Access to single points is only available in edit mode of the chart.
> The information of the point is shown as tooltip.

I don't see any tooltips?

> You can display the value of data points as data label. Enable it in
> edit mode in menu Insert > Data Label, or for a distinct point in its
> context menu "Insert Single Data Label".
>
> In case you use an XY-chart you can generate a text column for labels
> Such are called 'category' in the dialogs. But there is only one text
> per row possible.
>
>   Nor do I see a way to temporarily hide individual data lines to
> > more clearly see other particular lines, for example.  
>
> You can hide the associated column, then the line is removed from the
> chart. When you show the column, the line is drawn again.

OK, thanks. That works but is a bit laborious.

> > If there's any way to do these things with an LO chart, and to
> > speed it up, I'd be grateful for any pointers. Or if there are
> > better options out there that can read .ods or .csv and display
> > charts, that would also be interesting.  
>
> It seems you want to explore your data. Some people have pointed to
> the R-project. But I have never used it. Or you might find suitable
> tools with search term 'data mining'.

Thanks, I'll have a look. I might import the data into my emoncms
system.

> Kind regards
> Regina

--
To unsubscribe e-mail to: [hidden email]
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy

remygauthier remygauthier
Reply | Threaded
Open this post in threaded view
|

Re: what's the best way to make charts of spreadsheet data

Hi,

You could also have a look here:

https://www.linuxlinks.com/plottingtools/

Rémy.

Le vendredi 04 janvier 2019 à 22:05 +0000, Dave Howorth a écrit :

> On Fri, 4 Jan 2019 18:54:47 +0100
> Regina Henschel <[hidden email]> wrote:
>
>
> Hi Dave,
>
> Dave Howorth schrieb am 02-Jan-19 um 18:12:
>
> [..]
>
> My spreadsheet data is in columns. Column A is the dates and column
> B is the times. Then ten columns of humidity data and eleven
> columns of temperature data. There are currently just over 5000
> rows of samples.
>
> Making the chart was extremely slow. Every time I changed the Name
> of a line of the chart I had to wait whilst it cycled through
> several steps to make the change. Initially when I removed some
> empty columns from the data range, it took an age to do so. etc.  
>
> Each data item produces an object, which is selectable in edit mode
> of the chart. Your data produce 5000*(10+11)=105000 objects. That
> cannot be fast.
>
> What is the purpose of the chart? You do not need 105000 objects to
> illustrate something.
>
> With 5000 items in x-direction a lot of them will be on top of
> others. My screen has only 1920 dots width and yours?
>
> Thanks, but this answers itself. If my screen or window has less
> pixels, then why create more objects and try to represent them? The
> chart software should be sampling the data appropriately to the
> resolution.
>
> And sure if I want to explore the detail, then I should be able to
> expand a section of the X-axis. I'm thinking of something like the
> graph module in emoncms, which handles this task with reasonable speed.
>
>
>
> Now I've created the chart and got some reasonable lines across it,
> but what I hope are the dates and times along the X axis are just a
> mess and I've no idea how to make them display usefully.  
>
> I guess you have used a line chart. That x-axis cannot display time
> units, the smallest unit is day.
> A line chart shows an axis label for each data item. That is likely
> not suitable for your chart.
>
> Correct, I'm using a line chart (i.e. the data I have are a number of
> time series). Thanks for the info about how it behaves.
>
>
> You should try using an XY-chart.
>
> Thanks. Yes, that seems to work better. It's a lot faster as well :)
>
>
> In case you need different positions inside one day, you should make
> a separate column with added date and time values. Then use this
> column for your x-axis.
>
> Yes, I'd created the column and will use it.
>
>
>
> The Y-axis is reasonable from 0 to 120, although it would be nice to
> trim the blank space at the top a bit, since the data can't exceed
> 100.
>
> I haven't found any way to select individual points on the chart to
> display the detailed value of that point or all the values at that
> time.  
>
> Access to single points is only available in edit mode of the chart.
> The information of the point is shown as tooltip.
>
> I don't see any tooltips?
>
>
> You can display the value of data points as data label. Enable it in
> edit mode in menu Insert > Data Label, or for a distinct point in its
> context menu "Insert Single Data Label".
>
> In case you use an XY-chart you can generate a text column for labels
> Such are called 'category' in the dialogs. But there is only one text
> per row possible.
>
>   Nor do I see a way to temporarily hide individual data lines to
>
> more clearly see other particular lines, for example.  
>
> You can hide the associated column, then the line is removed from the
> chart. When you show the column, the line is drawn again.
>
> OK, thanks. That works but is a bit laborious.
>
>
>
> If there's any way to do these things with an LO chart, and to
> speed it up, I'd be grateful for any pointers. Or if there are
> better options out there that can read .ods or .csv and display
> charts, that would also be interesting.  
>
> It seems you want to explore your data. Some people have pointed to
> the R-project. But I have never used it. Or you might find suitable
> tools with search term 'data mining'.
>
> Thanks, I'll have a look. I might import the data into my emoncms
> system.
>
>
> Kind regards
> Regina
>
>

--
To unsubscribe e-mail to: [hidden email]
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy
Luuk Luuk
Reply | Threaded
Open this post in threaded view
|

Re: what's the best way to make charts of spreadsheet data

In reply to this post by Regina Henschel

On 4-1-2019 18:54, Regina Henschel wrote:

> Hi Dave,
>
> Dave Howorth schrieb am 02-Jan-19 um 18:12:
>> [..]
>>
>> My spreadsheet data is in columns. Column A is the dates and column B
>> is the times. Then ten columns of humidity data and eleven columns of
>> temperature data. There are currently just over 5000 rows of samples.
>>
>> Making the chart was extremely slow. Every time I changed the Name of a
>> line of the chart I had to wait whilst it cycled through several steps
>> to make the change. Initially when I removed some empty columns from
>> the data range, it took an age to do so. etc.
>
> Each data item produces an object, which is selectable in edit mode of
> the chart. Your data produce 5000*(10+11)=105000 objects. That cannot
> be fast.
>
> What is the purpose of the chart? You do not need 105000 objects to
> illustrate something.
>
> With 5000 items in x-direction a lot of them will be on top of others.
> My screen has only 1920 dots width and yours?
>
The fact that a screen has only (a limited) number of pixels, has
NOTHING to do with how many items are used, or can be used,  in a graph.

Maybe the user has a A0-plotter to print this graph?

This should still be possible with software like LibreOffice, or the
should kill the option to create graph's....


just 2 cents


--
To unsubscribe e-mail to: [hidden email]
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy
Windows7 / LibreOffice  3.3 330m19(Build:6)
Dave Howorth Dave Howorth
Reply | Threaded
Open this post in threaded view
|

Re: what's the best way to make charts of spreadsheet data

On Sat, 5 Jan 2019 10:50:42 +0100
Luuk <[hidden email]> wrote:

> On 4-1-2019 18:54, Regina Henschel wrote:
> > Hi Dave,
> >
> > Dave Howorth schrieb am 02-Jan-19 um 18:12:  
> >> [..]
> >>
> >> My spreadsheet data is in columns. Column A is the dates and
> >> column B is the times. Then ten columns of humidity data and
> >> eleven columns of temperature data. There are currently just over
> >> 5000 rows of samples.
> >>
> >> Making the chart was extremely slow. Every time I changed the Name
> >> of a line of the chart I had to wait whilst it cycled through
> >> several steps to make the change. Initially when I removed some
> >> empty columns from the data range, it took an age to do so. etc.  
> >
> > Each data item produces an object, which is selectable in edit mode
> > of the chart. Your data produce 5000*(10+11)=105000 objects. That
> > cannot be fast.
> >
> > What is the purpose of the chart? You do not need 105000 objects to
> > illustrate something.
> >
> > With 5000 items in x-direction a lot of them will be on top of
> > others. My screen has only 1920 dots width and yours?
> >  
> The fact that a screen has only (a limited) number of pixels, has
> NOTHING to do with how many items are used, or can be used,  in a
> graph.

You're clearly not a programmer.

> Maybe the user has a A0-plotter to print this graph?

In which case the resolution of the output device is greater.

> This should still be possible with software like LibreOffice, or the
> should kill the option to create graph's....

> just 2 cents

My 2p

--
To unsubscribe e-mail to: [hidden email]
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy
Luuk Luuk
Reply | Threaded
Open this post in threaded view
|

Re: what's the best way to make charts of spreadsheet data


On 5-1-2019 12:29, Dave Howorth wrote:
> On Sat, 5 Jan 2019 10:50:42 +0100
> Luuk <[hidden email]> wrote:
> The fact that a screen has only (a limited) number of pixels, has
> NOTHING to do with how many items are used, or can be used,  in a
> graph.
> You're clearly not a programmer.

I, indeed, fail to see the logic in this conclusion.



--
To unsubscribe e-mail to: [hidden email]
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy
Windows7 / LibreOffice  3.3 330m19(Build:6)
Regina Henschel Regina Henschel
Reply | Threaded
Open this post in threaded view
|

Re: what's the best way to make charts of spreadsheet data

In reply to this post by Luuk
Hi Luuk,

I have started with "What is the purpose of the chart?"

Kind regards
Regina

--
To unsubscribe e-mail to: [hidden email]
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy