Bug 94872 - Display of points in xy (scatter) chart as bars/columns
Summary: Display of points in xy (scatter) chart as bars/columns
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
5.0.0.2 rc
Hardware: All All
: low enhancement
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks: Chart-Enhancements
  Show dependency treegraph
 
Reported: 2015-10-07 23:21 UTC by thoskk
Modified: 2018-05-23 18:24 UTC (History)
2 users (show)

See Also:
Crash report or crash signature:


Attachments
Example for xy-scatter bar (taken from chromatogram simulator) (111.27 KB, image/jpeg)
2015-10-11 13:06 UTC, thoskk
Details
xy-scattered colums from LO (103.54 KB, image/jpeg)
2015-10-11 13:10 UTC, thoskk
Details
demonstration of another workaround (13.31 KB, application/vnd.oasis.opendocument.spreadsheet)
2015-10-11 14:36 UTC, Jean-Baptiste Faure
Details
xy-scatter graph for example (98.32 KB, image/jpeg)
2015-10-11 14:44 UTC, thoskk
Details

Note You need to log in before you can comment on or make changes to this bug.
Description thoskk 2015-10-07 23:21:16 UTC
Hi folks, 

I need an additional feature/setting for xy charts: The data points shall be displayed as (formattable) bars or columns. This means, make a bar/column chart, but with the bars/columns arranged according to given values on the x-axis.

I could not find a way to do this with the given options. 

Regards,
Thoskk
Comment 1 thoskk 2015-10-07 23:28:04 UTC
oh, and I forgot one thing: It would be perfect if the bars/columns could be displayed as Gaussian curves (with width corresponding to hight).
Comment 2 Buovjaga 2015-10-10 13:08:32 UTC
Ok, let's accept this, but low priority.

Question from a developer on IRC: what happens if two bars have the same x value?
Comment 3 thoskk 2015-10-10 13:41:56 UTC
> Question from a developer on IRC: what happens if two bars have the same x
> value?
xy-scatters don't make much sense if two identical x-values are present. If so, the second one belongs to a different x-axis.

BTW: You should keep in mind that there are many scientific applications for this. All measurements that produce signals over time could be displayed in the desired way (since they're not necessarily coherent and thus can't be displayed by a graph). But it's also conceivable to present economical results (though not in Gaussian curves, of course). 
And since no other spreadsheet software has this feature, I'd call this as a greater improvement, maybe worth a higher priority. I think it's about time to start keeping scientist in mind and stop focussing on economists only. 

There is some software called "chromatogram-simulator.exe", available on the NIST webpage (http://www.nist.gov/mml/csd/organic/simchromdata.cfm), that does similar. However, it's only available for Win XP and 7, and you can only enter 10 x-values, and it can't do anything else than simulating the peaks in different chromatographic environments.
Comment 4 Jean-Baptiste Faure 2015-10-10 22:05:42 UTC
(In reply to thoskk from comment #3)
> > Question from a developer on IRC: what happens if two bars have the same x
> > value?
> xy-scatters don't make much sense if two identical x-values are present. If
> so, the second one belongs to a different x-axis.

Really? Did you never make chart of jump function?

Please, could you attach a screen copy of a xy (scatter) chart as bars/columns?

If I understand your request correctly, I think you can do such a chart in LO:
1/ assuming x values are in column A and y values in column B, from x-data, compute an x-step which divides each [x_i ; x_i+1] interval
2/ create in column C a new x data column with this step
3/ for each x value in column C available in column A set the corresponding y value in column D
4/ for each x value in C not present in column A, set the corresponding y value in column D to zero
You can use lookup function to do 3 and 4.
5/ Now you have xy data with constant x step and you can create a bar chart with columns C and D.

Best regards. JBF
Comment 5 thoskk 2015-10-11 13:06:32 UTC
Created attachment 119509 [details]
Example for xy-scatter bar (taken from chromatogram simulator)

in lower part of screenshot you can see the result of the simulation.
Comment 6 thoskk 2015-10-11 13:10:44 UTC
Created attachment 119510 [details]
xy-scattered colums from LO

note hidden lines and keep in mind that it needs several hundred lines to enter values for graph
Comment 7 Jean-Baptiste Faure 2015-10-11 14:36:33 UTC
Created attachment 119514 [details]
demonstration of another workaround

(In reply to thoskk from comment #6)
> Created attachment 119510 [details]
> xy-scattered colums from LO
> 
> note hidden lines and keep in mind that it needs several hundred lines to
> enter values for graph

Indeed, it is not the easiest workaround in this case.
Here is another one. The idea is to surround each value (x,y) by 2 zero values for the same x.

Best regards. JBF
Comment 8 thoskk 2015-10-11 14:43:14 UTC
(In reply to Jean-Baptiste Faure from comment #4)
> (In reply to thoskk from comment #3)
> > > Question from a developer on IRC: what happens if two bars have the same x
> > > value?
> > xy-scatters don't make much sense if two identical x-values are present. If
> > so, the second one belongs to a different x-axis.
> 
> Really? Did you never make chart of jump function?
If I'm not completely wrong, even a jump function (or a log function) doesn't give two different y-values for one identical x-value: 
y = n1 | x >  m
y = n2 | x <= m
(or alike)

If you have results from measurements that tell different, the reason is most probably the measuring tolerance of the system.
 
> Please, could you attach a screen copy of a xy (scatter) chart as
> bars/columns?
Attached two screenshots. First one ("Example for xy-scatter bar (taken from chromatogram simulator") is a good example of how it could look. The program used has more specific options for the task of simulating chromatographic data, but is restricted to 10 entries. 

> If I understand your request correctly, I think you can do such a chart in
> LO:
> 1/ assuming x values are in column A and y values in column B, from x-data,
> compute an x-step which divides each [x_i ; x_i+1] interval
> 2/ create in column C a new x data column with this step
> 3/ for each x value in column C available in column A set the corresponding
> y value in column D
> 4/ for each x value in C not present in column A, set the corresponding y
> value in column D to zero
> You can use lookup function to do 3 and 4.
> 5/ Now you have xy data with constant x step and you can create a bar chart
> with columns C and D.
Please see second attachment (xy-scattered colums from LO). 
Not sure if I understood your advice correctly, since I can't see how to assign a separate y-value to each x-value. 
In addition, how can display of x-axis be set to give reasonable, legible intervals?
I'm afraid this method wouln't work, though, since already for this small simulation more than 600 lines of values are necessary. 
Imagine a simulation of 60 minutes measurement, with increments of 0.001 minutes (totally realistic values for chromatography).  This would result in 60,000 lines of values. And besides you would have to enter at least the starting values by hand, LO would maybe soon hit it's limits for some alterations (e.g., when applying functions to simulate different measurement conditions). 

I made some experiments myself, and the only way to have a similar result as given in attachment 1 is: 
1) Enter values by hand (e.g., column A representing the x-values, or minutes, in this case, and column B representing corresponding y-values)
2) Adding two new lines between every line of x-values
3) Calculate an increment for every x, according to your demands (e.g., x-0.01 before and x+0.01 after each x)
4) set corresponding y-values to 0
5) use data to plot xy-scatter graph
The result is displayed in the third attachment ("xy-scatter graph").

However, for a series of e.g. 60 minutes measurement with one measuring point every 0.001 minutes, it's very tedious to enter the data. If you copy the values from e.g. an ascii-file, there is no way to automatically enter two new, empty lines after each given line (at least I could not find any function that would allow to do so). This would mean, you had to insert 120,000 new lines by hand. And since selected cells or lines by ctrl-click and shift-click are not handled differently (see M$ Office for reference), one had to click at least 180,000 times.
Consider 4 - 5 of these measurement series a day, and you would spend your time doing nothing but clicking. 

Cheers,
Thoskk
Comment 9 thoskk 2015-10-11 14:44:15 UTC
Created attachment 119515 [details]
xy-scatter graph for example
Comment 10 thoskk 2015-10-11 14:46:55 UTC
(In reply to Jean-Baptiste Faure from comment #7)
> Created attachment 119514 [details]
> demonstration of another workaround
> 
> (In reply to thoskk from comment #6)
> > Created attachment 119510 [details]
> > xy-scattered colums from LO
> > 
> > note hidden lines and keep in mind that it needs several hundred lines to
> > enter values for graph
> 
> Indeed, it is not the easiest workaround in this case.
> Here is another one. The idea is to surround each value (x,y) by 2 zero
> values for the same x.
> 
> Best regards. JBF

Guess my third attachmend is close to your example. Though, same restrictions would apply for greater datasets.