How to get a value from a cell of a dataframe?

Question

I have constructed a condition that extract exactly one row from my data frame:

d2 = df[(df['l_ext']==l_ext) & (df['item']==item) & (df['wn']==wn) & (df['wd']==1)]

Now I would like to take a value from a particular column:

val = d2['col_name']

But as a result I get a data frame that contains one row and one column (i.e. one cell). It is not what I need. I need one value (one float number). How can I do it in pandas?

If you tried some of these answers but ended up with a `SettingWithCopyWarning`, you can take a look at [this post](https://stackoverflow.com/questions/20625582/how-to-deal-with-settingwithcopywarning-in-pandas/53954986#53954986) for an explanation of the warning and possible workarounds/solutions. — cs95, Jan 22 '19 at 23:52

score 709 · Accepted Answer · answered May 24 '13 at 07:31

709

If you have a DataFrame with only one row, then access the first (only) row as a Series using iloc, and then the value using the column name:

In [3]: sub_df
Out[3]:
          A         B
2 -0.133653 -0.030854

In [4]: sub_df.iloc[0]
Out[4]:
A   -0.133653
B   -0.030854
Name: 2, dtype: float64

In [5]: sub_df.iloc[0]['A']
Out[5]: -0.13365288513107493

answered May 24 '13 at 07:31

Andy Hayden

328,850
93
598
514

21

@Sophologist I agree that its ridiculous that this is required. It also does not work when you try to pass the conditionals inline; `my_df.loc[my_df['Col1'] == foo]['Col2']` still returns an object of type `` – user5359531 Nov 18 '16 at 03:54
65

Note that this solution returns a Series, not a value! – Atte Juvonen Feb 06 '17 at 12:12
2

@AtteJuvonen That depends if you have duplicates in your index/columns (note at/iat raises an exception with duplicate columns, will file an issue). – Andy Hayden Feb 06 '17 at 19:30
2

weird. I keep reading loc is for names and iloc is for integers but here i'ts iloc for both integer and name – mLstudent33 Mar 13 '20 at 21:26
4

@mLstudent33 It is iloc for the call to the row, and then the column name is given – Liz Apr 22 '21 at 13:43
Worked. Thanks a lot. – Geshan Ravindu Dec 10 '21 at 16:46

score 339 · Answer 2 · edited Jan 16 '18 at 10:43

339

These are fast access for scalars

In [15]: df = pandas.DataFrame(numpy.random.randn(5,3),columns=list('ABC'))

In [16]: df
Out[16]: 
          A         B         C
0 -0.074172 -0.090626  0.038272
1 -0.128545  0.762088 -0.714816
2  0.201498 -0.734963  0.558397
3  1.563307 -1.186415  0.848246
4  0.205171  0.962514  0.037709

In [17]: df.iat[0,0]
Out[17]: -0.074171888537611502

In [18]: df.at[0,'A']
Out[18]: -0.074171888537611502

edited Jan 16 '18 at 10:43

hartmut

876
12
25

answered May 24 '13 at 12:58

Jeff

117,982
20
211
179

19

I like this answer a lot. But whereas you can do `.iloc[-1]['A']` you cannot do `at[-1,'A']` to get the last row entry – hartmut Jan 16 '18 at 09:25
7

this should be the answer because we don't copy in memory an useless line to get only one element inside. – bormat Jun 21 '18 at 17:54
6

@hartmut You can always just do `at[df.index[-1],'A']` – cs95 Jan 22 '19 at 23:51
I like this answer the best. You can also refer to named indexes, which makes your code more readable: `df.at['my_row_name', 'my_column_name']` – LunkRat Apr 30 '22 at 20:14

Guillaume · Answer 3 · 2018-06-29T05:47:45.700

245

You can turn your 1x1 dataframe into a numpy array, then access the first and only value of that array:

val = d2['col_name'].values[0]

edited Jun 29 '18 at 05:47

answered Jun 25 '18 at 20:03

Guillaume

2,873
1
8
13

2

I prefer this method and use it frequently. Used to use `.get_values()[0]` as well. – aaronpenne Jul 12 '18 at 20:41
11

I think this is the best answer since it does not return a pandas.series, and it's the simplest. – Sean McCarthy Jun 30 '19 at 23:33
3

What advantage does this have over the methods provided by Pandas? – AMC Feb 07 '20 at 02:28
1

In my personal opinion, this is bloating. One should look for the simplest path and never include unnecessary frameworks or libraries, even if they are excellent doing their job. – Oak_3260548 Jul 28 '20 at 20:19
1

As of now, this works in pandas as well, no need to have advantage over methods available in pandas, it is a method available in pandas. – Joey Oct 27 '20 at 01:46
This is the one I'm looking for. This syntax is easy to forget everytime. Thank you. – hussam Feb 06 '21 at 06:38

score 44 · Answer 4 · edited Mar 02 '22 at 09:48

44

Most answers are using iloc which is good for selection by position.

If you need selection-by-label loc would be more convenient.

For getting a value explicitly (equiv to deprecated df.get_value('a','A'))
# this is also equivalent to df1.at['a','A']
In [55]: df1.loc['a', 'A'] 
Out[55]: 0.13200317033032932

edited Mar 02 '22 at 09:48

gemspark

103
3

answered Oct 24 '17 at 02:52

Shihe Zhang

2,373
5
33
53

score 39 · Answer 5 · answered May 18 '20 at 06:07

39

It doesn't need to be complicated:

val = df.loc[df.wd==1, 'col_name'].values[0]

answered May 18 '20 at 06:07

Eduardo Freitas

763
6
6

5

basically repeating what Natacha said Oct 27, 2018 ... and Guillaume on Jun 25, 2018 before that – James Jan 29 '21 at 04:53
1

How is that not complicated? Simple would be df.at[r, col] – Blaze Nov 23 '21 at 10:05

score 31 · Answer 6 · answered Oct 27 '18 at 18:21

31

I needed the value of one cell, selected by column and index names. This solution worked for me:

original_conversion_frequency.loc[1,:].values[0]

answered Oct 27 '18 at 18:21

Natacha

1,001
15
20

1

This create a slice, which can be memory consuming – VMAtm Feb 23 '22 at 13:57

score 22 · Answer 7 · edited Feb 28 '14 at 14:21

22

It looks like changes after pandas 10.1/13.1

I upgraded from 10.1 to 13.1, before iloc is not available.

Now with 13.1, iloc[0]['label'] gets a single value array rather than a scalar.

Like this:

lastprice=stock.iloc[-1]['Close']

Output:

date
2014-02-26 118.2
name:Close, dtype: float64

edited Feb 28 '14 at 14:21

newfurniturey

35,828
9
90
101

answered Feb 28 '14 at 01:40

timeislove

995
1
7
13

1

I think this should only be the case for Series with duplicate entries... in fact, I don't see this, could you give a small example to demonstrate this? – Andy Hayden Oct 07 '14 at 16:09
1

i used pandas 13.x, both iloc[][] or iloc[,] output a scalar. just the iloc not working with negative index, like -1 – timeislove Oct 10 '14 at 06:49
1

If you can give a toy example demonstrating this in the answer it'd be really helpful! – Andy Hayden Oct 10 '14 at 07:04

score 17 · Answer 8 · edited Sep 23 '17 at 17:30

17

The quickest/easiest options I have found are the following. 501 represents the row index.

df.at[501,'column_name']
df.get_value(501,'column_name')

edited Sep 23 '17 at 17:30

SilverNak

3,123
4
26
36

answered Sep 23 '17 at 16:41

jroakes

343
3
10

12

`get_value` is deprecated now(v0.21.0 RC1 (October 13, 2017))[reference is here](https://pandas-docs.github.io/pandas-docs-travis/whatsnew.html#deprecations) `.get_value and .set_value on Series, DataFrame, Panel, SparseSeries, and SparseDataFrame are deprecated in favor of using .iat[] or .at[] accessors (GH15269)` – Shihe Zhang Oct 24 '17 at 02:46

score 7 · Answer 9 · edited Nov 25 '16 at 01:53

7

Not sure if this is a good practice, but I noticed I can also get just the value by casting the series as float.

e.g.

rate

3 0.042679

Name: Unemployment_rate, dtype: float64

float(rate)

0.0426789

edited Nov 25 '16 at 01:53

jrbedard

3,494
5
27
34

answered Nov 25 '16 at 01:48

Michael Wei

71
1
2

1

Does that work with a multi-element series as well? – Praxiteles Apr 19 '19 at 15:21

Su Tingxuan · Answer 10 · 2018-10-10T18:18:29.673

7

df_gdp.columns

Index([u'Country', u'Country Code', u'Indicator Name', u'Indicator Code', u'1960', u'1961', u'1962', u'1963', u'1964', u'1965', u'1966', u'1967', u'1968', u'1969', u'1970', u'1971', u'1972', u'1973', u'1974', u'1975', u'1976', u'1977', u'1978', u'1979', u'1980', u'1981', u'1982', u'1983', u'1984', u'1985', u'1986', u'1987', u'1988', u'1989', u'1990', u'1991', u'1992', u'1993', u'1994', u'1995', u'1996', u'1997', u'1998', u'1999', u'2000', u'2001', u'2002', u'2003', u'2004', u'2005', u'2006', u'2007', u'2008', u'2009', u'2010', u'2011', u'2012', u'2013', u'2014', u'2015', u'2016'], dtype='object')

df_gdp[df_gdp["Country Code"] == "USA"]["1996"].values[0]

8100000000000.0

edited Oct 10 '18 at 18:18

answered Oct 10 '18 at 18:12

Su Tingxuan

119
1
2

7

Is this an answer or a question? – Vega Oct 10 '18 at 19:08
6

Welcome to Stack Overflow! Thank you for the code snippet, which might provide some limited, immediate help. A proper explanation would greatly improve its [long-term value](https://meta.stackexchange.com/q/114762/206345) by describing why this is a good solution to the problem, and would make it more useful to future readers with other similar questions. Please edit your answer to add some explanation, including the assumptions you've made. – sepehr Oct 26 '18 at 21:53
3

Despite the negative votes, this answer actually helped me. – Pedro Lobito Mar 21 '20 at 23:18

score 6 · Answer 11 · edited Dec 23 '15 at 10:58

6

For pandas 0.10, where iloc is unavalable, filter a DF and get the first row data for the column VALUE:

df_filt = df[df['C1'] == C1val & df['C2'] == C2val]
result = df_filt.get_value(df_filt.index[0],'VALUE')

if there is more then 1 row filtered, obtain the first row value. There will be an exception if the filter result in empty data frame.

edited Dec 23 '15 at 10:58

jezrael

729,927
78
1,141
1,090

answered Oct 15 '15 at 08:21

Sergey Sergienko

355
4
8

4

`get_value` is deprecated now(v0.21.0 RC1 (October 13, 2017)) [reference is here](https://pandas-docs.github.io/pandas-docs-travis/whatsnew.html#deprecations) `.get_value and .set_value on Series, DataFrame, Panel, SparseSeries, and SparseDataFrame are deprecated in favor of using .iat[] or .at[] accessors (GH15269)` – Shihe Zhang Oct 24 '17 at 03:06
1

But `iat` or `at` cannot get the value based on the column name. – sivabudh Oct 18 '19 at 09:03

score 4 · Answer 12 · edited Jan 24 '21 at 20:26

4

Converting it to integer worked for me:

int(sub_df.iloc[0])

edited Jan 24 '21 at 20:26

J. M. Arnold

4,515
2
17
33

answered Jan 24 '21 at 20:08

Shaig Hamzaliyev

84
1
3

tyersome · Answer 13 · 2021-12-12T04:13:35.263

I've run across this when using DataFrames with MultiIndexes and found squeeze useful.

From the docs:

Squeeze 1 dimensional axis objects into scalars.

Series or DataFrames with a single element are squeezed to a scalar. DataFrames with a single column or a single row are squeezed to a Series. Otherwise the object is unchanged.

# example for DataFrame with MultiIndex
> import pandas as pd

> df = pd.DataFrame(
                    [
                        [1, 2, 3], 
                        [4, 5, 6], 
                        [7, 8, 9]
                    ], 
                    index=pd.MultiIndex.from_tuples( [('i', 1), ('ii', 2), ('iii', 3)] ),
                    columns=pd.MultiIndex.from_tuples( [('A', 'a'), ('B', 'b'), ('C', 'c')] )
)

> df
       A  B  C
       a  b  c
i   1  1  2  3
ii  2  4  5  6
iii 3  7  8  9

> df.loc['ii', 'B']
   b
2  5

> df.loc['ii', 'B'].squeeze()
5

Note that while df.at[] also works (if you aren't needing to use conditionals) you then still AFAIK need to specify all levels of the MultiIndex.

Example:

> df.at[('ii', 2), ('B', 'b')]
5

I have a DataFrame with a 6-level index and 2-level columns, so only having to specify the outer level is quite helpful.

Daniel · Answer 14 · 2022-02-19T13:41:40.530

3

This is quite old by now but as of today you can fix it by simply doing

val = float(d2['col_name'].iloc[0])

edited Feb 19 '22 at 13:41

answered Feb 19 '22 at 13:35

Daniel

31
2

score 0 · Answer 15 · answered May 17 '22 at 15:47

Using .item() returns a scalar (not a Series), and it only works if there is a single element selected. It's much safer than .values[0] which will return the first element regardless of how many are selected.

>>> df = pd.DataFrame({'a': [1,2,2], 'b': [4,5,6]})
>>> df[df['a'] == 1]['a']  # Returns a Series
0    1
Name: a, dtype: int64
>>> df[df['a'] == 1]['a'].item()
1
>>> df2 = df[df['a'] == 2]
>>> df2['b']
1    5
2    6
Name: b, dtype: int64
>>> df2['b'].values[0]
5
>>> df2['b'].item()
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/usr/lib/python3/dist-packages/pandas/core/base.py", line 331, in item
    raise ValueError("can only convert an array of size 1 to a Python scalar")
ValueError: can only convert an array of size 1 to a Python scalar

score -1 · Answer 16 · answered Oct 07 '20 at 13:23

-1

To get the full row's value as JSON (instead of a Serie):

row = df.iloc[0]

Use the to_json method like bellow:

row.to_json()

answered Oct 07 '20 at 13:23

hzitoun

4,878
33
41

3

How is json involved in this question? – Teepeemm May 22 '21 at 14:58

How to get a value from a cell of a dataframe?

16 Answers16

Linked

Related