UNION (ALL) in pandas

Posted by Kevin

Selecting data from multiple SQL tables through a UNION or UNION ALL, translated to pandas.

Appending tables using UNION ALL in pandas

In SQL:

SELECT
  *
FROM
  table_1
UNION ALL
SELECT
  *
FROM
  table_2;

In pandas:

pd.concat([table_1, table_2], ignore_index=True)

Alternatively:

table_1
    .append(table_2, ignore_index=True)

The parameter ignore_index resets the index to a new one for the result set, avoiding duplicate index numbers.

Appending tables using UNION in pandas

In SQL:

SELECT
  *
FROM
  table_1
UNION
SELECT
  *
FROM
  table_2;

In pandas:

pd.concat([table_1, table_2], ignore_index=True)
    .drop_duplicates()
    .reset_index(drop=True)

Alternatively:

table_1
    .append(table_2, ignore_index=True)
    .drop_duplicates()
    .reset_index(drop=True)

The .reset_index(drop=True) method makes sure we have a new index running from 0 to n-1.

References