Calc pie chart for 25000 rows with long tail
I have a file that contains 25,000 rows, each row containing a number, looking like this:
4000
3000
1500
700
...
2
1
1
1
From this I want to make a pie chart.
The first few pie parts will be clearly visible (ideally with labels), and the long tail (the last 90% of the values are very small) will form an indistinct area that must have no labels, because showing that many labels would take the whole page.
How to create such a pie chart with LibreOffice (or OpenOffice) Calc?
When I use the normal pie chart with default options Calc becomes ultra-slow before crashing (Ubuntu 2018.04)
charts libreoffice openoffice libreoffice-calc openoffice-calc
asked Jan 30 at 5:09
Nicolas RaoulNicolas Raoul
4,092134082
|
show 2 more comments
I have a file that contains 25,000 rows, each row containing a number, looking like this:
4000
3000
1500
700
...
2
1
1
1
From this I want to make a pie chart.
The first few pie parts will be clearly visible (ideally with labels), and the long tail (the last 90% of the values are very small) will form an indistinct area that must have no labels, because showing that many labels would take the whole page.
How to create such a pie chart with LibreOffice (or OpenOffice) Calc?
When I use the normal pie chart with default options Calc becomes ultra-slow before crashing (Ubuntu 2018.04)
charts libreoffice openoffice libreoffice-calc openoffice-calc
asked Jan 30 at 5:09
Nicolas RaoulNicolas Raoul
4,092134082
I'm pretty sure, showing that many labels would take several pages if you wanted them to be at all legible.
– Ed Grimm
Jan 30 at 6:14
I think it's illegal to create a pie chart with 25,000 slices. :-)
– fixer1234
Jan 30 at 6:37
If you have 25,000 rows, then the long tails items amount to less that one arc-minute (1/60 of a degree). Unless your pie chart is truly of humongus proportion, there is no way this will show up. You would be better off merging them into a "misc." category.
– cybernetic.nomad
Jan 31 at 12:15
@cybernetic.nomad: Each will not show up, of course, but little by little they form about half of the pie. Grouping everything below 13 (for instance) into a "misc" category would arbitrary, harder to grasp, and introduce Calc complexity (one more parameter to keep in mind when adding new data).
– Nicolas Raoul
Jan 31 at 12:18
1) You have a problem generating the chart, 2) it will result in a slice of a single homogenous colour (grey) once your reach a tail ±100 items long 3) To put it in perspective, if your chart is 10 meters in diameter, a slice of1will me ±1 mm wide at the edge. 4) the obvious solution is to concatenate small values into a single category -- a table with the data to display can be generated with a formula -- so no need to take any additional parameter into account. when adding data
– cybernetic.nomad
Jan 31 at 12:45
|
show 2 more comments
I have a file that contains 25,000 rows, each row containing a number, looking like this:
4000
3000
1500
700
...
2
1
1
1
From this I want to make a pie chart.
The first few pie parts will be clearly visible (ideally with labels), and the long tail (the last 90% of the values are very small) will form an indistinct area that must have no labels, because showing that many labels would take the whole page.
How to create such a pie chart with LibreOffice (or OpenOffice) Calc?
When I use the normal pie chart with default options Calc becomes ultra-slow before crashing (Ubuntu 2018.04)
charts libreoffice openoffice libreoffice-calc openoffice-calc
asked Jan 30 at 5:09
Nicolas RaoulNicolas Raoul
4,092134082
I have a file that contains 25,000 rows, each row containing a number, looking like this:
4000
3000
1500
700
...
2
1
1
1
From this I want to make a pie chart.
The first few pie parts will be clearly visible (ideally with labels), and the long tail (the last 90% of the values are very small) will form an indistinct area that must have no labels, because showing that many labels would take the whole page.
How to create such a pie chart with LibreOffice (or OpenOffice) Calc?
When I use the normal pie chart with default options Calc becomes ultra-slow before crashing (Ubuntu 2018.04)
charts libreoffice openoffice libreoffice-calc openoffice-calc
charts libreoffice openoffice libreoffice-calc openoffice-calc
asked Jan 30 at 5:09
Nicolas RaoulNicolas Raoul
4,092134082
asked Jan 30 at 5:09
Nicolas RaoulNicolas Raoul
4,092134082
asked Jan 30 at 5:09
Nicolas RaoulNicolas Raoul
4,092134082
asked Jan 30 at 5:09
Nicolas RaoulNicolas Raoul
4,092134082
asked Jan 30 at 5:09
Nicolas RaoulNicolas Raoul
4,092134082
4,092134082
I'm pretty sure, showing that many labels would take several pages if you wanted them to be at all legible.
– Ed Grimm
Jan 30 at 6:14
I think it's illegal to create a pie chart with 25,000 slices. :-)
– fixer1234
Jan 30 at 6:37
If you have 25,000 rows, then the long tails items amount to less that one arc-minute (1/60 of a degree). Unless your pie chart is truly of humongus proportion, there is no way this will show up. You would be better off merging them into a "misc." category.
– cybernetic.nomad
Jan 31 at 12:15
@cybernetic.nomad: Each will not show up, of course, but little by little they form about half of the pie. Grouping everything below 13 (for instance) into a "misc" category would arbitrary, harder to grasp, and introduce Calc complexity (one more parameter to keep in mind when adding new data).
– Nicolas Raoul
Jan 31 at 12:18
1) You have a problem generating the chart, 2) it will result in a slice of a single homogenous colour (grey) once your reach a tail ±100 items long 3) To put it in perspective, if your chart is 10 meters in diameter, a slice of1will me ±1 mm wide at the edge. 4) the obvious solution is to concatenate small values into a single category -- a table with the data to display can be generated with a formula -- so no need to take any additional parameter into account. when adding data
– cybernetic.nomad
Jan 31 at 12:45
|
show 2 more comments
I'm pretty sure, showing that many labels would take several pages if you wanted them to be at all legible.
– Ed Grimm
Jan 30 at 6:14
I think it's illegal to create a pie chart with 25,000 slices. :-)
– fixer1234
Jan 30 at 6:37
If you have 25,000 rows, then the long tails items amount to less that one arc-minute (1/60 of a degree). Unless your pie chart is truly of humongus proportion, there is no way this will show up. You would be better off merging them into a "misc." category.
– cybernetic.nomad
Jan 31 at 12:15
@cybernetic.nomad: Each will not show up, of course, but little by little they form about half of the pie. Grouping everything below 13 (for instance) into a "misc" category would arbitrary, harder to grasp, and introduce Calc complexity (one more parameter to keep in mind when adding new data).
– Nicolas Raoul
Jan 31 at 12:18
1) You have a problem generating the chart, 2) it will result in a slice of a single homogenous colour (grey) once your reach a tail ±100 items long 3) To put it in perspective, if your chart is 10 meters in diameter, a slice of1will me ±1 mm wide at the edge. 4) the obvious solution is to concatenate small values into a single category -- a table with the data to display can be generated with a formula -- so no need to take any additional parameter into account. when adding data
– cybernetic.nomad
Jan 31 at 12:45
I'm pretty sure, showing that many labels would take several pages if you wanted them to be at all legible.
– Ed Grimm
Jan 30 at 6:14
I'm pretty sure, showing that many labels would take several pages if you wanted them to be at all legible.
– Ed Grimm
Jan 30 at 6:14
I think it's illegal to create a pie chart with 25,000 slices. :-)
– fixer1234
Jan 30 at 6:37
I think it's illegal to create a pie chart with 25,000 slices. :-)
– fixer1234
Jan 30 at 6:37
If you have 25,000 rows, then the long tails items amount to less that one arc-minute (1/60 of a degree). Unless your pie chart is truly of humongus proportion, there is no way this will show up. You would be better off merging them into a "misc." category.
– cybernetic.nomad
Jan 31 at 12:15
If you have 25,000 rows, then the long tails items amount to less that one arc-minute (1/60 of a degree). Unless your pie chart is truly of humongus proportion, there is no way this will show up. You would be better off merging them into a "misc." category.
– cybernetic.nomad
Jan 31 at 12:15
@cybernetic.nomad: Each will not show up, of course, but little by little they form about half of the pie. Grouping everything below 13 (for instance) into a "misc" category would arbitrary, harder to grasp, and introduce Calc complexity (one more parameter to keep in mind when adding new data).
– Nicolas Raoul
Jan 31 at 12:18
@cybernetic.nomad: Each will not show up, of course, but little by little they form about half of the pie. Grouping everything below 13 (for instance) into a "misc" category would arbitrary, harder to grasp, and introduce Calc complexity (one more parameter to keep in mind when adding new data).
– Nicolas Raoul
Jan 31 at 12:18
1) You have a problem generating the chart, 2) it will result in a slice of a single homogenous colour (grey) once your reach a tail ±100 items long 3) To put it in perspective, if your chart is 10 meters in diameter, a slice of
1 will me ±1 mm wide at the edge. 4) the obvious solution is to concatenate small values into a single category -- a table with the data to display can be generated with a formula -- so no need to take any additional parameter into account. when adding data– cybernetic.nomad
Jan 31 at 12:45
1) You have a problem generating the chart, 2) it will result in a slice of a single homogenous colour (grey) once your reach a tail ±100 items long 3) To put it in perspective, if your chart is 10 meters in diameter, a slice of
1 will me ±1 mm wide at the edge. 4) the obvious solution is to concatenate small values into a single category -- a table with the data to display can be generated with a formula -- so no need to take any additional parameter into account. when adding data– cybernetic.nomad
Jan 31 at 12:45
|
show 2 more comments
1 Answer
1
active
oldest
votes
The OP's computer lacks the processing power to handle this many slices. At some small number value, slices will become indistinguishable and create essentially a solid-looking slice. So as a practical matter, crunching all 25,000 values doesn't really buy anything. You can achieve the same result by finding the smallest value that is distinguishable and making that the last discrete value, then aggregating everything smaller into a residual slice.
That can be done by trial and error. Use a variable to select the smallest size, and plot it. Make the variable successively smaller until slices that size are no longer distinguishable. Here's a way to do that:
Pick a starting cutoff value and stick it in E1. For this example, the value is 13. When you see the size of the resulting slice, that will give you an idea of how much to adjust it for the next iteration.
Assuming your data is in columns
AandB, sorted in descending order (as in your example) enter the following formula inE2:
=B2
Enter the following formula in
E3and populate down until it returns empty cells:
=IF(B2 < $E$1,"",IF(B3 >= $E$1,B3,SUM(B$2:B$25000)-SUM(E$2:E2)))
Enter the following formula in
D2and populate down:
=IF(E2 = "","",IF(B2=E2,A2,"Misc."))
You should now have something that looks like this:
Which will then allow you to generate a chart looking like this:
"Rinse and repeat", adjusting the cutoff value in E1 until smaller values don't contribute anything visible.
edited Feb 4 at 7:17
fixer1234
19k144982
answered Feb 2 at 2:12
cybernetic.nomadcybernetic.nomad
2,481517
Thanks for the edits, they are an improvement. Note that in comments, OP says "Your point "2" is exactly what I want " in response to "2) it will result in a slice of a single homogenous colour (grey) once your reach a tail ±100 items long" -- so rthat's what I decided to respond to even though there is a contradiction between OP's statements... ¯_(ツ)_/¯
– cybernetic.nomad
Feb 4 at 14:03
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "3"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1399944%2fcalc-pie-chart-for-25000-rows-with-long-tail%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
The OP's computer lacks the processing power to handle this many slices. At some small number value, slices will become indistinguishable and create essentially a solid-looking slice. So as a practical matter, crunching all 25,000 values doesn't really buy anything. You can achieve the same result by finding the smallest value that is distinguishable and making that the last discrete value, then aggregating everything smaller into a residual slice.
That can be done by trial and error. Use a variable to select the smallest size, and plot it. Make the variable successively smaller until slices that size are no longer distinguishable. Here's a way to do that:
Pick a starting cutoff value and stick it in E1. For this example, the value is 13. When you see the size of the resulting slice, that will give you an idea of how much to adjust it for the next iteration.
Assuming your data is in columns
AandB, sorted in descending order (as in your example) enter the following formula inE2:
=B2
Enter the following formula in
E3and populate down until it returns empty cells:
=IF(B2 < $E$1,"",IF(B3 >= $E$1,B3,SUM(B$2:B$25000)-SUM(E$2:E2)))
Enter the following formula in
D2and populate down:
=IF(E2 = "","",IF(B2=E2,A2,"Misc."))
You should now have something that looks like this:
Which will then allow you to generate a chart looking like this:
"Rinse and repeat", adjusting the cutoff value in E1 until smaller values don't contribute anything visible.
edited Feb 4 at 7:17
fixer1234
19k144982
answered Feb 2 at 2:12
cybernetic.nomadcybernetic.nomad
2,481517
Thanks for the edits, they are an improvement. Note that in comments, OP says "Your point "2" is exactly what I want " in response to "2) it will result in a slice of a single homogenous colour (grey) once your reach a tail ±100 items long" -- so rthat's what I decided to respond to even though there is a contradiction between OP's statements... ¯_(ツ)_/¯
– cybernetic.nomad
Feb 4 at 14:03
add a comment |
The OP's computer lacks the processing power to handle this many slices. At some small number value, slices will become indistinguishable and create essentially a solid-looking slice. So as a practical matter, crunching all 25,000 values doesn't really buy anything. You can achieve the same result by finding the smallest value that is distinguishable and making that the last discrete value, then aggregating everything smaller into a residual slice.
That can be done by trial and error. Use a variable to select the smallest size, and plot it. Make the variable successively smaller until slices that size are no longer distinguishable. Here's a way to do that:
Pick a starting cutoff value and stick it in E1. For this example, the value is 13. When you see the size of the resulting slice, that will give you an idea of how much to adjust it for the next iteration.
Assuming your data is in columns
AandB, sorted in descending order (as in your example) enter the following formula inE2:
=B2
Enter the following formula in
E3and populate down until it returns empty cells:
=IF(B2 < $E$1,"",IF(B3 >= $E$1,B3,SUM(B$2:B$25000)-SUM(E$2:E2)))
Enter the following formula in
D2and populate down:
=IF(E2 = "","",IF(B2=E2,A2,"Misc."))
You should now have something that looks like this:
Which will then allow you to generate a chart looking like this:
"Rinse and repeat", adjusting the cutoff value in E1 until smaller values don't contribute anything visible.
edited Feb 4 at 7:17
fixer1234
19k144982
answered Feb 2 at 2:12
cybernetic.nomadcybernetic.nomad
2,481517
Thanks for the edits, they are an improvement. Note that in comments, OP says "Your point "2" is exactly what I want " in response to "2) it will result in a slice of a single homogenous colour (grey) once your reach a tail ±100 items long" -- so rthat's what I decided to respond to even though there is a contradiction between OP's statements... ¯_(ツ)_/¯
– cybernetic.nomad
Feb 4 at 14:03
add a comment |
The OP's computer lacks the processing power to handle this many slices. At some small number value, slices will become indistinguishable and create essentially a solid-looking slice. So as a practical matter, crunching all 25,000 values doesn't really buy anything. You can achieve the same result by finding the smallest value that is distinguishable and making that the last discrete value, then aggregating everything smaller into a residual slice.
That can be done by trial and error. Use a variable to select the smallest size, and plot it. Make the variable successively smaller until slices that size are no longer distinguishable. Here's a way to do that:
Pick a starting cutoff value and stick it in E1. For this example, the value is 13. When you see the size of the resulting slice, that will give you an idea of how much to adjust it for the next iteration.
Assuming your data is in columns
AandB, sorted in descending order (as in your example) enter the following formula inE2:
=B2
Enter the following formula in
E3and populate down until it returns empty cells:
=IF(B2 < $E$1,"",IF(B3 >= $E$1,B3,SUM(B$2:B$25000)-SUM(E$2:E2)))
Enter the following formula in
D2and populate down:
=IF(E2 = "","",IF(B2=E2,A2,"Misc."))
You should now have something that looks like this:
Which will then allow you to generate a chart looking like this:
"Rinse and repeat", adjusting the cutoff value in E1 until smaller values don't contribute anything visible.
edited Feb 4 at 7:17
fixer1234
19k144982
answered Feb 2 at 2:12
cybernetic.nomadcybernetic.nomad
2,481517
The OP's computer lacks the processing power to handle this many slices. At some small number value, slices will become indistinguishable and create essentially a solid-looking slice. So as a practical matter, crunching all 25,000 values doesn't really buy anything. You can achieve the same result by finding the smallest value that is distinguishable and making that the last discrete value, then aggregating everything smaller into a residual slice.
That can be done by trial and error. Use a variable to select the smallest size, and plot it. Make the variable successively smaller until slices that size are no longer distinguishable. Here's a way to do that:
Pick a starting cutoff value and stick it in E1. For this example, the value is 13. When you see the size of the resulting slice, that will give you an idea of how much to adjust it for the next iteration.
Assuming your data is in columns
AandB, sorted in descending order (as in your example) enter the following formula inE2:
=B2
Enter the following formula in
E3and populate down until it returns empty cells:
=IF(B2 < $E$1,"",IF(B3 >= $E$1,B3,SUM(B$2:B$25000)-SUM(E$2:E2)))
Enter the following formula in
D2and populate down:
=IF(E2 = "","",IF(B2=E2,A2,"Misc."))
You should now have something that looks like this:
Which will then allow you to generate a chart looking like this:
"Rinse and repeat", adjusting the cutoff value in E1 until smaller values don't contribute anything visible.
edited Feb 4 at 7:17
fixer1234
19k144982
answered Feb 2 at 2:12
cybernetic.nomadcybernetic.nomad
2,481517
edited Feb 4 at 7:17
fixer1234
19k144982
edited Feb 4 at 7:17
fixer1234
19k144982
edited Feb 4 at 7:17
fixer1234
19k144982
19k144982
answered Feb 2 at 2:12
cybernetic.nomadcybernetic.nomad
2,481517
answered Feb 2 at 2:12
cybernetic.nomadcybernetic.nomad
2,481517
answered Feb 2 at 2:12
cybernetic.nomadcybernetic.nomad
2,481517
2,481517
Thanks for the edits, they are an improvement. Note that in comments, OP says "Your point "2" is exactly what I want " in response to "2) it will result in a slice of a single homogenous colour (grey) once your reach a tail ±100 items long" -- so rthat's what I decided to respond to even though there is a contradiction between OP's statements... ¯_(ツ)_/¯
– cybernetic.nomad
Feb 4 at 14:03
add a comment |
Thanks for the edits, they are an improvement. Note that in comments, OP says "Your point "2" is exactly what I want " in response to "2) it will result in a slice of a single homogenous colour (grey) once your reach a tail ±100 items long" -- so rthat's what I decided to respond to even though there is a contradiction between OP's statements... ¯_(ツ)_/¯
– cybernetic.nomad
Feb 4 at 14:03
Thanks for the edits, they are an improvement. Note that in comments, OP says "Your point "2" is exactly what I want " in response to "2) it will result in a slice of a single homogenous colour (grey) once your reach a tail ±100 items long" -- so rthat's what I decided to respond to even though there is a contradiction between OP's statements... ¯_(ツ)_/¯
– cybernetic.nomad
Feb 4 at 14:03
Thanks for the edits, they are an improvement. Note that in comments, OP says "Your point "2" is exactly what I want " in response to "2) it will result in a slice of a single homogenous colour (grey) once your reach a tail ±100 items long" -- so rthat's what I decided to respond to even though there is a contradiction between OP's statements... ¯_(ツ)_/¯
– cybernetic.nomad
Feb 4 at 14:03
add a comment |
Thanks for contributing an answer to Super User!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1399944%2fcalc-pie-chart-for-25000-rows-with-long-tail%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
I'm pretty sure, showing that many labels would take several pages if you wanted them to be at all legible.
– Ed Grimm
Jan 30 at 6:14
I think it's illegal to create a pie chart with 25,000 slices. :-)
– fixer1234
Jan 30 at 6:37
If you have 25,000 rows, then the long tails items amount to less that one arc-minute (1/60 of a degree). Unless your pie chart is truly of humongus proportion, there is no way this will show up. You would be better off merging them into a "misc." category.
– cybernetic.nomad
Jan 31 at 12:15
@cybernetic.nomad: Each will not show up, of course, but little by little they form about half of the pie. Grouping everything below 13 (for instance) into a "misc" category would arbitrary, harder to grasp, and introduce Calc complexity (one more parameter to keep in mind when adding new data).
– Nicolas Raoul
Jan 31 at 12:18
1) You have a problem generating the chart, 2) it will result in a slice of a single homogenous colour (grey) once your reach a tail ±100 items long 3) To put it in perspective, if your chart is 10 meters in diameter, a slice of
1will me ±1 mm wide at the edge. 4) the obvious solution is to concatenate small values into a single category -- a table with the data to display can be generated with a formula -- so no need to take any additional parameter into account. when adding data– cybernetic.nomad
Jan 31 at 12:45