Shulou(Shulou.com)06/01 Report--

In this issue, the editor will bring you the comparison of Excel, SQL, PowerBI and Python in data query and screening. The article is rich in content and analyzes and describes for you from a professional point of view. I hope you can get something after reading this article.

Some netizens left a message earlier:

"SQL has never been a tool, it's a language!"

To tell you the truth, I was surprised. In my understanding, the essence of language is a tool, and Baidu encyclopedia is also defined in this way:

Language is the voice that conveys information. It is the most important communication tool for human beings, and it is the main way for people to communicate.

Natural language, computer language and database language is a kind of coding and decoding tool for communication between people and computers. As a communication tool, language usually needs to be recorded and conveyed through a certain physical carrier, and can not exist independently.

Just as English is a language and a communication tool, listening, speaking, reading and writing are its means of communication and the carrier of instrumentalization.

The same is true of SQL, which is a language tool, while MySQL, HiveSQL and other database tools are its design expression vectors.

Looking back, why should we learn English? why should we learn Python?,? why should we learn SQL?

The reason to believe you, it is definitely not just a language or culture, otherwise it will only be as uncomfortable as a foreign language exam in high school!

More often, it is to achieve a certain purpose, and stimulate us to take the initiative to find such a tool that can achieve thinking and communication.

In short, language is a tool. All tools, do not stick to one style, are used!

Another netizen left a message saying:

"Power BI is just good at visualization, but nothing else is good."

OK, let's take a look at the development trend of PowerBI in recent years, you will find that it is more powerful than Tableau and more and more different from other BI products, which has a lot to do with Microsoft's own user environment and software family.

2020

2019

Let's take a look at the requirements on the recruitment website, PowerBI has gradually become the main tool in recent years, such as operations department, financial department, business analysis and so on.

Are you hungry? recruitment requirements.

Ali recruitment requirements

In the past, Python has always been my main tool, but now Lao Haiqiang pushes Power BI and even suggests that it be the first choice for business analysis.

One formula: Power BI data analysis flow = PQ + PP + PV

That is, PowerQuery data processing query, PowerPivot data structure modeling, PowerView data chart visualization

This process sequence, which is common to all analysis tools, is naturally quite comfortable to use.

And Power BI can completely solve many annoying problems in Python or Excel, such as:

You don't have to write dozens or even hundreds of lines of Python code to make a chart, and you don't have to adjust the parameters of the Excel chart.

Make a table direct star connection, no longer use pandas back and forth join, also do not use the VLOOKUP function of Excel mystery effect

Do an analysis, pre-use Python to deal with data, later use Excel to chart, now directly PowerBI!

I won't say much about the details here. If you are interested, you can take a look at my Q & A, which is specially written.

In short, Lao Hai believes that Power BI is not just visualization. I believe that with the iteration of the version, it will be enough to replace Exce or even Python in the future.

OK, let's go back and compare Excel, SQL, PowerBI and Python.

Today, when it comes to the viewing and filtering of data, we will go straight to the operation demonstration without much nonsense:

View and filter data

When using Excel:

Step 1: take a look at the data, such as the number of rows, columns, first and last rows.

You can use shift + ctrl + key ↓ to pull directly to the bottom for viewing.

View number of rows & number of columns

Use shift + ctrl + ↑ to view the foremost data

View the front data

Step 2: you can also select the entire column to view the data of a specific field

Specific field

Step 3: or view the first 10 rows of data

The first 10 lines

Step 4: view the largest or smallest top 10 rows of data

Up to the first 10 lines

Step 5: check the null values of different data columns

Step 6: view the data that meet a certain condition

Step 7: select "Advanced" in "screening" to filter the data with multiple conditions, such as meeting the purchase volume of Tianjin more than 2 and Wuhan more than 3 at the same time.

Step 8: advanced filtering can also implement multi-conditional or relational filtering data, that is, one of the conditions can be met.

Step 9: to view the deduplicated data of a specific column name, you can select "Delete duplicates" in "data" to achieve this.

It can also be done by "Advanced" under "filter" in "data". Check "non-duplicate records" here.

Step 10: check the statistics of the deduplicated data. This method is similar to python's value_couts.

When using SQL:

Step 1: check the data

Step 2: view the first 10 rows of data

Step 3: view the data of a condition, such as the data that the store city is Tianjin

Step 4: view data that meet any of several conditions, such as Tianjin or Wuhan

Step 5: view specific column name data

Step 6: view the number of deduplicated data for a specific column name. For example, how much is the total in the store city?

Step 7: view the deduplicated data for a specific column name, and view the specific city name after deduplicated.

Step 8: view non-null records and null records

When using Power BI:

Step 1: check the basic situation of the data, select "convert data" under "Home Page", enter the PQ editor, and view the data.

Step 2: select a field to view the number of non-duplicate values in the lower left corner

Step 3: in the PQ editor, you can use various viewing functions, such as keeping the first few lines, the last few lines, and so on

Step 4: view specific field column data and click "Select column" to filter specific field columns.

Step 5: to view the data that meet a certain condition, click the drop-down arrow on the right side of the field to filter

Step 6: to view the quantity statistics after being deduplicated, you can use "count non-duplicate values" in "Statistics" under "conversion" in the PQ editor.

Step 7: check the statistics of different data values, click the arrow on the right side of the field, select "grouping by", set the grouping field and the method of calculation, and then complete the grouping statistics.

When using Python:

Step 1: check the data, mainly to see if there are garbled codes and whether the overall size of the data is correct

Step 2: check the first 10 rows of data, mainly to see the data format under different fields, and of course you can use .dtypes to see if the data type of the current field is reasonable.

Step 3: view the data of a specific column. It is generally recommended to use loc and iloc for slicing.

Step 4: view the data that meet a certain condition. Generally use loc, and filter with the condition.

Step 5: view data that meets any of several conditions. In addition to isin, you can also use relationships that can be combined with or with not

Step 6: check to see if there is a null value. For the contents of the null value NULL,Nan, you can read the articles before Lao Hai.

Step 7: view the deduplicated data of a specific column, and count the number.

The above is the comparison of Excel, SQL, PowerBI and Python in the data query and screening shared by Xiaobian. If you happen to have similar doubts, please refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.