Skip to main content

How to Solve "ValueError: Columns must be same length as key" in Pandas

The ValueError: Columns must be same length as key error in Pandas occurs when attempting to assign values to new columns in a DataFrame, but the number of values being assigned doesn't match the number of rows in the DataFrame, or when using str.split() and the resulting number of columns does not match the number of columns being assigned to.

This guide explores common causes and solutions for this error, ensuring your data assignments are accurate and error-free.

Understanding the Error

The error arises when you're creating new columns in a DataFrame by assigning a list or Series of values. Pandas expects the number of values to precisely match the number of rows in the DataFrame. If the numbers don't match, the ValueError: Columns must be same length as key will be raised.

import pandas as pd

df1 = pd.DataFrame({
'column1': ['Anna', 'Bob', 'Carl', 'Dan'],
'column2': [29, 30, 31, 32]
})

df2 = pd.DataFrame({
'column1': [100, 200, 300]
})

try:
df1[['column3', 'column4']] = df2['column1']
except ValueError as e: # Columns must be same length as key!
print(e) # Must have equal len keys and value when setting with an iterable
  • df1[['column3', 'column4']] = df2['column1'] will raise an exception because the dataframe df1 has 4 rows, while the column1 series on df2 has only 3 elements, and the number of columns on the left-hand side of the equal sign is 2.

Solutions

To resolve the error, ensure the number of values being assigned matches the number of rows in the DataFrame, or make sure that the number of columns that you assign to is correct.

Ensure Value Length Matches Row Length

The most common fix is to either add more values so that the number of values will match the row number, or assign to only one row:

import pandas as pd

df1 = pd.DataFrame({
'column1': ['Anna', 'Bob', 'Carl', 'Dan'],
'column2': [29, 30, 31, 32]
})

df2 = pd.DataFrame({
'column1': [100, 200, 300, 400] # Add a value for each row
})
df1[['column3']] = df2['column1'] # and also correct the number of columns being assigned to

print(df1)

Output:

  column1  column2  column3
0 Anna 29 100
1 Bob 30 200
2 Carl 31 300
3 Dan 32 400

Addressing Mismatched Columns with str.split()

When you're using str.split() to create new columns, the number of splits might not match the number of columns you're assigning to. Set expand=True and ensure you have the correct number of target columns.

import pandas as pd

df1 = pd.DataFrame({
'column1': ['Anna', 'Bob', 'Carl', 'Dan'],
'column2': [29, 30, 31, 32]
})

df2 = pd.DataFrame({
'column1': ['1. AB', '2. CD', '3. EF', '4. GH']
})

df1[['column3', 'column4']] = df2['column1'].str.split('.', n=1, expand=True)

print(df1)

Output:

  column1  column2 column3 column4
0 Anna 29 1 AB
1 Bob 30 2 CD
2 Carl 31 3 EF
3 Dan 32 4 GH

Alternatives

Copying a Column from One DataFrame to Another

If your goal is simply to copy data from one DataFrame column to another, make sure that you are assigning the data to only one column:

import pandas as pd

df1 = pd.DataFrame({
'column1': ['Anna', 'Bob', 'Carl', 'Dan'],
'column2': [29, 30, 31, 32]
})

df2 = pd.DataFrame({
'column1': [100, 200, 300]
})

df1['column3'] = df2['column1']
print(df1)

Output:

  column1  column2  column3
0 Anna 29 100.0
1 Bob 30 200.0
2 Carl 31 300.0
3 Dan 32 NaN
  • If the number of elements don't match, NaN values will be added.

Conclusion

The ValueError: Columns must be same length as key can be addressed by carefully ensuring that the number of values you're assigning to new columns matches the DataFrame's number of rows.

  • You should also make sure that you assign to the proper number of columns, and if needed, add the data to a temporary variable to check its size before performing any assignment.
  • Pay particular attention when using chained operations like str.split() , as they can be more complex.