How to Use Excel's LEFT Function in Pandas

Related Functions

RIGHT MID LEN

Excel's LEFT function allows you to extract a specific number of characters from the start of a text string. This is commonly used in data preparation tasks such as splitting columns, extracting codes, or cleaning up data.

This page explains how to use Excel's LEFT function in Python using pandas

Use Mito's LEFT 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 LEFT formula in the Mito Spreadsheet and generate the equivalent Python code automatically.

Mito's LEFT 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 LEFT function
# Note: We don't need to first convert the column to a 
# string because Mito's LEFT function does so automatically
df['extracted'] = LEFT(df['text_column'], 2)

Copy!

Implementing the Extract Left Characters function in Pandas#

In pandas, replicating the LEFT function's behavior involves using string slicing or the str accessor. Here are ways to achieve the functionality:

Extracting First N Characters#

In Excel, you would use =LEFT(A1, 2) to extract the first two letters from A1. To achieve the same in pandas, you can use string slicing.

The following code extracts the first two characters from the 'text_column' of a DataFrame:

df['extracted'] = df['text_column'].str[:2]

Copy!

Extracting Characters up to another character#

Often you will not want to extract the same number of characters for each cell. For example, if you're extracting the first name from a full name, you'll want to split on the first space.

In Excel, you would use the formula =LEFT(A1, FIND(" ", A1) - 1) to extract the first name from a full name in A1.

In pandas, you can use the 'str.split()' method to split the string on a space, and then use string slicing to extract the first element of the resulting list.

# Split the full name on the space and extract the firt name
df['first name'] = df['full name'].str.split(' ').str[0]

Copy!

Common mistakes when using LEFT in Python#

While implementing the equivalent of the LEFT function in pandas, there are potential pitfalls. Here are some common mistakes and their remedies.

Misunderstanding String Indices#

In Python, string indexing starts at 0, not 1. This can be a source of confusion for those transitioning from Excel.

For instance, to get the first character, you'd use index 0, ie: string[0], not string[1].

Wrong Data Type#

Applying string operations to a column that isn't of string type will raise an error. Always ensure that the column you're working with is of type string (object in pandas terms).

You can convert a column to a string using the 'astype' method.

# Convert to string type
df['column_name'] = df['column_name'].astype(str)

# Extract first 2 characters
df['extracted'] = df['column_name'].str[:2]

Copy!

Understanding the Extract Left Characters Formula in Excel#

The LEFT function in Excel returns the first N characters in a text string, based on the number of characters you specify.

=LEFT(text, [num_chars])

LEFT Excel Syntax

Parameter	Description	Data Type
text	The text string containing the characters you want to extract.	string
num_chars	(Optional) The number characters you want to extract. Defaults to 1.	number