Pandas Refresher: Using Join on Tables

Business Intelligence and analytics services are more than trendy concepts; they're in growing demand in an economy that is in dire need of data-driven decisions and solutions. To achieve true and effective business intelligence and analytics, you need numerous information systems for massive data processing. Additionally, you need to change the reference terms for these heavy-duty information systems several times a day, as businesses need to have operational data about specific parameters to quickly respond and adapt to shifting markets and external conditions. For this purpose, it is common practice to use modern databases, be it relational or NoSQL, to quickly and efficiently build multiple dependencies between large entities. To the rescue, we call upon reliable tools like Python and Pandas. To top it off, it very often happens that certain information is needed immediately and only once, even when dealing with critical business information, key parameters, or market Intel. Tomorrow, that data may no longer be relevant, and you will need to process new data in a new format. Unfortunately, the development cycle of these types of relational databases can be quite costly and time-consuming. Luckily, you can leverage smart tools to help you alleviate these concerns. By writing a script in Python and pairing it with the Pandas library, you'll be able to solve the problem relatively quickly while yielding accurate results for higher consumption. Let's take a look at how you can process data from various tables in Pandas. How to load tables in Pandas The first and most important operation when it comes to data is loading tables. The Pandas library uses a very compact and efficient method of loading tables, where the most common format is CSV. pandas pd df = pd.read_table( ) (df) import as 'data.csv' print Pandas can read SQL data as well: ... pd.read_sql_table( , ) ... 'table_name' 'postgres:///db_name' Why do we need to join data in tables? Relational databases are designed to join data in tables. Each table in the database contains data of a certain form and content. To get a new representation of information, it is necessary to combine data and obtain tables with different columns and lines. Please refer to the following article for more information about joins in SQL, “ ” written by C.L. Moffatt. In it, the author proposed the following visual notation of joins: Visual Representation of SQL Joins Image from: https://www.codeproject.com/Articles/33052/Visual-Representation-of-SQL-Joins When combining two tables, they are linked using the chosen characteristic to get a new table or view. Keep in mind that the column used to combine the tables should contain only unique values. Let’s define two tables with some information for the Pandas join experiment. pandas pd df1 = pd.DataFrame({ : [ , , , , ], : [ , , , , ]}) df2 = pd.DataFrame({ : [ , , , , ], : [ , , , , ]}) import as 'lkey' 'Alice' 'Brian' 'Joe' 'Jessica' 'Nick' 'age' 18 21 30 25 33 'rkey' 'Alice' 'Brian' 'Joe' 'Jessica' 'Zac' 'LastName' 'Smith' 'Simson' 'Goodman' 'Hammond' "Bell" We will make all possible variants of joins in Panda: r, and we'll two tables. Pandas documentation states that “JOINs can be performed with or . left, right, inner, oute merge join() merge() By default, will join the DataFrames on their indices. Each method has parameters allowing you to specify the type of join to perform (LEFT, RIGHT, INNER, FULL) or the columns to join on (column names or indices).” join() Please refer to “ ” if you need more information about similarities and differences between SQL and Pandas. Another great article to read is “ ”. Comparison with SQL Pandas vs SQL in 5 Examples Left join Let’ start with left joining. Join two tables by column in the dataframe and by column in the dataframe . lkey df1 rkey df2 df1.set_index( ).join(df2.set_index( )) 'lkey' 'rkey' If you do not have a record in the right table, will be marked in the corresponding column after joining. Nick NaN Right join With the right joining, you can join two tables by column in the dataframe and by column in the dataframe with the right intersection from the table. lkey df1 rkey df2 df1.set_index( ).join(df2.set_index( ), how= ) 'lkey' 'rkey' "right" If you do not have a record in the right table, will be marked in the corresponding column after joining. Zac NaN Outer join Outer joining allows you to join two tables and includes all lines that are present in both tables. will be placed in the proper column if you don’t have such records on the left side or on the right side. NaN df1.set_index( ).join(df2.set_index( ), how= ) 'lkey' 'rkey' "outer" If you do not have a Nick record in the right table, will be marked in the column . If you do not have a record in the left table, will be marked in the column after joining. NaN LastName Zac NaN age Inner join Inner join allows you to get all the records from the tables that are present on the left table and right table. df1.set_index( ).join(df2.set_index( ), how= ) 'lkey' 'rkey' "inner" In this case, you won't have lines with and . Nick Zac Merging data in tables If you need to keep all columns from the left and right tables; and perform inner joining, Pandas allows you to perform functions like merge: df1.merge(df2, left_on= , right_on= ) 'lkey' 'rkey' By default, merge uses the inner variant of table union. In this case, you will have , and , age columns. This is very useful when merging data in tables. Alice , Brian , Joe , Jessica lkey rkey LastName Conclusion In this article, we aimed to cover the basic techniques for joining tables in Pandas. This is very convenient since you get new dataframes (i.e. tables) as the output. What's more, this information is conveniently processed by all available Pandas algorithms. When it comes to speed, you will be satisfied with how Pandas works with your data volumes. Of course, Pandas will run slower than SQL queries at some point, but when large amounts of data are involved, it is worth switching to NoSQL databases for quick information retrieval in the required output you need. Pandas will be an indispensable tool if you need to quickly and efficiently solve a problem in data science or data analytics. Oftentimes, you need to perform the joining of tables only once, since you will likely need a new set of tasks for your information the next day. It is worth spending time writing and debugging SQL queries when you run this code quite often and on large amounts of data.