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?

cs95 · Accepted Answer · 2018-12-10 11:29:43Z

7

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

answered Dec 10, 2018 at 11:29

cs95

406k106 gold badges744 silver badges797 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

cs95 Over a year ago

@NathanTew Well, if you think about it; it makes sense. Pandas assumes you know what you are doing when you use these indexer functions, although I will admit there is a non-zero gradient to this learning curve.

Nathan Tew Over a year ago

but isn't untrue in itself to say that at only can have integer indexers, because it doesn't?

cs95 Over a year ago

@NathanTew But df[df.foo == 222].loc[:,'bar'] does only have integer indices, so it will naturally complain when you try to index with a non-integer (.at['bar']).

cs95 Over a year ago

@NathanTew (Last ping, promise) Thank you for accepting; you can also upvote the answer if it was helpful. Click the triangle above the "0" to the left of my answer. Thank you!

meW · Accepted Answer · 2018-12-10 11:23:48Z

0

You can easily get the value using values

df2.loc[1,'Name'] = df[df.foo == 222]['foo'].values
df2

#   Name
# 1 222

answered Dec 10, 2018 at 11:23

meW

3,97710 silver badges27 bronze badges

2 Comments

Nathan Tew Over a year ago

thanks, that helped, but my main question was why .at isn't working

meW Over a year ago

pass the complete index to at like at.[row_idx, col_idx]

Vasu Bandaru · Accepted Answer · 2018-12-10 12:03:07Z

0

The point at which you're using at, the data is a Pandas Series with integer Index, That is why your getting the mentioned error.

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

If you check the index of the data, you'll see an index with value 2

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

#Int64Index([2], dtype='int64')

One of the correct method would be, as mentioned by coldspeed

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

#555

answered Dec 10, 2018 at 12:03

Vasu Bandaru

112 bronze badges

Comments

SherylHohman · Accepted Answer · 2020-04-23 19:12:36Z

0

.at will work with label indexing, not position indexing.
Example:

df.at(3,'ColName')

Returns value of ColName for 3rd row.

edited Apr 23, 2020 at 19:12

SherylHohman

18.2k18 gold badges94 silver badges101 bronze badges

answered Apr 23, 2020 at 16:33

Shifu

412 bronze badges

Collectives™ on Stack Overflow

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

4 Answers 4

4 Comments

2 Comments

Comments

Returns value of `ColName` for 3rd row.

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

2 Comments

Comments

Returns value of ColName for 3rd row.

Comments

Your Answer

Sign up or log in

Post as a guest

Related

Returns value of `ColName` for 3rd row.