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

How to make Excel easy to access powerful Python, for this problem, this article details the corresponding analysis and solutions, hoping to help more friends who want to solve this problem find a simpler and easier way.

preface

Python is very powerful, some complex Excel operations, python only one or two statements can be. But Python has a high threshold for installing software and various module libraries. Most of the time we just want to use Python's functionality lightweight, what's the easy way? Smartbi Analytics provides web python functionality to execute python scripts in a browser without installing any software; it also provides Excel plug-ins to easily retrieve python execution results back to Excel.

The following is an example of basic data filtering, so that you can quickly get started with the python function of Smartbi intelligent analysis and appreciate the charm of python data analysis.

data source

We need to use an EXCEL sample file first. Here I choose an e-commerce order list.

Excel screening

If we filter in EXCEL, we generally use EXCEL's own filtering function to check with the mouse in the pop-up filtering interface. For example, we want to filter the "Order Date" as 2010 and the "Order Grade" as Advanced:

Smartbi Intelligence Analytics uses python for filtering

load data

Since the ETL of Smartbi intelligent analysis is processed on the web page, the first thing we have to do is to import the local data source into the Smartbi intelligent analysis platform, and the data source can be quickly imported in the Smartbi intelligent analysis data connection interface. In addition to local data files, Smartbi intelligent analysis also supports the connection of relational databases such as mysql and Alibaba Cloud. Open the ETL interface, drag the component of the relational data source to the display area, and find your data source according to the storage path

Right-click on View Output for a relational data source and we can preview the output of the data source:

Python

After completing the data connection, we can use the python script that comes with Smartbi intelligent analysis ETL to perform various processing on the data. We first drag the python component to the middle display area and connect it to the relational data source above.

Click Python input box to view, you can see that the input box here is basically the same as the input box of pycharm and other software, as long as you are familiar with Python friends can easily start, and the system has entered some scripts in advance, these codes are the default system comes with, do not need to write their own, very thoughtful. As you can see, the script written in advance mainly calls numpy and pandas and defines functions:

According to the requirements mentioned above, our main purpose is to filter the two fields of order date and order grade. We first define two variables cond and cond1. Cond calls pd.to_datetime() function of pandas here, which is the time processing function of pandas. The result to be filtered by dt.year is that the year is equal to 2010. Next cond1 to filter the condition is column4= advanced, because the relationship between the two is and, after writing these two scripts, but also use & to connect these two conditions.

After the script is written, click OK. Finally, let's take a look at the effect of python script execution. The order date in the second column is displayed as 2010, and the order level is displayed as advanced. Just write 3 lines of code, and our filtering requirements have been realized:

data output

We use ETL output function to save and output the report. In order to call the data source conveniently in EXCEL, the data source type to be output here is dataset. First, drag "Output to dataset" into the middle display area in ETL interface and connect with the above components.

Then right-click "Export to dataset" and select "Execute to here". The dataset will be successfully saved on the cloud of Smartbi Intelligent Analysis:

Data back to EXCEL

Finally, go back to EXCEL, find the data set just saved in the data set panel, click on the data set, and drag the fields in the data set to EXCEL with the mouse.

Click Refresh on the toolbar, and all the data will be refreshed. We can see that the two fields of "Order Date" and "Order Level" have achieved the filtered effect:

About how to make Excel easy access to powerful Python questions to share the answer here, I hope the above content can be of some help to everyone, if you still have a lot of doubts not solved, you can pay attention to the industry information channel to learn more related knowledge.

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.