How do you make a scatter plot from this excel file about cancer rate and age?
Hi all, I am quite new to data viz and having a hard time to adopt. I want to make a scatter plot from the following incidental rate of cancer, depends on the age and part (e.g. throat, lung)
Unfortunately i could not attach the file at the moment, I don't know if it helps but following is the part of data set I am using.
As you can see, the ages are listed horizontal, parts and rate per age are listed vertical. I am not sure how I can change into handy data to enable scatter plot.
On Tableau I could somehow use pivot to summarize the number but not on current Sisense so I am assuming that I have to do it on excel.
Any advice is appreciated!
| Code | Part | ICD-10 | Sex | Year | Rought Rate | 0-4歳 | 5-9歳 | 10-14歳 | 15-19歳 | 20-24歳 | 25-29歳 | 30-34歳 | 35-39歳 | 40-44歳 | 45-49歳 | 50-54歳 | 55-59歳 | 60-64歳 | 65-69歳 |
| 1 | All Part | C00-C96 | Total | 2016 | 784.0 | 20.2 | 10.3 | 10.8 | 16.1 | 26.2 | 44.9 | 80.6 | 137.2 | 226.4 | 339.9 | 498.4 | 731.7 | 1086.3 | 1541.6 |
| 1 | All Part | C00-C96 | Total | 2017 | 771.4 | 20.3 | 10.6 | 12.3 | 15.2 | 24.5 | 43.2 | 77.2 | 132.8 | 221.1 | 337.2 | 470.5 | 693.9 | 1048.7 | 1514.1 |
| 1 | All Part | C00-C96 | Total | 2018 | 775.7 | 18.5 | 10.4 | 12.2 | 16.2 | 24.4 | 44.7 | 79.4 | 130.3 | 217.2 | 331.5 | 466.7 | 689.1 | 1028.9 | 1494.8 |
| 1 | All Part | C00-C96 | Male | 2016 | 917.3 | 22.4 | 11.8 | 11.4 | 16.3 | 23.3 | 32.7 | 48.2 | 76.2 | 123.1 | 197.6 | 396.3 | 746.1 | 1308.8 | 2036.7 |
| 1 | All Part | C00-C96 | Male | 2017 | 906.4 | 21.4 | 11.8 | 12.8 | 13.7 | 21.3 | 30.6 | 45.4 | 72.1 | 119.3 | 201.6 | 370.6 | 702.9 | 1262.5 | 2009.1 |
| 1 | All Part | C00-C96 | Male | 2018 | 908.3 | 20.4 | 10.7 | 12.8 | 15.7 | 20.6 | 32.5 | 50.2 | 73.5 | 119.0 | 197.2 | 357.0 | 693.0 | 1228.5 | 1973.9 |
| 1 | All Part | C00-C96 | Female | 2016 | 657.5 | 18.0 | 8.7 | 10.1 | 15.8 | 29.3 | 57.7 | 114.0 | 200.0 | 332.5 | 485.0 | 601.3 | 717.3 | 870.2 | 1077.5 |
| 1 | All Part | C00-C96 | Female | 2017 | 643.4 | 19.1 | 9.3 | 11.8 | 16.7 | 27.9 | 56.3 | 110.1 | 195.2 | 325.6 | 475.5 | 571.5 | 685.0 | 840.9 | 1050.4 |
| 1 | All Part | C00-C96 | Female | 2018 | 650.1 | 16.5 | 10.0 | 11.6 | 16.6 | 28.4 | 57.6 | 109.7 | 188.7 | 318.1 | 468.6 | 577.6 | 685.2 | 834.6 | 1045.8 |
| 3 | 口腔・咽頭 | C00-C14 | Total | 2016 | 17.0 | 0.0 | 0.0 | 0.1 | 0.4 | 1.0 | 1.8 | 2.5 | 3.1 | 5.4 | 8.4 | 14.8 | 21.8 | 29.7 | 35.9 |
| 3 | 口腔・咽頭 | C00-C14 | Total | 2017 | 17.4 | 0.1 | 0.1 | 0.2 | 0.4 | 0.9 | 1.8 | 2.8 | 3.3 | 5.5 | 8.4 | 13.1 | 21.2 | 30.2 | 36.9 |
Are you using the Scatter Chart? I assume you want to represent the sums of each age group, grouped by part and then by year. If so, try adding the date to the X axis, the sum of the age groups to the Size field, and the part to the Break By field. You could also add part to the Y axis ... it depends how you want to visualize it. I hope this is helpful!
One thing I've picked up along the way I thought I would share is that it is often beneficial to start your widget as a pivot, get the data to look how you want it to look, and then switch the widget type to the more visual type. This doesn't always work, and didn't work for Scatter Chart, but it has helped me a lot in other scenarios.
Let us know how it goes!
