I have written several times about the usefulness of pandas as a data manipulation/wrangling tool and how it can be used to efficiently move data to and from Excel. There are cases, however, where you need an interactive environment for data analysis and trying to pull that together in pure python, in a user-friendly manner would be difficult. This article will discuss how to use xlwings to tie Excel, Python and pandas together to build a data analysis tool that pulls information from an external database, manipulates it and presents it to the user in a familiar spreadsheet format.

Read more...

Pandas includes multiple built in functions such as sum, mean, max, min, etc. that you can apply to a DataFrame or grouped data. However, building and using your own function is a good way to learn more about how pandas works and can increase your productivity with data wrangling and analysis.

The weighted average is a good example use case because it is easy to understand but useful formula that is not included in pandas. I find that it can be more intuitive than a simple average when looking at certain collections of data. Building a weighted average function in pandas is relatively simple but can be incredibly useful when combined with other pandas functions such as groupby.

This article will discuss the basics of why you might choose to use a weighted average to look at your data then walk through how to build and use this function in pandas. The basic principles shown in this article will be helpful for building more complex analysis in pandas and should also be helpful in understanding how to work with grouped data in pandas.

Read more...

I have written several articles about using python and pandas to manipulate data and create useful Excel output. In my experience, no matter how strong the python tools are, there are times when you need to rely on Excel as the vehicle to communicate your message or further analyze the data. This article will walk through some additional improvements you can make to your Excel-based output by:

• Adding Excel tables with XlsxWriter

• Inserting custom VBA into your Excel file

• Using COM for merging multiple Excel worksheets

Read more...

As many of you know, pandas released version 0.17.0 on October 9th. In typical pandas fashion there are a bunch of updates, bug fixes and new features which I encourage you to read all about here. I do not plan to go through all of the changes but there are a couple of key things that I think will be useful to me in my daily work that I will explore briefly in this article. In addition, I am including a couple of other tips and tricks for pandas that I use on a frequent basis and hope will be useful to you.

Read more...

Using python and pandas in the business world can be a very useful alternative to the pain of manipulating Excel files. While this combination of technologies is powerful, it can be challenging to convince others to use a python script - especially when many may be intimidated by using the command line. In this article I will show an example of how to easily create an end-user-friendly GUI using the Gooey library. This interface is based on wxWindows so it looks like a “native” application on Windows, Mac and Linux. Ultimately, I believe that presenting a simple user interface to your scripts can greatly increase the adoption of python in your place of business.

Read more...