What is Pivot Analysis?
Pivot analysis is to summarize, filter, analyze, compare and plot data in different dimensions. Used to discover changes in data and differences caused by different factors. This is very useful in sales, statistics, finance, etc., and is often used in some management software.
Next, I used Excel to introduce what pivot analysis and pivot tables are.
Below I use Excel's pivot table to analyze the sales volume data of iPhone phones in 2013, 2014 and 2015 in China and the United States to summarize the sales trends of iPhone phones.
Statement: All data is made up by oneself and has no reference value.
Excel Pivot Tables and Pivot Diagrams
This is the sales volume of iPhone phones in China and the United States in 2013, 2014, 2015.
---- In order to discover changes in different mobile phone sales in each country in 2013, 2014, 2015, a pivot table needs to be inserted and data is configured. Here, if you add fields to rows, they will be classified by rows. (First by country, then year and product);
Get the pivot table according to the configuration
Through the pivot table, it is easy to see the total sales volume in China and the total sales volume in the United States.
Pivot diagram
According to this chart, it can be seen that in recent years, iPhone sales in China have dropped sharply.
---- In order to observe the sales differences between China and the United States, you only need to configure the data panel as follows. (Category by product and country)
Pivot Table
Pivot diagram
It can be found that since 2014, iPhone sales in China have begun to be lower than those in the United States, and the difference between its price and the United States has become smaller and smaller.
Through the above analysis of iPhone sales using Excel pivot tables, we found that Excel's Pivot Pivot Pivot Tables and Pivot Charts have the following benefits:
Analyzing data from multiple dimensions, only one table is needed to analyze the changes in data from multiple angles.
By dragging and dropping anyone can create dynamic, summary views.
It processes and analyzes very complex and cumbersome data, which can help us intuitively discover the trends and differences in data changes.
There are a wide variety of reports (bar charts, line charts), meeting a variety of needs.
It can perform data filtering for certain specific analysis.
OK, Excel and its pivot tables are so powerful!
It is precisely because Excel is so powerful that the requirements are now here, and Javascript is now required to implement the same functions on the web side! (My God-) :)
Implementation using Javascript
First, use an object array to define the data (used for a relational database. Although it is ordered here, the real data is unordered)
var json = [{ "year": 2013, "country": "usa", "product": "iphone 5", "sales": 8000, "price": 6000 }, { "year": 2013, "country": "usa", "product": "iphone 6", "sales": 0, "price": 0 }, { ... ... }]Or use key-value pairs
var data = { "2013": { "china": { "iphone": { "sales": 8000, "price": 6000 }, "iphone5":{ ... } }, "usa": { ... } }, "2014": { ... } ...}Pivot Table
First, I will solve the problem of pivot tables first.
According to the above data, there are two ways to process the data summary (here is just a demo, the real code will be much more complicated)
Use regular data traversal methods. (To complete a sorting function, use Array.prototype.sort() method here.)
data.sort(function(a, b) { return a.year > b.year || a.country > b.country || a.product > b.product || a.sales > b.sales || a.price > b.price ;});Here we first sort by country. If the country is the same, then sort by product...
Then summarize and calculate based on the sorted array (loop traversal, here it is just classified by year and country)
var getTotal = function () { var total = {}; for (var i = 0; i < json.length; i++) { var item = json[i]; // Get the total sales volume of each country total[item.country] = total[item.country] || {}; total[item.country].sales = total[item.country].sales == undefined ? item.sales : total[item.country].sales + item.sales; // Total sales volume of each country in different years total[item.country][item.year] = total[item.country][item.year] == undefined ? item.sales : total[item.country][item.year] + item.sales; } var sum = 0; // Total sales for (var key in total) { sum += total[key].sales; } total.sum = sum; return total;};This is the summary result
Okay, now you can make a table based on this data (I won't go into details here).
Use SQL statements for data sorting and summarization
The second method to sort and summarize data is to use SQL.
Just one sentence
select * ,total from data as A,select sum(sales) from data group by product as B where A.product = B.product
Finally, the interface is improved. This method can achieve similar effects. A simple pivot table is considered complete.
However, since this project has many tables and does not even know the name of the column title, the above method cannot be used at all.
Now, in another way to solve this problem, this plug-in has been used in the Excel IO part.
Solved via Wijmo
Here is a demo that uses Wijmo to complete pivot.
Its basic use has been introduced in the first pure Javascript implementation of excek IO.
Pivot Table
First import the required packages
<script src="./lib/wijmo/wijmo.min.js"></script><script src="./lib/wijmo/wijmo.input.min.js"></script><script src="./lib/wijmo/wijmo.grid.min.js"></script><script src="./lib/wijmo/wijmo.grid.filter.min.js"></script><script src="./lib/wijmo/wijmo.chart.min.js"></script><script src="./lib/wijmo/wijmo.olap.min.js"></script>
Then instantiate a pivot panel based on the data
<div id="pivot_panel"></div>
var app = {};app.pivotPanel = new wijmo.olap.PivotPanel('#pivot_panel');// engine is the data engine of this panel, and the associated charts will share a data engine. var ng = app.pivotPanel.engine;ng.itemsSource = app.collectionView;ng.rowFields.push('Country','Year','Product');ng.valueFields.push('Sales (Table)');ng.showRowTotals = wijmo.olap.ShowTotals.Subtotals;Here is the effect of this code:
Generate tables based on the pivot configuration panel
<div id="pivot_grid"></div>
app.pivotGrid = new wijmo.olap.PivotGrid('#pivot_grid', { itemsSource: app.pivotPanel, showSelectedHeaders: 'All'});Add filter
Filter column data by right-clicking on the column and selecting Filter.
Pivot diagram
app.pivotChart = new wijmo.olap.PivotChart('#pivot_chart', { chartType:'Column',// Bar itemsSource: app.pivotPanel});OK, the pivot table and perspective are done.
This is the complete effect of the Demo.
Through the Pivot control, the data function of this project is also completed quickly.
Summarize
The above is the entire content of this article. I hope it will be helpful to everyone's study or work. If you have any questions, you can leave a message to communicate.