How to prevent "None of [MultiIndex...] are in the [columns]" in Pandas?

user_880783 0
Viewed 758

In the example code below, I am adding a NumPy array to a Pandas DataFrame. To avoid a SettingWithCopyWarning, I rewrote the code, but now I am getting a KeyError instead:

"None of [MultiIndex([('new', 0),\n ('new', 1),\n ('new', 2)],\n )] are in the [columns]"

This is not wrong, as the given columns are not in the columns. But why do they need to be?

import numpy as np
import pandas as pd

df_ = pd.DataFrame(range(10), columns=[["old"], [0]])
df = df_[df_.old[0] < 4]

arr = np.random.randn(4, 3)
indices = pd.MultiIndex.from_product([["new"], range(arr.shape[-1])])

# A value is trying to be set on a copy of a slice from a DataFrame.
# Try using .loc[row_indexer,col_indexer] = value instead
# df[indices] = pd.DataFrame(arr, index=df.index)

# OK - I did that! This works after the previous line, but not without it:
df.loc[:, indices] = pd.DataFrame(arr, index=df.index)
pandas dataframe numpy arrays python
2 Answers

The problem is here:

df_ = pd.DataFrame(range(10), columns=[["old"], [0]])
df = df_[df_.old[0] < 4]

where df is a slice of df_. Copy it:

df_ = pd.DataFrame(range(10), columns=[["old"], [0]])
df = df_[df_.old[0] < 4].copy()

Update: per your comment,

df.loc[:, indices] = pd.DataFrame(arr, index=df.index)

doesn't work because they have different columns. The left hand side has MultiIndex columns indices while the right hand side has RangeIndex columns 0,1,2. Should it work if it were:

df.loc[:, indices] = pd.DataFrame(arr, index=df.index, columns=indices)

or

df.loc[:, indices] = arr
user_42384080

This is an interesting question about the "None of MultiIndex... are in the [columns]" error.

I made a simple test (to remove the SettingWithCopyWarning factor) with single-index columns, and it worked fine to use df.loc with non-existent column labels. But if you do the exact same commands with a list of multi-index column labels, it fails. It seems like a pandas bug to me. (pandas v1.3.3, python v3.9.5)

# single index

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

   old0
0     0
1     1
2     2
3     3

>>> new_df = pd.DataFrame({ 'new0':[10]*4, 'new1':[11]*4, 'new2':[12]*4 })

   new0  new1  new2
0    10    11    12
1    10    11    12
2    10    11    12
3    10    11    12

>>> df.loc[:, ['new0', 'new1', 'new2'] ] = new_df

   old0  new0  new1  new2
0     0    10    11    12
1     1    10    11    12
2     2    10    11    12
3     3    10    11    12

# multi-index

>>> mdf = pd.DataFrame({ ('old',0):[0,1,2,3] })

  old
    0
0   0
1   1
2   2
3   3

>>> new_mdf = pd.DataFrame({ ('new',0):[10]*4, ('new',1):[11]*4, ('new',2):[12]*4 })

  new        
    0   1   2
0  10  11  12
1  10  11  12
2  10  11  12
3  10  11  12

>>> mdf.loc[:, [('new',0), ('new',1), ('new',2)] ] = new_mdf

KeyError: "None of [MultiIndex([('new', 0),\n            ('new', 1),\n            ('new', 2)],\n           )] are in the [columns]"
user_10810430
Related