Set value for particular cell in pandas DataFrame using index

Question

I ve created a Pandas DataFrame  df   DataFrame index   A   B   C    columns   x   y      and got this       x    y A  NaN  NaN B  NaN  NaN C  NaN  NaN    Then I want to assign value to particular cell  for example for row  C  and column  x   I ve expected to get such result        x    y A  NaN  NaN B  NaN  NaN C  10  NaN   with this code   df xs  C    x     10   but contents of df haven t changed  It s again only NaNs in DataFrame    Any suggestions

User · Answer

I too was searching for this topic and I put together a way to iterate through a DataFrame and update it with lookup values from a second DataFrame   Here is my code   src df   pd read sql query src sql src connection  for index1  row1 in src df iterrows        for index  row in vertical df iterrows            src df set value index index1 col u etl load key  value etl load key          if  row1 u src id      row  SRC ID    is True              src df set value index index1 col u vertical  value row  VERTICAL

User · Answer

In my example i just change it in selected cell      for index  row in result iterrows            if np isnan row  weight                 result at index   weight     0 0    result  is a dataField with column  weight

User · Answer

you can use  iloc   df iloc  2    0     10

User · Answer

RukTech s answer  df set value  C    x   10   is far and away faster than the options I ve suggested below  However  it has been slated for deprecation   Going forward  the recommended method is  iat  at     Why df xs  C    x   10 does not work   df xs  C   by default  returns a new dataframe with a copy of the data  so   df xs  C    x   10   modifies this new dataframe only   df  x   returns a view of the df dataframe  so   df  x    C     10   modifies df itself   Warning  It is sometimes difficult to predict if an operation returns a copy or a view  For this reason the docs recommend avoiding assignments with  chained indexing        So the recommended alternative is  df at  C    x     10   which does modify df     In  18    timeit df set value  C    x   10  100000 loops  best of 3  2 9   s per loop  In  20    timeit df  x    C     10 100000 loops  best of 3  6 31   s per loop  In  81    timeit df at  C    x     10 100000 loops  best of 3  9 2   s per loop

User · Answer

The recommended way  according to the maintainers  to set a value is   df ix  x   C   10   Using  chained indexing   df  x    C    may lead to problems   See    https   stackoverflow com a 21287235 1579844 http   pandas pydata org pandas-docs dev indexing html indexing-view-versus-copy https   github com pydata pandas pull 6031

User · Answer

From version 0 21 1 you can also use  at method  There are some differences compared to  loc as mentioned here - pandas  at versus  loc  but it s faster on single value replacement

User · Answer

Try using df loc row index col indexer    value

User · Answer

I tested and the output is df set value is little faster  but the official method df at looks like the fastest non deprecated way to do it   import numpy as np import pandas as pd  df   pd DataFrame np random rand 100  100     timeit df iat 50 50  50      timeit df at 50 50  50       timeit df set value 50 50 50    will deprecate  timeit df iloc 50 50  50  timeit df loc 50 50  50  7 06   s    118 ns per loop  mean    std  dev  of 7 runs  100000 loops each  5 52   s    64 2 ns per loop  mean    std  dev  of 7 runs  100000 loops each  3 68   s    80 8 ns per loop  mean    std  dev  of 7 runs  100000 loops each  98 7   s    1 07   s per loop  mean    std  dev  of 7 runs  10000 loops each  109   s    1 42   s per loop  mean    std  dev  of 7 runs  10000 loops each    Note this is setting the value for a single cell  For the vectors loc and iloc should be better options since they are vectorized

User · Answer

If you want to change values not for whole row  but only for some columns   x   pd DataFrame   A    1  2  3    B    4  5  6    x iloc 1    dict A 10  B -10

User · Answer

You can also use a conditional lookup using  loc as seen here   df loc df  lt some column name gt       lt condition gt     lt another column name gt       lt value to add gt    where  lt some column name is the column you want to check the  lt condition gt  variable against and  lt another column name gt  is the column you want to add to  can be a new column or one that already exists    lt value to add gt  is the value you want to add to that column row   This example doesn t work precisely with the question at hand  but it might be useful for someone wants to add a specific value based on a condition

User · Answer

To set values  use   df at 0   clm1     0    The fastest recommended method for setting variables  set value  ix have been deprecated  No warning  unlike iloc and loc

User · Answer

This is the only thing that worked for me   df loc  C    x     10   Learn more about  loc here

User · Answer

One way to use index with condition is first get the index of all the rows that satisfy your condition and then simply use those row indexes in a multiple of ways  conditional index   df loc  df  col name    lt condition gt    index   Example condition is like    5   gt 10      Any string    gt   DateTime   Then you can use these row indexes in variety of ways like   Replace value of one column for conditional index   df loc conditional index    col name     lt new value gt     Replace value of multiple column for conditional index   df loc conditional index   col1 col2     lt new value gt     One benefit with saving the conditional index is that you can assign value of one column to another column with same row index   df loc conditional index   col1 col2    df loc conditional index  col name     This is all possible because  index returns a array of index which  loc can use with direct addressing so it avoids traversals again and again

User · Answer

Soo  your question to convert NaN at   x  C  to value 10 the answer is   df  x   loc  C    10 df  alternative code is df loc  C    x   10 df

User · Answer

iat  at is the good solution  Supposing you have this simple data frame       A   B   C 0  1   8   4  1  3   9   6 2  22 33  52   if we want to modify the value of the cell  0  A   u can use one of those solution      df iat 0 0    2 df at 0  A     2   And here is a complete example how to use iat to get and set a value of cell     def prepossessing df     for index in range 0 len df           df iat index 0    df iat index 0    2   return df   y train before         0 0   54 1   15 2   15 3   8 4   31 5   63 6   11   y train after calling prepossessing function that iat to change to multiply the value of each cell by 2         0 0   108 1   30 2   30 3   16 4   62 5   126 6   22

User · Answer

In addition to the answers above  here is a benchmark comparing different ways to add rows of data to an already existing dataframe  It shows that using at or set-value is the most efficient way for large dataframes  at least for these test conditions     Create new dataframe for each row and          append it  13 0 s      concatenate it  13 1 s   Store all new rows in another container first  convert to new dataframe once and append      container   lists of lists  2 0 s  container   dictionary of lists  1 9 s   Preallocate whole dataframe  iterate over new rows and all columns and fill using       at  0 6 s      set value  0 4 s     For the test  an existing dataframe comprising 100 000 rows and 1 000 columns and random numpy values was used  To this dataframe  100 new rows were added    Code see below      usr bin env python3   - - coding  utf-8 - -     Created on Wed Nov 21 16 38 46 2018   author  gebbissimo      import pandas as pd import numpy as np import time  NUM ROWS   100000 NUM COLS   1000 data   np random rand NUM ROWS NUM COLS  df   pd DataFrame data   NUM ROWS NEW   100 data tot   np random rand NUM ROWS   NUM ROWS NEW NUM COLS  df tot   pd DataFrame data tot   DATA NEW   np random rand 1 NUM COLS        FUNCTIONS    create and append def create and append df       for i in range NUM ROWS NEW           df new   pd DataFrame DATA NEW          df   df append df new      return df    create and concatenate def create and concat df       for i in range NUM ROWS NEW           df new   pd DataFrame DATA NEW          df   pd concat  df  df new       return df     store as dict and  def store as list df       lst       for i in range NUM ROWS NEW       for i in range NUM ROWS NEW           for j in range NUM COLS               lst i  append DATA NEW 0 j       df new   pd DataFrame lst      df tot   df append df new      return df tot    store as dict and  def store as dict df       dct          for j in range NUM COLS           dct j               for i in range NUM ROWS NEW               dct j  append DATA NEW 0 j       df new   pd DataFrame dct      df tot   df append df new      return df tot       preallocate and fill using  at def fill using at df       for i in range NUM ROWS NEW           for j in range NUM COLS                print  i j        format i j               df at NUM ROWS i j    DATA NEW 0 j      return df     preallocate and fill using  at def fill using set df       for i in range NUM ROWS NEW           for j in range NUM COLS                print  i j        format i j               df set value NUM ROWS i j DATA NEW 0 j       return df       TESTS t0   time time       create and append df  t1   time time   print  Needed    seconds  format t1-t0    t0   time time       create and concat df  t1   time time   print  Needed    seconds  format t1-t0    t0   time time       store as list df  t1   time time   print  Needed    seconds  format t1-t0    t0   time time       store as dict df  t1   time time   print  Needed    seconds  format t1-t0    t0   time time       fill using at df tot  t1   time time   print  Needed    seconds  format t1-t0    t0   time time       fill using set df tot  t1   time time   print  Needed    seconds  format t1-t0

User · Answer

If one wants to change the cell in the position  0 0  of the df to a string such as   quot 236 quot 76 quot    the following options will do the work  df 0  0      quot 236 quot 76 quot      timeit df 0  0      quot 236 quot 76 quot     938   s    83 4   s per loop  mean    std  dev  of 7 runs  1000 loops each   Or using pandas DataFrame at df at 0  0      quot 236 quot 76 quot       timeit df at 0  0      quot 236 quot 76 quot     15   s    2 09   s per loop  mean    std  dev  of 7 runs  100000 loops each   Or using pandas DataFrame iat df iat 0  0      quot 236 quot 76 quot       timeit df iat 0  0      quot 236 quot 76 quot     41 1   s    3 09   s per loop  mean    std  dev  of 7 runs  10000 loops each   Or using pandas DataFrame loc df loc 0  0      quot 236 quot 76 quot       timeit df loc 0  0      quot 236 quot 76 quot     5 21 ms    401   s per loop  mean    std  dev  of 7 runs  100 loops each   Or using pandas DataFrame iloc df iloc 0  0      quot 236 quot 76 quot       timeit df iloc 0  0      quot 236 quot 76 quot     5 12 ms    300   s per loop  mean    std  dev  of 7 runs  100 loops each   If time is of relevance  using pandas DataFrame at is the fastest approach

User · Answer

set value   is deprecated   Starting from the release 0 23 4  Pandas  announces the future       gt  gt  gt  df                    Cars  Prices  U   0               Audi TT        120 0 1 Lamborghini Aventador        245 0 2      Chevrolet Malibu        190 0  gt  gt  gt  df set value 2   Prices  U     240 0    main   1  FutureWarning  set value is deprecated and will be removed in a future release  Please use  at   or  iat   accessors instead                     Cars  Prices  U   0               Audi TT        120 0 1 Lamborghini Aventador        245 0 2      Chevrolet Malibu        240 0   Considering this advice  here s a demonstration of how to use them    by row column integer positions      gt  gt  gt  df iat 1  1    260 0  gt  gt  gt  df                    Cars  Prices  U   0               Audi TT        120 0 1 Lamborghini Aventador        260 0 2      Chevrolet Malibu        240 0    by row column labels      gt  gt  gt  df at 2   Cars      Chevrolet Corvette   gt  gt  gt  df                   Cars  Prices  U   0               Audi TT        120 0 1 Lamborghini Aventador        260 0 2    Chevrolet Corvette        240 0   References    pandas DataFrame iat pandas DataFrame at

User · Answer

Update  The  set value method is going to be deprecated   iat  at are good replacements  unfortunately pandas provides little documentation    The fastest way to do this is using set value  This method is  100 times faster than  ix method  For example    df set value  C    x   10

User · Answer

I would suggest  df loc index position   quot column name quot     some value

User · Answer

df loc  c   x   10 This will change the value of cth row and  xth column

User · Answer

Here is a summary of the valid solutions provided by all users  for data frames indexed by integer and string   df iloc  df loc and df at work for both type of data frames  df iloc only works with row column integer indices  df loc and df at supports for setting values using column names and   or integer indices   When the specified index does not exist  both df loc and df at would append the newly inserted rows columns to the existing data frame  but df iloc would raise  IndexError  positional indexers are out-of-bounds   A working example tested in Python 2 7 and 3 7 is as follows   import numpy as np  pandas as pd  df1   pd DataFrame index np arange 3   columns   x   y   z    df1  x       A   B   C   df1 at 2  y     400    rows columns specified does not exist  appends new rows columns to existing data frame df1 at  D   w     9000 df1 loc  E   q     499    using df  lt some column name gt       lt condition gt  to retrieve target rows df1 at df1  x     B    y     10000 df1 loc df1  x     B     z   w      10000    using a list of index to setup values df1 iloc  1 2 4   2    9999 df1 loc  0  D   E    w     7500 df1 at  0 2  D    x     10 df1 at      y    w      8000  df1  gt  gt  gt  df1      x     y     z     w      q 0   10  8000   NaN  8000    NaN 1    B  8000  9999  8000    NaN 2   10  8000  9999  8000    NaN D   10  8000   NaN  8000    NaN E  NaN  8000  9999  8000  499 0

[python] Set value for particular cell in pandas DataFrame using index

Examples related to python

Examples related to pandas

Examples related to dataframe

Examples related to cell

Examples related to nan