Hi everyone,
I am a beginner new to QuickSight. My original data consisted of a column which contained a list of numerical values (for dummy purposes, ages). Unfortunately, I ran into issues importing the dataset when it contained a column that represented a list in each sample. So, I made each value in ages a separate column. So now I have columns Age 1, Age 2, so on and so forth. I would like to visualize this list on a line plot. The issue is that I do not have an x-axis. Ideally, the line plot values would have data points (1, Age 1), (2, Age 2), etc.
How can I go about doing this? I tried creating a calculated field using the dense_rank function to no avail. Am I thinking about this the wrong way? I would appreciate any help.
Hi @slefcourt - Welcome to AWS QuickSight and thanks for posting the question. Before building the analysis in QS, can you shared some details around the data and provide sample input and expected output. I believe you have to make one column called age and put requested values in one column so that you can build the graph.
Regards - Sanjeeb
1 Like
Hi @Sanjeeb2022 thank you for your response and my apologies for the delay! I had tried several workarounds to no avail.
This is a mock data but resembles the same structure as my use case. Each row represents a classroom. A column âagesâ corresponds to a list of the studentsâ ages in the class. In this example each classroom has 7 students. I had initially tried loading this data however SPICE did not recognize the column which was now represented as a stringified list after I had saved my dataframe using .to_csv. I then tried saving the dataframe as a JSON, but QuickSight said the âThe number of fields in your dataset exceeds Amazon QuickSightâs limitsâ (because list is not a supported data type in QuickSight I believe?). This forced me to separate each element in the ages column to a column. So now the dataset has seven columns each corresponding to Age 1, Age 2, âŚ, Age 7.
You mention putting the list values in one column. Could you please elaborate? That would certainly make things easier than splitting each element into an individual column, I just had issues when I imported the data this way.
I want to take the average across my rows in these columns and then create a line plot. The next step from there is create some mechanism to visualize the maximum and minimum across these rows for the ages.
Thank you for your help.
Hello @slefcourt .
Thanks for sharing details of your data structure.
From what I understand your table needs to have the age values split and then pivoted to contain a structure similar to this (just added some of the values for simplicity).
| Age |
Class |
| 14 |
0 |
| 17 |
0 |
| 10 |
0 |
| 16 |
3 |
| 11 |
3 |
And so on for Quicksight to be able to have Age as a measure (that you will aggregate with a count) and Class as a dimension (that will be represented in the X axis) if you use an histogram for example.
QuickSight does not support pivoting data natively but you can use other services in the AWS ecosystem such as AWS DataBrew - Visual interface or Glue ETL - Scripting if you need to automate complex transformations of your data before consuming (visualizing) it.
Hope it helps.
Thank you for posting into the QS community!
1 Like
Thank you, this does help a lot. I will try applying this concept to my data and re-open if I have further questions.
1 Like
