Pandas .at throwing ValueError: At based indexing on an integer index can only have integer indexers

Question

So I have a df where I am extracting one value to store it in another df:

import pandas as pd

# Create data set
d = {'foo':[100, 111, 222], 
     'bar':[333, 444, 555]}
df = pd.DataFrame(d)

# Full dataframe:
print(df)

# Shows:
#    bar   foo 
# 0  333   100
# 1  444   111
# 2  555   222

df2=pd.DataFrame()
df2.loc[1,'Name'] = df[df.foo == 222]['foo']

#error: 
ValueError: Incompatible indexer with Series

I'm assuming the last line throws that error because df[df.foo == 222]['foo'] is a Series:

2    222
Name: foo, dtype: int64

So I'm trying to get the value itself. I used .at and got this:

print(df[df.foo == 222].loc[:,'bar'].at['bar'])

#ValueError: At based indexing on an integer index can only have integer indexers

From what I've read it's iat that uses integer indexers and at uses both label and integer, so what's going on here?

Answer 1

Using at with a boolean mask is considered bad form unless you can 100% guarantee only one row in the mask is true (otherwise, at fails).

The best thing to do is to use loc and take the first result.

df.loc[df.foo == 222, 'bar'].values[0]
555

For reference, at does not work because returns a single-row Series with a index [2]:

df[df.foo == 222].loc[:,'bar']

2    555
Name: bar, dtype: int64

At this point, at['bar'] makes no sense because it searches for "bar" in the index and bar isn't. What you should've done is

df[df.foo == 222].at[2, 'bar']
555