Functions

Text

CONCAT

How to Use Excel's CONCAT Function in Pandas

Related Functions

JOIN MERGE APPEND

Excel's CONCAT function combines two or more strings into one. It's a powerful tool in text-based data analysis, and is often used to combine fields or create unique identifiers.

This page explains how to implement Excel's CONCAT function in Python using pandas.

Use Mito's CONCAT function

Mito is an open source library that lets you write Excel formulas in Python. Either write the formula directly in Python or use the CONCAT formula in the Mito Spreadsheet and generate the equivalent Python code automatically.

Mito's CONCAT function works exactly like it does in Excel. That means you don't need worry about managing data types, handling errors, or the edge case differences between Excel and Python formulas.

Install Mito to start using Excel formulas in Python.

# Import the mitosheet Excel functions
from mitosheet.public.v3 import *;

# Use Mito's CONCAT function
df['Combined'] = CONCAT(df['StringCol'], df['NumberCol'])

Copy!

Implementing the String Concatenation function in Pandas#

To replicate the CONCAT function in Excel using Python and pandas, there are several methods depending on the specific need. Here are some common implementations:

Basic Column Concatenation#

Combining values in a row is similar to entering =CONCAT(A1, B1) in Excel.

The following code shows how to concatenate values in columns 'Col1' and 'Col2' into a new column 'Combined':

df['Combined'] = df['Col1'] + df['Col2']

Copy!

Adding a Separator#

To introduce a separator, like a space or a hyphen, between concatenated values in Excel, you would use =CONCAT(A1, "-", B1).

Similarly in pandas, you'd add the separator string in between columns:

df['Combined'] = df['Col1'] + '-' + df['Col2']

Copy!

Concatenating Numbers and Strings#

If you need to concatenate numbers with strings, it's essential to convert numbers to string format first.

Excel takes care of data type handling for you, so you could write the formula =CONCAT(A1, B1) where A1 is text and B1 is a number without any problems. However, in Pandas, you need to explicitly convert the number to a string before concatenating:

df['Combined'] = df['StringCol'] + df['NumberCol'].astype(str)

Copy!

Conditional Concatenation#

There might be scenarios where you want to concatenate only if a certain condition is met.

For example, in Excel: =IF(C1>10, CONCAT(A1, B1), A1). In pandas, you can use the `.loc` to conditionally select columns that meet a criteria:

df.loc[df['C'] > 10, 'Combined'] = df['A'] + df['B']
df.loc[df['C'] <= 10, 'Combined'] = df['A']

Copy!

Common mistakes when using CONCAT in Python#

While using pandas for concatenation, there are certain pitfalls that might be encountered. Here are some common mistakes and their resolutions.

Inconsistent Data Types#

Excel formulas handle data types dynamically, so you could write the formula =CONCAT(A1, B1) where A1 is text and B1 is a number without any problems. However, in Pandas, attempting to concatenate columns with different data types raises a TypeError.

You should explicitly convert any non-string columns to strings before concatenating:

df['Combined'] = df['Col1'].astype(str) + df['Col2'].astype(str)

Copy!

NaN Values#

Again, Excel takes care of missing values for you, but in pandas, you need to explicitly handle them.

If any of the values in the columns being concatenated are NaN, the result will be NaN. To avoid this, use the `fillna()` method to replace NaN values with empty strings.

df['Combined'] = df['Col1'].fillna('') + df['Col2'].fillna('')

Copy!

Understanding the String Concatenation Formula in Excel#

The CONCAT function in Excel takes two or more string arguments and combines them into one continuous string.

=CONCAT(text1, [text2], ...)

CONCAT Excel Syntax

Parameter	Description	Data Type
text1	The first text value you want to concatenate.	string
text2	(Optional) Additional text values to concatenate.	string
...	(Optional) Add up to 255 text values.	string

Examples

Formula	Description	Result
=CONCAT("Hello", " ", "World")	Combine the words 'Hello' and 'World' with a space in between.	Hello World
=CONCAT(A1, "-", B1)	Combine the words in A1 and B1 with a - in between.	value_of_A1-value_of_B1

Don't re-invent the wheel. Use Excel formulas in Python.

Install Mito