How to update a pandas dataframe with an array of values, indexes, and columns?

Question

I have a large dataframe and would like to update specific values at known row and column indices. I would like to do this without an explicit for loop.

For example:

import string                                                                                                                                  
import numpy as np                                                                                                                             
import pandas as pd                                                                                                                            
df = pd.DataFrame(np.random.rand(10, 10), index = range(10), columns = list(string.ascii_lowercase)[:10])

I have arbitrary arrays of indexes, columns, and values that I would like to use to update df. For example:

update_values = [0,-2,-3]                                                                                                                       
update_index = [3,5,7]                                                                                                                          
update_columns = ["d","g","i"]

I can loop over the arrays to update the original dataframe:

for i,j,v in zip(update_index, update_columns, update_values): 
    df.loc[i,j] = v

but would like to use a technique not involving an explicit for loop.

You have a list of values, and you want to use each item in the list to update your dataframe, you have to use a loop! — Devesh Kumar Singh
– Devesh Kumar Singh, Commented Apr 21, 2019 at 15:32
Ohh, Even I am learning pandas, could you point me to good resources which will help my understanding of pandas — Devesh Kumar Singh
– Devesh Kumar Singh, Commented Apr 21, 2019 at 15:38
@DeveshKumarSingh I believe constant training, official docs and stackoverflow is the best combo ;} — rafaelc
– rafaelc, Commented Apr 21, 2019 at 15:42

rafaelc · Accepted Answer · 2019-04-21 15:41:07Z

4

Use the underlying numpy values

indexes = map(df.columns.get_loc, update_columns)
df.values[update_index, list(indexes)] = update_values

edited Apr 21, 2019 at 15:41

answered Apr 21, 2019 at 15:35

rafaelc

59.4k15 gold badges64 silver badges87 bronze badges

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

2 Comments

Brian Rigney Over a year ago

this answers my question. thank you. I do still wonder if there is anything in the pandas API to accomplish this without resorting to numpy and .values?

rafaelc Over a year ago

pandas' indexing is different from numpy's indexing. Take a look at the docs on indexing of both modules and you'll see the differences.. The lists you have are just perfect for numpy indexing and there is probably no faster way to do this other than just using the underlying numpy array

Ziyad Moraished · Accepted Answer · 2019-04-21 15:50:34Z

3

try using loc which is used to specify the needed indexes and columns names loc[[index_names], [columns_names]]

df.loc[[3,5,7], ["d","g","i"]] = [0,-2,-3]

answered Apr 21, 2019 at 15:50

Ziyad Moraished

3612 silver badges11 bronze badges

1 Comment

user3483203 Over a year ago

This doesn't do what you think it does. Those indices will return a 3x3 dataframe, and you are updating each row to those values, rather than the individual entries.

Collectives™ on Stack Overflow

How to update a pandas dataframe with an array of values, indexes, and columns?

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related