In December 2019 my InfoWorld colleague Sharon Machlis wrote an article called “How to merge data in R using R merge, dplyr, or data.table.” Sharon is a whiz at R programming, and analytics in general, which comes through in the article. But suppose you’re planning on doing machine learning or deep learning on the data using Python and (for example) Scikit-learn, PyTorch, or TensorFlow? While it’s possible to pass data from R to Python, it’s not the best solution to the problem.

In this article, I’ll discuss how to accomplish data merging natively in Python, which will make it easy to pass the in-memory merged dataset on to one of the Python AI frameworks. I’ll use the same public datasets as Sharon did, which record US airline flight delays, but I’ll stick to the best framework for the purpose, which is currently Pandas.

[ Tune into Serdar Yegulalp’s Smart Python video tutorials to learn smart Python tricks in 5 minutes or less ]

Pandas and the DataFrame

Pandas is a BSD-licensed open source library that provides high-performance, easy-to-use data structures and data analysis tools for Python. The R dataframe data structure has a close equivalent in Pandas, the DataFrame. As the Pandas project describes it:

DataFrame is a two-dimensional labeled data structure with columns of potentially different types. You can think of it like a spreadsheet or SQL table, or a dict of Series objects. It is generally the most commonly used Pandas object.

Pandas merge

The Pandas method for joining two DataFrame objects is merge(), which is the single entry point for all standard database join operations between DataFrame or named Series objects. (Series objects are one-dimensional.) The merge method specification is:

pd.merge(left, right, how='inner', on=None, left_on=None, right_on=None,
             left_index=False, right_index=False, sort=True,
             suffixes=('_x', '_y'), copy=True, indicator=False,
             validate=None)
  • left : A DataFrame or named Series object.
  • right : Another DataFrame or named Series object.
  • on : Column or index level names to join on. Must be found in both the left and right DataFrame and/or Series objects. If not passed and left_index and right_index are False, the intersection of the columns in the DataFrames and/or Series will be inferred to be the join keys.
  • left_on : Columns or index levels from the left DataFrame or Series to use as keys. Can either be column names, index level names, or arrays with length equal to the length of the DataFrame or Series.
  • right_on : Columns or index levels from the right DataFrame or Series to use as keys. Can either be column names, index level names, or arrays with length equal to the length of the DataFrame or Series.
  • left_index : If True, use the index (row labels) from the left DataFrame or Series as its join key(s). In the case of a DataFrame or Series with a MultiIndex (hierarchical), the number of levels must match the number of join keys from the right DataFrame or Series.
  • right_index : Same usage as left_index for the right DataFrame or Series
  • how : One of left, right, ‘outer, or inner. Defaults to inner. See below for more detailed description of each method.
  • sort : Sort the result DataFrame by the join keys in lexicographical order. Defaults to True; setting to False will improve performance substantially in many cases.
  • suffixes : A tuple of string suffixes to apply to overlapping columns. Defaults to ('_x', '_y').
  • copy : Always copy data (default True) from the passed DataFrame or named Series objects, even when reindexing is not necessary. Cannot be avoided in many cases but may improve performance and memory usage. The cases where copying can be avoided are somewhat pathological but this option is provided nonetheless.
  • indicator : Add a column to the output DataFrame called _merge with information on the source of each row. _merge is Categorical-type and takes on a value of left_only for observations whose merge key only appears in left DataFrame or Series, right_only for observations whose merge key only appears in right DataFrame or Series, and both if the observation’s merge key is found in both.
  • validate : string, default None. If specified, checks if merge is of specified type.
    • one_to_one or 1:1 : checks if merge keys are unique in both left and right datasets.
    • one_to_many or 1:m : checks if merge keys are unique in left dataset.
    • many_to_one or m:1 : checks if merge keys are unique in right dataset.
    • many_to_many or m:m : allowed, but does not result in checks.

Read a CSV file in Pandas

As you might expect, Pandas has a method for reading CSV files, pd.read_csv(), which returns a DataFrame. It has many optional arguments, but for our purposes only the basic arguments are required:

filepath_or_buffer : various