Data analytics power pack using Python

Data analytics power pack using Python

To be precise, there are cases where you operate Excel itself and cases where you operate on Excel files, but in any case, you can read and write Excel from Python, draw graphs, execute Excel macros, etc.

First, let's see what kinds of libraries are available.

• 1. Pandas

To be precise, Pandas is a library for data analysis. However, you can operate Excel with Pandas as well. However, Pandas is not a library for beginners because it requires an understanding of unique concepts and can only be used if you are familiar with data analysis. A library like those used by data scientists.

• 2. NumPy
• 3. Matplotlib
• 4. OpenPyXL

It is the most major library for Excel operations in Python. The features of OpenPyXL are as follows:

High-speed processing

It is said to be the fastest in the library that operates on Excel from Python. When it comes to processing speed, we tend to focus on machine power, but in reality, it depends greatly on the performance of the library. If you want to process Excel at high speed, it will be openpyxl.

There is a lot of information on the internet.

Since Python's Excel operations heavily use detailed functions, it is convenient to find information with just a little research.

• xlwings

xlwings is a library that operates on Excel directly from Python. Openpyxl and Pandas are libraries that operate Excel files, but xlwings operate Excel directly. Therefore, xlwings cannot be used on Linux, which does not support Excel.

What is convenient about operating Excel directly is that you can execute Excel macros. Executing Excel macros is quite difficult with Openpyxl. However, it is said to be slower than OpenPyXL.

2. Benefits of operating Excel with Python

• 1. Easy to automate Excel, even on Mac.

Did you know that there is a non-negligible difference between Excel VBA on Windows and Excel VBA on Mac? Because of this difference, bringing a macro that works in Excel on Windows to Excel on Mac will not work normally. "Porting work" is required. There are even engineers who specialize in it.

However, in the case of Python, the differences between Windows and Mac are limited to a small part, such as differences in machine-dependent characters. A normal Python program works as it is even if you develop it on Windows and bring it to your Mac. Therefore, if you write what you want to process in Excel in Python instead of VBA, it will work on both Windows and Mac, which is very convenient.

• 2. Not only Excel but also other apps can be linked.

Excel VBA can only operate on Excel. Well, Excel VBA is a language developed for Excel. However, Python is an open language. You can do a wide range of things, and you can automate other apps, such as browser operations. Taking advantage of this feature, it is possible to link Excel operations and browser operations and automate all the processing. Being able to automate not only Excel but also other apps is probably the biggest advantage of using Python to automate Excel operations.

• 3. You can deepen your learning of Python

When automating Excel operations, writing Python is usually concise, but the only thing that tends to be complicated is the data format. This is unavoidable as Excel can store various data formats in each cell in tabular format.

However, when dealing with data in complex data formats in Python, you have to take full advantage of the features of Python and become proficient in Python programming.

For novice Python programmers, working with Excel in Python is a great way to improve their Python programming.