Resources

pandas filtering records

Pandas – filtering records in 20 ways

Filtering records is a quite common operation when you process or analyze data with pandas,a lot of times you will have to apply filters so that you can concentrate to the data you want. Pandas is so powerful and flexible that it provides plenty of ways you can filter records, whether you want to filtering by columns to focus on a subset of the data or base on certain conditions. In this article, we will be discussing the various ways of filtering records in pandas.

Prerequisite:

You will need to install pandas package in order to follow the below examples. Below is the command to install pandas with pip:

pip install pandas

And I will be using the sample data from here, so you may also want to download a copy into your local machine to try out the later examples.

With the below codes, we can get a quick view of how the sample data looks like:

import pandas as pd
df = pd.read_excel(r"C:\Sample-Sales-Data.xlsx")
df.head(5)

Below is the output of the first 5 rows of data:

pandas filtering data

Let’s get started with our examples.

Filtering records by label or index

Filtering by column name/index is the most straightforward way to get a subset of the data frame in case you are only interested in a few columns of the data rather than the full data frame. The syntax is to use df[[column1,..columnN]] to filter only the specified columns. For instance, the below will get a subset of data with only 2 columns –  “Salesman” and “Item Desc”:

new_df = df[["Salesman","Item Desc"]]
new_df.head(5)

Output from the above would be:

pandas filtering data subset

If you are pretty sure which are the rows you are looking for, you can use the df.loc function which allows you to specify both the row and column labels to filter the records. You can pass in a list of row labels and column labels like below:

df.loc[[0,4], ["Salesman", "Item Desc"]]

And you would see the row index 0 and 4, column label “Salesman” and “Item Desc” are selected as per below output:

pandas filtering loc

Or you can specify the label range with : to filter the records by a range:

df.loc[0:4, ["Salesman", "Item Desc"]]

You would see 5 rows (row index 0 to 4) selected as per below output:

pandas filtering loc with label range

Note that currently we are using the default row index which is a integer starting from 0, so it happens to be same as the position of the rows. Let’s say you have Salesman as your index, then you will need to do filtering based on the index label (value of the Salesman), e.g.:

df.set_index("Salesman", inplace=True)
df.loc["Sara", ["Item Desc", "Order Quantity"]]

With the above code, you will be able to select all the records with Salesman as “Sara”:

pandas filtering loc with row label

Filtering records by row/column position

Similarly, you can use iloc function to achieve the same as what can be done with loc function. But the difference is that, for iloc, you shall pass in the integer position for both row and columns. E.g.:

df.iloc[[0,4,5,10],0:2]

The integers are the position of the row/column from 0 to length-1 for the axis. So the below output will be generated when you run the above code:

pandas filtering iloc function

Filtering records by single condition

If you would like to filter the records based on a certain condition, for instance, the value of a particular column, you may have a few options to do the filtering based on what type of data you are dealing with. 

The eq and == work the same when you want to compare if the value matches:

flt_wine = df["Item Desc"].eq("White Wine")
df[flt_wine]

Or:

flt_wine = (df["Item Desc"] == "White Wine")
df[flt_wine]

Both will generate the below output:

pandas filtering equals condition

If you run the flt_wine alone, you will see the output is a list of True/False with their index. This is how the filter works as pandas data frame would filter out the index with False value.

To get the data with the negation of certain condition, you can use ~ before your condition statement as per below:

df[~flt_wine]
#or
df[~(df["Item Desc"] == "White Wine")]
#or
df[(df["Item Desc"] != "White Wine")]

This will return the data with “Item Desc” other than “White Wine”.

And for string data type, you can also use the str.contains to match if the column has a particular sub string.

df[df["Item Desc"].str.contains("Wine")]

If you want to filter by matching multiple values, you can use isin with a list of values:

flt_wine = df["Item Desc"].isin(["White Wine", "Red Wine"])
df[flt_wine].head(5)

pandas filtering isin function

And you can also use data frame query function to achieve the same. But the column label with spaces in-between would cause errors when using this function, so you will need to reformat a bit of your column header, such as replacing spaces with underscore (refer to this article for more details ).

With this change in the column header, you shall be able to run the below code with the same result as above isin method.

df1 = df.query("Item_Desc in ('White Wine','Red Wine')")
df1.head(5)

There are other Series functions you can use to filter your records, such as isnull, isna, notna, notnull, find etc. You may want to check pandas Series documentation.

Filtering records by multiple conditions

When you need to filter by multiple conditions where multiple columns are involved, you can also do similar as what we have discussed in above with the & or | to join the conditions.

For filtering records when both conditions are true:

flt_whisky_bulk_order = (df["Item Desc"] == "Whisky") & (df["Order Quantity"] >= 10)
df[flt_whisky_bulk_order]

The output would be :

pandas filtering and condition

For filtering the records when either condition is true:

flt_high_value_order = (df["Item Desc"] == "Whisky") | (df["Price Per Unit"] >= 50) 
df[flt_high_value_order]

The output would be :

pandas filtering or condition

Similarly, the above can be done with data frame query function. Below is the example of AND condition:

df1 = df.query("Item_Desc == 'Whisky' and Order_Quantity >= 10") 
df1.head(5)

Below is the example of OR condition:

df1 = df.query("Item_Desc_ == 'Whisky' or Price_Per_Unit >= 10")
df1.head(5)

Filtering records by dataframe.filter

There is also another filter method which can be used to filter by the row or column label.

Below is an example that can be used to get all the columns with the name starting with “Order” keyword:

df.filter(regex="Order*", axis=1)

you shall see the below output:

pandas filtering dataframe filter

Similarly, when applying to row labels, you can axis=0

df.set_index("Order Date", inplace=True)
df.filter(like="2020-06-21", axis=0)

pandas filtering dataframe filter 2

Take note that data frame query function only works on the row or column label not any specific data series.

Conclusion

Filtering records is a so frequently used operation whenever you need to deal with the data in pandas, and in this article we have discussed a lot of methods you can use under different scenarios. It may not cover everything you need but hopefully it can solve 80% of your problems. There are other Series functions you may employ to filter your data, but probably you would see the syntax still falls under what we have summarized in this article.

If you are interested in other topics about pandas, you may refer to here.

pandas format column headers

Pandas format column headers

When using Pandas to deal with data from various sources, you may usually see the data headers in various formats, for instance, some people prefers to use upper case, some uses lowercase or camel case. And there are also different ways to join the words when using as column label, such as space, hyphen or underscore are commonly seen. This potentially causes some problem when you want to reference a particular column since pandas column label is case sensitive, and you may get confused what the correct spelling. In this case, you would need to format column headers into a standard format before processing the data. This article will be explaining the different ways to format column headers.

Prerequisite:

You will need to install pandas package in order to follow the below examples. Below is the command to install pandas with pip:

pip install pandas

With the package installed, let’s create a sample data set for our later use:

import pandas as pd
df = pd.DataFrame({"Salesman" : ["Patrick", "Sara", "Randy"],
                  "order date" : pd.date_range(start='2020-08-01', periods=3),
                  "Item Desc " : ["White Wine", "Whisky", "Red Wine"],
                  "Price Per-Unit": [10, 20, 30], 
                  "Order Quantity" : [50, 10, 40],
                  99: ["remak1", "remark2", "remark3"]})

You can preview your data set from Jupyter Notebook, it would be similar to below:

pandas format column headers

You probably wonder why someone would use number as column header, but it does happen in the real-world for various reasons.

If you use df[99] or df.loc[0,99], you are able to see the correct data, which means it does not confuse pandas whether your column label is string or numeric.

pandas format column headers

But it sometimes causes readability issue to human and introduce errors, especially if you always assume column labels are string and perform some string operation on them.

Convert column header to string

So the first thing we probably want to do it to convert column header into string. You can use the astype method to convert it:

df.columns = df.columns.astype("str")

A lot of pandas methods have “inplace” parameter to apply the changes without creating new objects, but astype does not support “inplace”, so we will need to re-assign the formatted object back to df.columns.

Format column header with cases

If you would like to reference all columns in uppercase, you can have at least three choices of doing it:

  • Use the str method from pandas Index object
  • Use the map method from pandas Index object
  • Use Python built-in map method

Below is the sample code for above 3 options:

#Index.str method
df.columns = df.columns.str.upper()

#Index.map method
df.columns = df.columns.map(str.upper)

#Python built-in map method
df.columns = map(str.upper, df.columns)

The column headers would be all converted to uppercase:

Index(['SALESMAN', 'ORDER DATE', 'ITEM DESC ', 'PRICE PER-UNIT',
       'ORDER QUANTITY', '99'],
      dtype='object')

Option 1 seems to be most straightforward way as long as the operations are supported by str, such as ljust, rjust, split etc.

Similarly, you can convert column headers to lowercase with str.lower():

df.columns = df.columns.str.lower()

or camel case with str.title if this is the format you wish to standardize across all data sources:

df.columns = df.columns.str.title()

Replace characters in column header

If you noticed there is a space accidentally added in my column header – “Item Desc “, this will cause index error if I use df[“Item Desc”] to access the column. To fix this, we can use the str.strip to remove all the leading or trailing spaces:

df.columns = df.columns.str.strip()

But those spaces in-between cannot be removed, if want to you use df.Item Desc , it will give you error. The best way is to replace all the spaces with hyphen or underscore, so that you can use both df[“Item_Desc”] and df.Item_Desc format to reference the column. Below is how you can use a simple lambda function to replace the space and hyphen with underscore:

df.columns = df.columns.map(lambda x : x.replace("-", "_").replace(" ", "_"))
# Or
df.columns = map(lambda x : x.replace("-", "_").replace(" ", "_"), df.columns)

If you check again, the column header would be updated as per below:

Index(['Salesman', 'Order_Date', 'Item_Desc', 'Price_Per_Unit',
       'Order_Quantity', '99'],
      dtype='object')

Note that, if you use df.columns.str.replace, you cannot just chain multiple replace function together, as the first replace function just return an Index object not a string.

Often you would see there are new line characters in the column header, you can remove them with the replace method as per below:

df.columns = df.columns.str.replace("\n", "")

Add prefix or suffix to column header

With the map and lambda, you can also easily add prefix or suffix to the column header, e.g.:

#adding prefix with "Label_"
df.columns = df.columns.map(lambda x : "Label_" + x)

#adding suffix with "_Col"
df.columns = df.columns.map(lambda x : x + "_Col")

Use of rename method

If you find the entire column header is not meaningful to you, you can manually rename multiple column names at one time with the data frame rename method as per below:

df.rename(columns={"Salesman" : "Sales Person", "Item Desc " : "Order Desc"}, inplace=True)

The rename method support inplace parameter, so you can immediately apply the changes in the original data frame.

pandas rename column name

flatten multi index column

After you aggregated your data with groupby and agg function, you may sometimes get a multi index column header, for instance:

df_sum = df.groupby("Salesman").agg({"Order Quantity": ["mean", "sum"]})

When you calculate both mean and sum of the “Order Quantity” column at the same time, you will get the result similar to below:

python pandas format column header multi index column

The column header become a multi index header, so if you want to flatten this column header by join the two levels into one, you can make use of the list comprehension as per below :

df_sum.columns = [' '.join(col) for col in df_sum.columns]

With the above, you would see column header changed from hierarchical to flattened as per the below:

python pandas format column header flatten multi index column

 

Conclusion

In this article, we have discussed a few options you can use to format column headers such as using str and map method of pandas Index object, and if you want something more than just some string operation, you can also pass in a lambda function. All these methods are not just limited to column header or row label (Index object), you can also use them to format your data series.

If you are interested in other topics about pandas, you may refer to here.

 

split or merge PDF files with PyPDF2

Split or merge PDF files with 5 lines of Python code

There are many cases you want to extract a particular page from a big PDF file or merge PDF files into one due to various reasons. You can make use of some PDF editor tools to do this, but you may realize the split or merge functions are usually not available in the free version, or it is too tedious when there are just so many pages or files to be processed. In this article, I will be sharing a simple solution to split or merge multiple PDF files with a few lines of Python code.

Prerequisite

We will be using a Python library called PyPDF2, so you will need to install this package in your working environment. Below is an example with pip:

pip install PyPDF2

Let’s get started

The PyPDF2 package has 4 major classes PdfFileWriter, PdfFileReader, PdfFileMerger and PageObject which looks quite self explanatory from class name itself. If you need to do something more than split or merge PDF pages, you may want to check this document to find out more about what you can do with this library.

Split PDF file

When you want to extract a particular page from the PDF file and make it a separate PDF file, you can use PdfFileReader to read the original file, and then you will be able to get a particular page by it’s page number (page number starts from 0). With the PdfFileWriter, you can use addPage function to add the PDF page into a new PDF object and save it.

Below is the sample code that extracts the first page of the file1.pdf and split it as a separate PDF file named first_page.pdf

from PyPDF2 import PdfFileWriter, PdfFileReader
input_pdf = PdfFileReader("file1.pdf")
output = PdfFileWriter()
output.addPage(input_pdf.getPage(0))
with open("first_page.pdf", "wb") as output_stream:
    output.write(output_stream)

The input_pdf.getPage(0) returns the PageObject which allows you to modify some of the attributes related to the PDF page, such as rotate and scale the page etc. So you may want to understand more from here.

Merge PDF files

To merge multiple PDF files into one file, you can use PdfFileMerger to achieve it. Although you can also do with PdfFileWriter, but PdfFileMerger probably is more straightforward when you do not need to edit the pages before merging them.

Below is the sample code which using append function from PdfFileMerger to append multiple PDF files and write into one PDF file named merged.pdf

from PyPDF2 import PdfFileReader, PdfFileMerger
pdf_file1 = PdfFileReader("file1.pdf")
pdf_file2 = PdfFileReader("file2.pdf")
output = PdfFileMerger()
output.append(pdf_file1)
output.append(pdf_file2)

with open("merged.pdf", "wb") as output_stream:
    output.write(output_stream)

If you do not want to include all pages from your original file, you can specify a tuple with starting and ending page number as pages argument for append function, so that only the pages specified would be add to the new PDF file.

The append function will always add new pages at the end, in case you want to specify the position where you wan to put in your pages, you shall use merge function. It allows you to specify the position of the page where you want to add in the new pages.

Conclusion

PyPDF2 package is a very handy toolkit for editing PDF files. In this article, we have reviewed how we can make use of this library to split or merge PDF files with some sample codes. You can modify these codes to suit your needs in order to automate the task in case you have many files or pages to be processed. There is also a pdfcat script included in this project folder which allows you to split or merge PDF files by calling this script from the command line. You may also want to take a look in case you just simply deal with one or two PDF files each time.

In case you are interested in other topics related to Python automation, you may check here. Thanks for reading.

Pyinstaller upxdir and icon options

In previous article, we have discussed about most of the commonly used options for PyInstaller library. There are two more very useful options but you may encounter some issues when you use them for the first time. In this article, we will discuss about the common issues for using PyInstaller –icon and –upxdir options.

Customize icon for your exe file with –icon

PyInstaller has the –icon option to specify your own icon when creating the executable file. If this option is not given, the exe files will be generated with default icon as per below.

pyinstaller logo

You can use –icon followed by image file name to let PyInstaller to use your own icon. You may see errors when you try to use a normal image format as icon, in this case you can convert your image file into .ico format and run the command again.

For demo purpose, I downloaded an icon from this website into my project folder to use it for my app. And with the below command, I shall be able to get new look for my exe file.

pyinstaller --onefile hello.py --name "SuperHero" --add-data "test.config;." --icon "superhero.icon" --clean

Below is how it looks like when the new exe file generated:

Pyinstaller generate exe with icon

Sometimes, you may also find that the icon did not get changed after you rebuilt the executable file, but when checking the “General” tab in file properties, you are able to see the new icon displayed. This is due to the window icon cache, you may try to delete the cache files from the below directory and retry.

User\AppData\Local\Microsoft\Windows\Explorer\IconCacheToDelete

Or if you specify a new name for your exe file, you shall be able to see the new icon applied.

 

Reduce file size with PyInstaller –upx-dir option

When you used a lot of libraries or resource files, your executable file can grow very big and become difficult for distribution. In this case, you can use upx to compress your exe file.

You can download the upx executable file into your PC and copy the full path as the parameter value for –upx-dir option. E.g.:

pyinstaller --onefile hello.py --name "SuperHero" --add-data "test.config;." --icon "superhero.icon" --upx-dir "c:\upx-3.96-win64" --clean

Sometimes you may find even there is no error when you build the executable file, there can be a runtime error such as the below, which showing that VCRUNTIME140.dll is either not designed to run on Windows or it contains an error.

pyinstaller-VCRUNTIME140.dll-error

This issue is due to PyInstaller modified the dll files during packing and compressing. The workaround is that you use the –upx-exclude to exclude the particular dll files. (No need to specify the path for the dll)

pyinstaller --onefile hello.py --name "SuperHero" --add-data "test.config;." --icon "superhero.icon" --upx-dir "c:\upx-3.96-win64" --upx-exclude "VCRUNTIME140.dll" --clean

Conclusion

Beside the above issues we discussed, you may occasional encounter some other errors, you will need to check  both your Python and PyInstaller versions to see if is it some compatibility issues. And also not all the Python libraries are supported by PyInstaller, you will need to check this list to see if you have used any libraries not in supported by PyInstaller.

python split text with multiple delimiters

Python split text with multiple delimiters

There are cases you want to split a text which possibly use different symbols (delimiters) for separating the different elements, for instance, if the given text is in csv or tsv format, each field can be separated with comma (,) or tab (\t). You will need to write your code logic to support both delimiters. In this article, I will be sharing with you a few possible ways to split text with multiple delimiters in Python.

Checking if certain delimiter exists before splitting

If you are pretty sure the text will only contains one type of delimiter at a time, you can check if such delimiter exists before splitting. e.g. 

text = 'field1,field2,field3,field4'
#or 
text = 'field1;field2;field3;field4'

You can write a one-liner to check if comma exists before splitting by comma, otherwise splitting by semicolon.

text.split(",") if text.find(",") > -1 else text.split(";")

But if there are a lot of possible delimiters can be used in the text, or different delimiters can be mixed in the text, then writing the above if else logic will become very tedious work.  You might have thought about to use the replace function (see the full list of string functions from this article) to replace all the different delimiters into a single delimiter. It may work for your case, but it is far from a elegant solution.

So for such case, let’s move to the second option.

Using re to split text with multiple delimiters

In regular expression module, there is a split function which allows to split by pattern. You can specify all the possible delimiters with “|” to split the text with multiple delimiters at one time.

For instance, the below will extract the field1 to field5 into a list.

import re

text1 = "field1\tfield2,field3;field4 field5"
fields = re.split(r",|;|\s|\t", text1)

The result of fields will be list with all the data fields we want:

['field1', 'field2', 'field3', 'field4', 'field5']

What if you want to also keep these delimiters in the list for later use (e.g. reform back the text) ? You can use the capture groups () in the regular expression, so that the matched patterns will be also showing in the result.

fields = re.split(r'(,|;|\s|\t)', text1)

Result of fields variable:

['field1', '\t', 'field2', ',', 'field3', ';', 'field4', ' ', 'field5']

Conclusion

This quite common that we need write code to split text with multiple delimiters, and there are possibly other ways to solve this problem, but so far using the re.split still the most straightforward and efficient way.