Columns must be same length as key ошибка

To solve this error, check the shape of the object you’re trying to assign the df columns (using np.shape). The second (or the last) dimension must match the number of columns you’re trying to assign to. For example, if you try to assign a 2-column numpy array to 3 columns, you’ll see this error.

A general workaround (for case 1 and case 2 below) is to cast the object you’re trying to assign to a DataFrame and join() it to df, i.e. instead of (1), use (2).

df[cols] = vals   # (1)
df = df.join(vals) if isinstance(vals, pd.DataFrame) else df.join(pd.DataFrame(vals))  # (2)

If you’re trying to replace values in an existing column and got this error (case 3(a) below), convert the object to list and assign.

df[cols] = vals.values.tolist()

If you have duplicate columns (case 3(b) below), then there’s no easy fix. You’ll have to make the dimensions match manually.

This error occurs in 3 cases:

Case 1: When you try to assign a list-like object (e.g. lists, tuples, sets, numpy arrays, and pandas Series) to a list of DataFrame column(s) as new arrays¹ but the number of columns doesn’t match the second (or last) dimension (found using np.shape) of the list-like object. So the following reproduces this error:

df = pd.DataFrame({'A': [0, 1]})
cols, vals = ['B'], [[2], [4, 5]]
df[cols] = vals # number of columns is 1 but the list has shape (2,)

Note that if the columns are not given as list, pandas Series, numpy array or Pandas Index, this error won’t occur. So the following doesn’t reproduce the error:

df[('B',)] = vals # the column is given as a tuple

One interesting edge case occurs when the list-like object is multi-dimensional (but not a numpy array). In that case, under the hood, the object is cast to a pandas DataFrame first and is checked if its last dimension matches the number of columns. This produces the following interesting case:

# the error occurs below because pd.DataFrame(vals1) has shape (2, 2) and len(['B']) != 2
vals1 = [[[2], [3]], [[4], [5]]]
df[cols] = vals1

# no error below because pd.DataFrame(vals2) has shape (2, 1) and len(['B']) == 1
vals2 = [[[[2], [3]]], [[[4], [5]]]]
df[cols] = vals2

Case 2: When you try to assign a DataFrame to a list (or pandas Series or numpy array or pandas Index) of columns but the respective numbers of columns don’t match. This case is what caused the error in the OP. The following reproduce the error:

df = pd.DataFrame({'A': [0, 1]})
df[['B']] = pd.DataFrame([[2, 3], [4]]) # a 2-column df is trying to be assigned to a single column

df[['B', 'C']] = pd.DataFrame([[2], [4]]) # a single column df is trying to be assigned to 2 columns

Case 3: When you try to replace the values of existing column(s) by a DataFrame (or a list-like object) whose number of columns doesn’t match the number of columns it’s replacing. So the following reproduce the error:

# case 3(a)
df1 = pd.DataFrame({'A': [0, 1]})
df1['A'] = pd.DataFrame([[2, 3], [4, 5]]) # df1 has a single column named 'A' but a 2-column-df is trying to be assigned

# case 3(b): duplicate column names matter too
df2 = pd.DataFrame([[0, 1], [2, 3]], columns=['A','A'])
df2['A'] = pd.DataFrame([[2], [4]]) # df2 has 2 columns named 'A' but a single column df is being assigned

¹: df.loc[:, cols] = vals may overwrite data inplace, so this won’t produce the error but will create columns of NaN values.

Estimated reading time: 3 minutes

Are you looking to learn python , and in the process coming across this error and trying to understand why it occurs?

In essence, this usually occurs when you have more than one data frames and in the process of writing your program you are trying to use the data frames and their data, but there is a mismatch in the no of items in each that the program cannot process until it is fixed.

A common scenario where this may happen is when you are joining data frames or splitting out data, these will be demonstrated below.

Scenario 1 – Joining data frames

Where we have df1[[‘a’]] = df2 we are assigning the values on the left side of the equals sign to what is on the right.

When we look at the right-hand side it has three columns, the left-hand side has one.

As a result the error “ValueError: Columns must be same length as key” will appear, as per the below.

import pandas as pd

list1 = [1,2,3]
list2 = [[4,5,6],[7,8,9]]

df1 = pd.DataFrame(list1,columns=['column1'])
df2 = pd.DataFrame(list2,columns=['column2','column3','column4'])

df1[['a']] = df2

The above code throws the below error:

The objective here is to have all the columns from the right-hand side, beside the columns from the left-hand side as follows:

What we have done is make both sides equal regards the no of columns to be shown from df2
Essentially we are taking the column from DF1, and then bringing in the three columns from DF2.
The columna, columnb, columnc below correspond to the three columns in DF2, and will store the data from them.

The fix for this issue is : df1[[‘columna’,’columnb’,’columnc’]] = df2

print (df1)

Scenario 2 – Splitting out data

There may be an occasion where you have a python list, and you need to split out the values of that list into separate columns.

new_list1 = ['1 2 3']
df1_newlist = pd.DataFrame(new_list1,columns=['column1'])

In the above, we have created a list, with three values that are part of one string. Here what we are looking to do is create a new column with the below code:

df1_newlist[["column1"]] = df1_newlist["column1"].str.split(" ", expand=True) #Splitting based on the space between the values.

print(df1_newlist)

When we run the above it throws the following valueerror:

The reason it throws the error is that the logic has three values to be split out into three columns, but we have only defined one column in df1_newlist[[“column1”]]

To fix this, we run the below code:

df1_newlist[["column1","column2","column3"]] = df1_newlist["column1"].str.split(" ", expand=True) #Splitting based on the space between the values.

print(df1_newlist)

This returns the following output, with the problem fixed!

This error happens when you try to assign a data frame as columns to another data frame, and the number of column names provided is not equal to the number of columns of the assignee data frame. e.g. given a simple data frame as follows:

df = pd.DataFrame({'x': [1,2,3]})

df
#   x
#0  1
#1  2
#2  3

And a second data frame:

df1 = pd.DataFrame([[1, 2], [3, 4], [5, 6]])

df1
#   0  1
#0  1  2
#1  3  4
#2  5  6

It is possible to assign df1 to df as columns as follows:

df[['a', 'b']] = df1

df
#   x  a  b
#0  1  1  2
#1  2  3  4
#2  3  5  6

Notice how the content of df1 has been assigned as two separate columns to df.

Now given a third data frame with only one column, on the other hand:

df2 = pd.DataFrame([[1], [3], [5]])
df2
#   0
#0  1
#1  3
#2  5

If you do df[['a', 'b']] = df2, you will get an error:

ValueError: Columns must be same length as key

The reason being that df2 has only one column, but on the left side of the assignment, you are expecting two columns to be assigned, hence the error.

Solution:

Whenever you encounter this error, just make sure you check the number of columns to be assigned to is the same of the number of columns in the assignee data frame. In our above example, for instance, if you have ['a', 'b‘] on the left side as columns, then make sure you also have a data frame that has two columns on the right side.

Bonus: Want to play around pandas and python in the browser without going through any complex set up, try the PyConsole browser extension:

1. Install chrome extension
2. Install firefox extension

Please consider writing a review and sharing it with other people if you like it 🙂

I used groupby() and ffill() to fill NULL for the DataFrame as below. The code works in other Python environment, but it failed when I switch to another Python environment with error message as :»ValueError: Columns must be same length as key».

The DataFrame:

ID     Var1    Var2    Var3     Var4
 1      AA      BB      CC        DD
 1      AA      NULL    NULL      NULL
 1      NULL    BB      CE        NULL
 2      AB      ED      EE        FF
 2      AB      NULL    EH        FG
 2      NULL    EC      NULL      NULL
 3      EG      FH      HG        FJ
 3      NULL    NULL    HJ        FK
 3      ER      FH      NULL      NULL

I tried to use the code to fill first 3 variables:
df[['ID','Var1','Var2','Var3']]=df[['ID','Var1','Var2','Var3']].groupby(['ID'], sort=False, as_index=False).ffill()

ID     Var1    Var2    Var3     Var4
 1      AA      BB      CC        DD
 1      AA      BB      CC        NULL
 1      AA      BB      CE        NULL
 2      AB      ED      EE        FF
 2      AB      ED      EH        FG
 2      AB      EC      EH        NULL
 3      EG      FH      HG        FJ
 3      EG      FH      HJ        FK
 3      ER      FH      HJ        NULL

The error shows:

ValueError: Columns must be same length as key

I used groupby() and ffill() to fill NULL for the DataFrame as below. The code works in other Python environment, but it failed when I switch to another Python environment with error message as :»ValueError: Columns must be same length as key».

The DataFrame:

ID     Var1    Var2    Var3     Var4
 1      AA      BB      CC        DD
 1      AA      NULL    NULL      NULL
 1      NULL    BB      CE        NULL
 2      AB      ED      EE        FF
 2      AB      NULL    EH        FG
 2      NULL    EC      NULL      NULL
 3      EG      FH      HG        FJ
 3      NULL    NULL    HJ        FK
 3      ER      FH      NULL      NULL

I tried to use the code to fill first 3 variables:
df[['ID','Var1','Var2','Var3']]=df[['ID','Var1','Var2','Var3']].groupby(['ID'], sort=False, as_index=False).ffill()

ID     Var1    Var2    Var3     Var4
 1      AA      BB      CC        DD
 1      AA      BB      CC        NULL
 1      AA      BB      CE        NULL
 2      AB      ED      EE        FF
 2      AB      ED      EH        FG
 2      AB      EC      EH        NULL
 3      EG      FH      HG        FJ
 3      EG      FH      HJ        FK
 3      ER      FH      HJ        NULL

The error shows:

ValueError: Columns must be same length as key

pandas is expecting two values (columns) to be returned as you specified two keys ['Latitude', 'Longitude'].

However, when there’s an exception, e.g. no geocoding result, you only return a single value None. So you get ValueError: Columns must be same length as key

Return two values when there’s a geocoding error, i.e. None, None and you can filter them out later:

from geopy.geocoders import Nominatim
import pandas as pd
import numpy as np


def my_geocoder(row):
    try:
        point = geolocator.geocode(row).point
        return pd.Series({'Latitude': point.latitude, 'Longitude': point.longitude})
    except:
        return None, None


geolocator = Nominatim(user_agent = 'ed')
universities = pd.read_csv("test.csv")
universities[['Latitude', 'Longitude']] = universities.apply(lambda x: my_geocoder(x['Name']), axis=1)

print(universities.head())

Output:

                             Name   Latitude   Longitude
0  Australian National University -35.281213  149.116779
1            University of Sydney -33.896083  151.184661
2                            RMIT -37.682204  145.068329
3              No Such University        NaN         NaN

Hi, I got this error when running the command in the instruction.
python moff_all.py --config_file absence_peak_data/configuration_iRT.ini

No module named 'brainpy._c.composition'
Matching between run module (mbr)
MBR Output folder in : /hwfssz5/ST_CANCER/CGR/USER/zhuyafeng/moFF/moFF/absence_peak_data/mbr_output
B002419_Ap_22cm_iRT_PRC-Hans_equimolar_100fmol_inYeast.txt
B002413_Ap_22cm_Yeast_171215184201.txt
B002417_Ap_22cm_iRT_PRC-Hans_equimolar_100fmol.txt
B002421_Ap_22cm_iRT_PRC-Hans_equimolar_100fmol.txt
Reading file: absence_peak_data/B002419_Ap_22cm_iRT_PRC-Hans_equimolar_100fmol_inYeast.txt
Reading file: absence_peak_data/B002413_Ap_22cm_Yeast_171215184201.txt
Reading file: absence_peak_data/B002417_Ap_22cm_iRT_PRC-Hans_equimolar_100fmol.txt
Reading file: absence_peak_data/B002421_Ap_22cm_iRT_PRC-Hans_equimolar_100fmol.txt
Read input --> done
matched features   2449  MS2 features  5815
matched features   1272  MS2 features  7084
matched features   8321  MS2 features  106
matched features   8296  MS2 features  128
Apex module...
Starting Apex for absence_peak_data/mbr_output/B002419_Ap_22cm_iRT_PRC-Hans_equimolar_100fmol_inYeast_match.txt ...
moff Input file: absence_peak_data/mbr_output/B002419_Ap_22cm_iRT_PRC-Hans_equimolar_100fmol_inYeast_match.txt  XIC_tol 5.0 XIC_win 4.0000 moff_rtWin_peak 1.0000
RAW file from folder :  absence_peak_data/raw_repo/
Output file in :  absence_peak_data/output
Apex module has detected mbr peptides
starting estimation of quality measures..
quality measures estimation  using 582  MS2 ident. peptides randomly sampled
MAD retention time along all isotope count    479.000000
mean       0.847468
std        1.503114
min        0.000000
25%        0.000000
50%        0.674053
75%        0.832173
max       11.003640
Name: RT_drift, dtype: float64
Estimated distribition ratio exp. int. left isotope vs. monoisotopic isotope count    151.000000
mean       0.813199
std        0.113688
min        0.578109
25%        0.732179
50%        0.811201
75%        0.886644
max        1.056126
Name: delta_log_int, dtype: float64
quality threhsold estimated : MAD_retetion_time 1.2549080000000081  Ratio Int. FakeIsotope/1estIsotope: 0.9336942595876153
starting apex quantification of MS2 peptides..
end  apex quantification of MS2 peptides..
starting quantification with matched peaks using the quality filtering...
initial # matched peaks: (2449, 15)
end apex quantification matched peptide
Computational time (sec):  1011.8213
after filtering matched peak #805
Starting Apex for absence_peak_data/mbr_output/B002413_Ap_22cm_Yeast_171215184201_match.txt ...
moff Input file: absence_peak_data/mbr_output/B002413_Ap_22cm_Yeast_171215184201_match.txt  XIC_tol 5.0 XIC_win 4.0000 moff_rtWin_peak 1.0000
RAW file from folder :  absence_peak_data/raw_repo/
Output file in :  absence_peak_data/output
Apex module has detected mbr peptides
starting estimation of quality measures..
quality measures estimation  using 708  MS2 ident. peptides randomly sampled
MAD retention time along all isotope count    581.000000
mean       0.932257
std        1.708456
min        0.000000
25%        0.000000
50%        0.548507
75%        0.827493
max       10.349933
Name: RT_drift, dtype: float64
Estimated distribition ratio exp. int. left isotope vs. monoisotopic isotope count    230.000000
mean       0.817862
std        0.125736
min        0.497536
25%        0.728463
50%        0.823870
75%        0.904802
max        1.147108
Name: delta_log_int, dtype: float64
quality threhsold estimated : MAD_retetion_time 1.2892266666667638  Ratio Int. FakeIsotope/1estIsotope: 0.9483079412122462
starting apex quantification of MS2 peptides..
end  apex quantification of MS2 peptides..
starting quantification with matched peaks using the quality filtering...
initial # matched peaks: (1272, 15)
end apex quantification matched peptide
Computational time (sec):  1183.7997
after filtering matched peak #450
Starting Apex for absence_peak_data/mbr_output/B002417_Ap_22cm_iRT_PRC-Hans_equimolar_100fmol_match.txt ...
moff Input file: absence_peak_data/mbr_output/B002417_Ap_22cm_iRT_PRC-Hans_equimolar_100fmol_match.txt  XIC_tol 5.0 XIC_win 4.0000 moff_rtWin_peak 1.0000
RAW file from folder :  absence_peak_data/raw_repo/
Output file in :  absence_peak_data/output
Apex module has detected mbr peptides
starting estimation of quality measures..
quality measures estimation  using 11  MS2 ident. peptides randomly sampled
Traceback (most recent call last):
  File "/hwfssz5/ST_CANCER/CGR/USER/zhuyafeng/moFF/moFF/moff.py", line 863, in apex_multithr
    h_rt_w, s_w, s_w_match, offset_index), axis=1)
  File "/hwfssz1/ST_CANCER/POL/SHARE/tools/miniconda3/lib/python3.6/site-packages/pandas/core/frame.py", line 3116, in __setitem__
    self._setitem_array(key, value)
  File "/hwfssz1/ST_CANCER/POL/SHARE/tools/miniconda3/lib/python3.6/site-packages/pandas/core/frame.py", line 3138, in _setitem_array
    raise ValueError('Columns must be same length as key')
Traceback (most recent call last):
ValueError: Columns must be same length as key

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/hwfssz5/ST_CANCER/CGR/USER/zhuyafeng/moFF/moFF/moff.py", line 133, in save_moff_apex_result
    if result[df_index].get()[1] == -1:
  File "/hwfssz1/ST_CANCER/POL/SHARE/tools/miniconda3/lib/python3.6/multiprocessing/pool.py", line 644, in get
    raise self._value
ValueError: Columns must be same length as key
multiprocessing.pool.RemoteTraceback:
"""
Traceback (most recent call last):
  File "/hwfssz1/ST_CANCER/POL/SHARE/tools/miniconda3/lib/python3.6/multiprocessing/pool.py", line 119, in worker
    result = (True, func(*args, **kwds))
  File "/hwfssz5/ST_CANCER/CGR/USER/zhuyafeng/moFF/moFF/moff.py", line 888, in apex_multithr
    raise e
  File "/hwfssz5/ST_CANCER/CGR/USER/zhuyafeng/moFF/moFF/moff.py", line 863, in apex_multithr
    h_rt_w, s_w, s_w_match, offset_index), axis=1)
  File "/hwfssz1/ST_CANCER/POL/SHARE/tools/miniconda3/lib/python3.6/site-packages/pandas/core/frame.py", line 3116, in __setitem__
    self._setitem_array(key, value)
  File "/hwfssz1/ST_CANCER/POL/SHARE/tools/miniconda3/lib/python3.6/site-packages/pandas/core/frame.py", line 3138, in _setitem_array
    raise ValueError('Columns must be same length as key')
ValueError: Columns must be same length as key
"""

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "moff_all.py", line 374, in <module>
    ptm_map, args.sample_size, args.quantile_thr_filtering, args.match_filter, log_file, num_CPU)
  File "/hwfssz5/ST_CANCER/CGR/USER/zhuyafeng/moFF/moFF/moff.py", line 539, in estimate_parameter
    ms2_data = save_moff_apex_result(result)
  File "/hwfssz5/ST_CANCER/CGR/USER/zhuyafeng/moFF/moFF/moff.py", line 144, in save_moff_apex_result
    raise e
  File "/hwfssz5/ST_CANCER/CGR/USER/zhuyafeng/moFF/moFF/moff.py", line 133, in save_moff_apex_result
    if result[df_index].get()[1] == -1:
  File "/hwfssz1/ST_CANCER/POL/SHARE/tools/miniconda3/lib/python3.6/multiprocessing/pool.py", line 644, in get
    raise self._value
ValueError: Columns must be same length as key