What is a pivot table? Pivot tables are a tabular way to summarize and group data. They’re a valuable tool in the analyst’s toolkit, as they’re an efficient way to present and rearrange a lot of information. Here’s how they work: Pivot tables are currently unavailable for the following databases in Metabase:
Pivot tables work for all other officially supported databases. Pivot tables vs. regular tablesYour typical, basic table is a grid of cells. Each column represents an attribute of a single record, with a single record per row. A pivot table is a table that groups rows and columns, and includes summary rows with aggregate values for those groupings. These aggregate values are usually referred to as subtotals and grand totals, though these aggregates could also be other values, such as averages. The reason they’re called pivot tables is because you can rotate (“pivot”) a column 90 degrees so that the values in that column become column headings themselves. Pivoting values into column headings can be really helpful when trying to analyze data across multiple attributes, like time, location, and category. You can pivot multiple rows to columns and vice versa, or not pivot any at all. But this is all pretty abstract, so let’s walk you through an example to give you a feel for how pivot tables work. How to create pivot tables: exampleTo start, let’s say we want to know:
Here’s our query using the Notebook Editor: Fig. 1. Notebook Editor with a preview of the question's results.Here we’re taking data from the The resulting table is a regular one, with rows for each combination of state, year, and product category. Now, let’s say that for each state, we also want to know the sum of the annual subtotals for each state (e.g., how much money did orders for Doohickey products make in Alaska for all years?). To find out, we could add up the subtotals ourselves, or use a pivot table to calculate that figure for us. At the bottom left of your screen, click Visualization > Pivot table. Fig. 2. The pivot table is born.In our pivot table, Metabase has set the rows, columns, and values as follows:
Like the flat table, the pivot table lets us see, for example, that in 2020 our customers in Alaska (AK) have purchased a combined 11 Doohickey products for $867.63. But now the pivot table has grouped the rows related to Alaska, and given a subtotal for those Alaskan rows, allowing us to see the answer to our question: Alaskans purchased 103 Doohickeys from 2016–2020, totaling $6,900.43. In addition to the group subtotals, the pivot table also includes both row and column grand totals:
We can navigate the table by collapsing and expanding groups of rows: Fig. 3. Collapsed rows on a pivot table, showing only the groupings subtotals. Only Colorado's grouping is expanded.Now, let’s try pivoting the table. In the bottom left of the screen, we’ll click on Settings. To pivot the table, we’ll move fields between the three buckets: rows, columns, and values. Order within a single bucket matters, so let’s start by simply rearranging the table within a single bucket: the rows bucket. If we switch the order of fields to use for table rows, putting Now the table groups first by year, then gives a breakdown of orders for each state across each product category. We can also switch fields between the buckets, like moving You can also turn off subtotals for a given row grouping: Fig. 6. You can toggle subtotals on or off.Like with flat tables, we have some sorting and formatting options, and we can click on values in the table to bring up the action menu, which will lets us drill through the data. Fig. 7. Sorting and formatting options, as well as the action menu.How to create pivot tables: limitationsPivot tables only work with relational databases that support joins and expressions, so you won’t be able to use them with databases like MongoDB and Google Analytics. They also only work with questions composed with the query builder. The workaround here is that if you must use SQL to compose a question, you can save that question, then use its results as the starting point for a GUI question in order to build a question. The trick here is to do your aggregation and grouping in the GUI question. That is, use the SQL question to grab the raw data you want to work with (maybe create a model), then start a new GUI question to filter, summarize, and group that data. For example, to use a SQL question to build the pivot table we created above, you’d first write a SQL query to get the raw data you want to work with:
Notice that we’re just grabbing records here; there’s no summarizing or grouping. Next, we save that SQL question (here as Now we can count, sum, and group our results: Fig. 9. The Notebook Editor summarizing data from a saved question.When we visualize this question, we’ll now be able to use the pivot table visualization to see the group subtotals and grand totals. Further reading
Thanks for your feedback! Get articles like this one in your inbox every month Is a data summarization tool used to sort?A pivot table is a data summarization tool that is used in the context of data processing. Pivot tables are used to summarize, sort, reorganize, group, count, total or average data stored in a database.
What are the first steps a data analyst takes when working with data in a spreadsheet?Step 1: Get familiar with data analysis. Explanation. ... . Step 2: Start with Excel, with an eye on Tableau. Explanation. ... . Step 3: Start working with pivot tables. ... . Step 4: Participate in Data Visualization Competitions. ... . Step 5: Run analysis on non-profit data sets.. Which of the following questions do data analysts ask to make sure they will engage their audience select all that apply?Solution. To engage their audience, data analysts ask about what roles the people in the audience play, their stake in the project, and what they hope to do with the data insights.
What do data analyst use to label the type of data contained in each column in a spreadsheet?Correct. The labels that describe the type of data contained in each column of a spreadsheet are called attributes.
|