This seems like a ridiculously easy question... but I'm not seeing the easy answer I was expecting.
So, how do I get the value at an nth row of a given column in Pandas? (I am particularly interested in the first row, but would be interested in a more general practice as well).
For example, let's say I want to pull the 1.2 value in Btime as a variable.
Whats the right way to do this?
>>> df_testATime X Y Z Btime C D E0 1.2 2 15 2 1.2 12 25 121 1.4 3 12 1 1.3 13 22 112 1.5 1 10 6 1.4 11 20 163 1.6 2 9 10 1.7 12 29 124 1.9 1 1 9 1.9 11 21 195 2.0 0 0 0 2.0 8 10 116 2.4 0 0 0 2.4 10 12 15Best Answer
To select the ith row, use iloc:
In [31]: df_test.iloc[0]Out[31]: ATime 1.2X 2.0Y 15.0Z 2.0Btime 1.2C 12.0D 25.0E 12.0Name: 0, dtype: float64To select the ith value in the Btime column you could use:
In [30]: df_test['Btime'].iloc[0]Out[30]: 1.2There is a difference between df_test['Btime'].iloc[0] (recommended) and df_test.iloc[0]['Btime']:
DataFrames store data in column-based blocks (where each block has a singledtype). If you select by column first, a view can be returned (which isquicker than returning a copy) and the original dtype is preserved. In contrast,if you select by row first, and if the DataFrame has columns of differentdtypes, then Pandas copies the data into a new Series of object dtype. Soselecting columns is a bit faster than selecting rows. Thus, althoughdf_test.iloc[0]['Btime'] works, df_test['Btime'].iloc[0] is a little bitmore efficient.
There is a big difference between the two when it comes to assignment.df_test['Btime'].iloc[0] = x affects df_test, but df_test.iloc[0]['Btime']may not. See below for an explanation of why. Because a subtle difference inthe order of indexing makes a big difference in behavior, it is better to use single indexing assignment:
df.iloc[0, df.columns.get_loc('Btime')] = xdf.iloc[0, df.columns.get_loc('Btime')] = x (recommended):
The recommended way to assign new values to aDataFrame is to avoid chained indexing, and instead use the method shown byandrew,
df.loc[df.index[n], 'Btime'] = xor
df.iloc[n, df.columns.get_loc('Btime')] = xThe latter method is a bit faster, because df.loc has to convert the row and column labels topositional indices, so there is a little less conversion necessary if you usedf.iloc instead.
df['Btime'].iloc[0] = x works, but is not recommended:
Although this works, it is taking advantage of the way DataFrames are currently implemented. There is no guarantee that Pandas has to work this way in the future. In particular, it is taking advantage of the fact that (currently) df['Btime'] always returns aview (not a copy) so df['Btime'].iloc[n] = x can be used to assign a new valueat the nth location of the Btime column of df.
Since Pandas makes no explicit guarantees about when indexers return a view versus a copy, assignments that use chained indexing generally always raise a SettingWithCopyWarning even though in this case the assignment succeeds in modifying df:
In [22]: df = pd.DataFrame({'foo':list('ABC')}, index=[0,2,1])In [24]: df['bar'] = 100In [25]: df['bar'].iloc[0] = 99/home/unutbu/data/binky/bin/ipython:1: SettingWithCopyWarning: A value is trying to be set on a copy of a slice from a DataFrameSee the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copyself._setitem_with_indexer(indexer, value)In [26]: dfOut[26]: foo bar0 A 99 <-- assignment succeeded2 B 1001 C 100df.iloc[0]['Btime'] = x does not work:
In contrast, assignment with df.iloc[0]['bar'] = 123 does not work because df.iloc[0] is returning a copy:
In [66]: df.iloc[0]['bar'] = 123/home/unutbu/data/binky/bin/ipython:1: SettingWithCopyWarning: A value is trying to be set on a copy of a slice from a DataFrameSee the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copyIn [67]: dfOut[67]: foo bar0 A 99 <-- assignment failed2 B 1001 C 100Warning: I had previously suggested df_test.ix[i, 'Btime']. But this is not guaranteed to give you the ith value since ix tries to index by label before trying to index by position. So if the DataFrame has an integer index which is not in sorted order starting at 0, then using ix[i] will return the row labeled i rather than the ith row. For example,
In [1]: df = pd.DataFrame({'foo':list('ABC')}, index=[0,2,1])In [2]: dfOut[2]: foo0 A2 B1 CIn [4]: df.ix[1, 'foo']Out[4]: 'C'When working with dataframes in Python, you may often need to extract specific rows or subsets of data. To retrieve the first row of a dataframe, you can use the iloc function.
The iloc function allows you to select rows and columns by their integer positions. To extract the first row, you can use iloc[0]. This will return a series object containing the values of the first row.
Alternatively, you can use the head function to retrieve the first n rows of a dataframe. By default, head(1) will return the first row. This method is more convenient if you only need to retrieve a small number of rows.
Another way to get the first row of a dataframe is by using the loc function. The loc function allows you to subset dataframes based on labels instead of integer positions. To extract the first row, you can use loc[0]. This will return a series object containing the values of the first row.
Overall, there are multiple ways to retrieve the first row of a dataframe in Python. Whether you prefer using iloc, head, or loc, these methods allow you to easily extract the desired data from your dataframe.
Another way to do this:
first_value = df['Btime'].values[0]This way seems to be faster than using .iloc:
In [1]: %timeit -n 1000 df['Btime'].values[20]5.82 µs ± 142 ns per loop (mean ± std. dev. of 7 runs, 1000 loops each)In [2]: %timeit -n 1000 df['Btime'].iloc[20]29.2 µs ± 1.28 µs per loop (mean ± std. dev. of 7 runs, 1000 loops each)Note that the answer from @unutbu will be correct until you want to set the value to something new, then it will not work if your dataframe is a view.
In [4]: df = pd.DataFrame({'foo':list('ABC')}, index=[0,2,1])In [5]: df['bar'] = 100In [6]: df['bar'].iloc[0] = 99/opt/local/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/pandas-0.16.0_19_g8d2818e-py2.7-macosx-10.9-x86_64.egg/pandas/core/indexing.py:118: SettingWithCopyWarning:A value is trying to be set on a copy of a slice from a DataFrameSee the the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copyself._setitem_with_indexer(indexer, value)Another approach that will consistently work with both setting and getting is:
In [7]: df.loc[df.index[0], 'foo']Out[7]: 'A'In [8]: df.loc[df.index[0], 'bar'] = 99In [9]: dfOut[9]:foo bar0 A 992 B 1001 C 100df.iloc[0].head(1)- First data set only from entire first row.df.iloc[0]- Entire First row in column.
In a general way, if you want to pick up the first N rows from the J column from pandas dataframe the best way to do this is:
data = dataframe[0:N][:,J]To access a single value you can use the method iat that is much faster than iloc:
df['Btime'].iat[0]You can also use the method take:
df['Btime'].take(0).iat and .at are the methods for getting and setting single values and are much faster than .iloc and .loc. Mykola Zotko pointed this out in their answer, but they did not use .iat to its full extent.
When we can use .iat or .at, we should only have to index into the dataframe once.
This is not great:
df['Btime'].iat[0]It is not ideal because the 'Btime' column was first selected as a series, then .iat was used to index into that series.
These two options are the best:
- Using zero-indexed positions:
df.iat[0, 4] # get the value in the zeroth row, and 4th column - Using Labels:
df.at[0, 'Btime'] # get the value where the index label is 0 and the column name is "Btime".
Both methods return the value of 1.2.
To get e.g the value from column 'test' and row 1 it works like
df[['test']].values[0][0]as only df[['test']].values[0] gives back a array
Another way of getting the first row and preserving the index:
x = df.first('d') # Returns the first day. '3d' gives first three days.According to pandas docs, at is the fastest way to access a scalar value such as the use case in the OP (already suggested by Alex on this page).
Building upon Alex's answer, because dataframes don't necessarily have a range index it might be more complete to index df.index (since dataframe indexes are built on numpy arrays, you can index them like an array) or call get_loc() on columns to get the integer location of a column.
df.at[df.index[0], 'Btime']df.iat[0, df.columns.get_loc('Btime')]One common problem is that if you used a boolean mask to get a single value, but ended up with a value with an index (actually a Series); e.g.:
0 1.2Name: Btime, dtype: float64you can use squeeze() to get the scalar value, i.e.
df.loc[df['Btime']<1.3, 'Btime'].squeeze()