DEV Community: Ottava

Visualizing Multi-Dimensional Data in Action: Vehicle Ownership

Ottava — Mon, 15 Apr 2024 20:00:22 +0000

Did you know that the majority of American families own two vehicles? Surprisingly, there are approximately 5 million single-living individuals who own two or more vehicles. Let's dive into these fascinating insights through our Vehicle Ownership public chart.

Exploring numerical data through charts not only offers a visually engaging experience but also taps into our innate ability to process visual information swiftly, as research has shown. Charts provide a clear context and deeper insights into the underlying data compared to traditional numerical formats.

So, how did we create these insightful vehicle ownership charts using Ottava?

We began with our multi-dimensional data stored in an Excel file. You can access similar data from various sources such as the US Census Bureau, Kaggle, and Yahoo Finance, among others.

Once we had our data, we seamlessly transferred it to Ottava's pivot grid with a few simple steps:

Select the desired data table in Excel using Ctrl-C.
Navigate to Ottava's pivot grid and click on the dot in the upper-left corner.
Paste the data using Ctrl-V.

For detailed steps on using Ottava's Smart Copy/Paste.

Voila! Your data is now in Ottava, and the platform automatically suggests suitable charts based on the presented data in the right panel.

You don't need to perform any additional actions; Ottava streamlines the process for you.

If you wish to focus on specific aspects of the data, Ottava offers easy filtering options. For instance, you can select and filter data for 2-person and 3-person households by using Ctrl-click directly in the pivot grid. Ottava then generates corresponding charts, excluding other options.

For further customization or exploration, add the chart to "My View" and tweak it to your preferences.

You can change chart types, apply filters, and rearrange fields effortlessly. Ottava's intuitive interface allows you to swap X and Y axes or modify chart types from bar charts to pie charts seamlessly.

With Ottava, data exploration becomes a breeze. Uncover intriguing insights and discover hidden trends with just a few clicks. Try Ottava today and embark on a journey of data discovery like never before!

Decoding Data Analysis: Transforming Cross-Tabulation Into Structured Tabular Tables

Ottava — Fri, 02 Feb 2024 02:53:24 +0000

Looking at the two tables below, which format do you find more intuitive and easier to read?

For years, people have been using spreadsheet software to create cross-tabulated (or contingency, multi-dimensional) reports or fill forms. These reports neatly organize categories, dates, and other data points into levels of rows and columns, making them easy to read and analyze.

However, each report represents just one viewpoint of the underlying data, such as the total sales revenue of each salesman in each quarter of the year. In order to show another viewpoint of the collected data (e.g., the average sales revenue of each quarter for each salesman in the year 2023), we then have to create a new report or fill the form from scratch with spreadsheet software again, which is tedious and error-prone.

The Pivot Table Solution

To address the limitations of static cross-tabulated reports, software engineer Pito Salas introduced the pivot table concept in 1989 with Lotus Improv. Pivot tables allow users to dynamically restructure data, allowing them to easily view reports from different angles.

Prerequisites for Pivot Tables

However, using pivot tables requires a well-structured tabular-style source data model, which can be challenging for general users. Unlike creating cross-tabulated reports, designing such a data model demands engineering training and can be time-consuming and complex.

Unpivoting the Cross-Tabulated Table

To unlock the full potential of cross-tabulated data, we need a way to rapidly analyze existing cross-tabulated reports and convert them into structured tabular-style data models for further analysis. This process, known as "unpivoting," involves splitting the cross-tabulation into different parts and then reorganizing them into a structured format.

By identifying and organizing the side labels, top labels, and numbers, we can create a structured tabular-style data model that represents the original cross-tabulation in a more flexible format.

A Step-by-Step Transformation Example

Consider a simple example: an ad hoc spreadsheet-made cross-tabulation report for student scores is laid out like the following.

With students being split into two teams on the left columns and subjects on the top row, the central cross matrix area is the corresponding scores of the subject for each student. On the right end columns, column E, are formulas for the total score of each student. On the bottom row (row 6) is the formula for the average score for all students for each subject.

To convert the above cross-tabulation rectilinear displayed report into a corresponding structured tabular-style source data model, we have to split cross-tabulation into three parts first, i.e., the side labels, top labels, and numbers.

Each level of the side labels and top labels corresponds to a field of the resolved structured tabular-style data model. The number itself is also a field of the structured tabular-style data model.

As the above example cross-tabulation, there is a “Team” field(column A) for “Team A” and “Team B,” a “Student” field(column B) for “Bob,” “Ella,” “John,” and “Sally,” a “Subject” field(row 1) for “Math” and “Physics,” and the “Score” field(area C2:D5) for each corresponding number for each associated student and subject; as shown in the following resolved tabular-style data model:

Note that each number is uniquely associated with a combination of side labels and top labels, so it is most important to identify the number area first. As long as we identify the number area, the side label areas (to the left of the number area) and top label areas (to the top of the number area) are easily identified.

Identifying the Number Area

To identify the number area, the most direct way is to scan reports row by row and pick those cells with numbers, then union such cells into areas. As in the example, we can immediately know that the number area is C2:D5. But for complex reports, there might be other numbers scattered around, making it difficult to identify the real concerned number area.

Another way is to utilize the information provided by summary formulas. People write aggregation formulas in reports such as SUM or AVERAGE to summarize real numbers of concern, and we can use such information to locate the number area. In the example cross-tabulation report, in column E, we saw four SUM formulas that reference C2:D2, C3:D3, C4:D4, and C5:D5, which union into C2:D5 helping us to identify the number area. In row 6, we saw two AVERAGE formulas that reference C2:C5 and D2:D5, which combine into C2:D5, which allows us to identify the number area again.

Identifying the Label Area

After we get the number area, just scan column by column from the left border of the number area until there is no column or empty column to find the side labels; scan row by row from the top border of the number area until no row or empty row to find the top labels.

Dealing With Multi-Level Labels

The side labels and/or top labels might be in multiple levels. They each naturally form a group tree from higher level to lower level. Each level is a field for the resolved tabular-style data model. For, the side labels in the example form a group tree like the following:

And top labels (with only one level in the example) form a group tree like the following:

Then we combine and expand two trees into one master tree and append the corresponding number to the path of each side labels plus top labels combinations, as shown below:

Note that each level of the trees is associated with a field in the resolved tabular-style data model. Now, we simply traverse the tree from root to leaf and fill in the value in each associated field:

At this point, we've converted a cross-tabulation into a raw, structured tabular format.

I'm excited to share this article as I've been exploring various data analysis techniques for my project, ottava.io. We've recently implemented this unpivoting technique that I believe could be helpful for other developers, too.

By incorporating this method into our platform, ottava.io aims to simplify data analysis and manipulation, eliminating the need for manual data preparation or reliance on advanced tools like Power Query or Python Pandas. Our goal is to streamline the process and empower users to delve deeper into their data to uncover valuable insights. Your feedback and input on this approach are most welcome.

Pivot Challenge: Can you analyze this aggregated data?

Ottava — Wed, 17 Jan 2024 17:55:47 +0000

My mom is a teacher; one day, she showed me a grade book and asked me how she could re-arrange them to see how students were doing in each class across Q1 and Q2 and find out which students were falling behind on any specific subject.

“Easy peasy." I was more than confident. Pivot tables and pivot charts are for slice and dice; I immediately thought about using them. I am not an expert, but I've used Excel pivot table at work a few times. I opened up my laptop and input the grade data, starting to insert a pivot chart.

“Hum,” I immediately ran into a warning message saying that data has to be in labeled columns.

I updated the table so that the columns are labeled.

After the change, I was able to select a range and insert a pivot chart, except for the very top quarter row. Nevermind.
The resulting chart looks like this:

Almost there. But it’s not grouping students in the correct classes: Peter should be in Class A and Cindy should be in Class B.

I switched the rows and columns and tweaked them here and there. I could not get a useful chart comparing the two classes. Also, I could not include Q1 and Q2 in the charts at all.

I stumbled upon several forums and finally concluded that this grade book is not something I can slice and dice with pivot table in Excel directly. I will first have to convert it into tabular data before I can pivot it.

Tabular data, what exactly is it?

Tabular data (left) has a row header indicating the meaning of each column, followed by rows of data, either text or numbers. It is well-structured and can be easily filtered, sorted and analyzed.

On the other hand, the grade book I have (right) is pivoted or aggregated data - it has one or more row headers, one or more column headers, and many numbers in the main grid. In most cases, it looks like a summarized report.

I've tried to use other popular spreadsheet apps like Tableau, and they all have the same requirement - you have to unpivot or shape your data in tabular format before you can even start to analyze them. In Excel, this unpivot process is normally done with advanced tools like Power Query. Unpivot can also be done using Python or other advanced platforms.

How inconvenient! Wouldn't it be great if we could explore the grade book directly without having to convert it back to the tabular data?

That’s where the Ottava approach comes in to change the game.

Ottava is designed to work with pivoted or aggregated data directly, such as this grade book, sales reports, or an annual budget. Simply input your pivoted data and it will intelligently suggest appropriate charts for you to pick from.

This brings two advantages: first, obviously, skipping the unpivot, or pivoted-to-tabular transformation, saves time and makes the process of analyzing pivoted data easier.

Secondly, the pivoted table has more high-level information. If you take a look at the pivoted data below:

You can tell the hierarchy from the table. For example, there are two classes, A and B, where John and Peter belong to Class A, and Anna and Cindy belong to Class B. Also, you can tell that there are two quarters, and in each quarter, students study 3 subjects.

With this information, we can assume that the user having this table would want to compare individual grades and the grades between Class A and Class B or across Q1 and Q2. And this is how Ottava can help.

If you turn your pivoted data into tabular data, such hierarchy information is no longer there, and all fields are treated equally. A tool that analyzes tabular data would not be less effective in predicting what the user really wants to visualize.

Ottava’s distinctive capability of analyzing pivoted data streamlines the data analysis process, saving valuable time and providing users with a seamless experience. Most importantly, now, my mom can easily visualize and explore her grade book with Ottava all by herself!

If this sounds like something you’ve been waiting for, visit ottava.io to give it a try and let us know what you think!

Unlocking Data Dynamics: Understanding the Power of Pivoting and Un-pivoting

Ottava — Tue, 02 Jan 2024 21:51:00 +0000

In the realm of data manipulation and analysis, the terms "pivoting" and "un-pivoting" play crucial roles in transforming raw data into meaningful insights.

These operations are fundamental to reshaping datasets for better visualization, analysis, and interpretation. In this blog post, let's kick back, sip some coffee, and demystify these concepts that make your data dance to the right tunes.

Pivoting Data and Why Does It Matter?

Pivoting a column means aggregating identical values in that column, resulting in a new table orientation. The initial step involves sorting the table in ascending order based on the values present in the first column.

This transformative process revolves around the art of rotating or transposing data, seamlessly converting rows into columns and vice versa. The beauty of pivoting lies in its ability to bring order to the chaos of scattered information.

Picture a scenario where dates are laid out in rows, and sales figures are scattered across columns in a dataset. Pivoting enables you to elegantly rearrange the data landscape. With a simple pivot, dates effortlessly transform into columns, aligning perfectly with their corresponding sales figures.

Within the domain of data analysis, leveraging pivoted data empowers you to effortlessly employ aggregation and filtering techniques. Embracing the prowess of Pivot not only optimizes your data for analysis but also clears the path for a more streamlined and user-friendly data entry process.

Key Characteristics of Pivoting:

Aggregate Values: Pivoting is often used to aggregate or summarize data. By grouping and restructuring information, it becomes simpler to perform calculations or generate summary statistics.
Improved Readability: Pivoting can enhance the readability of data, especially when dealing with time series or categorical information. This makes it easier for analysts and decision-makers to identify trends or patterns.
Charting and Visualization: Many data visualization tools prefer data in a pivoted format. Pivoted data is often more compatible with charting libraries and makes it straightforward to create visual representations of trends and comparisons.

Un-Pivoting Data and What Does It Even Mean?

You’ve heard of pivoting data, but have you ever heard of un-pivoting before?

In the realm of data manipulation, pivoting restructures data to enhance analysis, but its counterpart, un-pivoting, performs the inverse function. Un-pivoting (sometimes called flattening the data) takes data from a summarized or aggregated state back to its original, more detailed form. This reversal is particularly valuable when dealing with pre-aggregated data designed for reporting purposes.

The significance of un-pivoting becomes apparent when considering the diverse angles from which data analysis can be approached. While pivoted data provides a convenient view for specific analyses, the un-pivoted form is essential for exploring data from various perspectives. Despite its importance, working with un-pivoted data can be a messy and intricate process.

To illustrate, creating charts or reports often necessitates the use of un-pivoted data. This step is crucial for delving into the nuances of information, revealing patterns, and facilitating a comprehensive understanding of the dataset. In essence, while pivoting optimizes data for specific analyses, un-pivoting is the key to unlocking the full analytical potential, offering a more granular and nuanced view of the underlying information.

Key Characteristics of Un-pivoting:

Detail Retrieval: Un-pivoting allows users to retrieve detailed information from aggregated datasets. For instance, if sales data is pivoted by product categories, un-pivoting can bring back the individual transactions associated with each category.
Database Normalization: Un-pivoting is an essential step in the normalization of databases. By breaking down aggregated data into its atomic components, databases can be structured more efficiently.
Flexible Analysis: Unpivoted data provides a level of granularity that is crucial for certain types of analysis. Users can explore individual data points and relationships that might be lost in a pivoted summary.

Why Ottava Chose to Work with Pivoted Data

By this time, we hope you already spent some time working with Ottava and realized what we’ve done with the data entry method. If you are new, learn more about this no-code SaaS analysis and visualization platform at ottava.io.

Ottava's unique feature allows users to input raw data directly in a pivoted format (multi-dimensional tables) rather than the conventional tabular data. This choice is driven by our understanding of the benefits and efficiency that such an approach brings to data management. The key reason Ottava enables users to work with pivoted data directly is to mitigate data entry errors that can arise from manual input.

Moreover, Ottava seamlessly executes the un-pivoting command in the background, a crucial step often required before diving into data analysis. By automating this process, Ottava spares users the complexities of un-pivoting data, allowing them to focus on working directly with the pivoted data and the charts we've meticulously crafted.

The already pivoted data in Ottava opens up a world of possibilities, presenting users with a curated collection of ready-to-use charts and visualizations tailored to this format. This not only simplifies the creation of dashboards and reports but also ensures that users can effortlessly transform their data into impactful visual representations.