What is the fastest way to populate one pandas dataframe based on values from another pandas dataframe?

user_10358900 0
Viewed 72

I have a pandas dataframe position

        row    column
  1      3     Brazil
  2      6     USA
  3      3     USA
  4      7     Canada

and another x

        Brazil   Canada  USA
  1     False    False   False
  2     False    False   False
  3     False    False   False
  4     False    False   False
  5     False    False   False
  6     False    False   False
  7     False    False   False

I want to populate the second one based on the values from the first one, so the result is:

        Brazil   Canada  USA
  1     False    False   False
  2     False    False   False
  3     True     False   True
  4     False    False   False
  5     False    False   False
  6     False    False   True
  7     False    True    False

I'm doing that using iterrows()

  for i, r in positions.iterrows():
      x.at[r['row'],r['column']] = True

Is there a faster way to do that?

pandas dataframe numpy python
3 Answers

I will do crosstab with update 

x.update(pd.crosstab(df.row,df.column).eq(1))
x
Out[44]: 
  Brazil Canada    USA
1  False  False  False
2  False  False  False
3   True  False   True
4  False  False  False
5  False  False  False
6  False  False   True
7  False   True  False
user_79645270

You can pivot the positions table:

s = (df.assign(dummy=True).set_index(['row','column'])
       ['dummy'].unstack(fill_value=False)
    )
x |= s

Output:

   Brazil  Canada    USA
1   False   False  False
2   False   False  False
3    True   False   True
4   False   False  False
5   False   False  False
6   False   False   True
7   False    True  False
user_42384080

searchsorted and slice assignment with iloc

This assumes that index and columns in x are sorted.

We'll use iloc and tuples of positions to assign the value of True

i = tuple(x.index.searchsorted(df.row))
j = tuple(x.columns.searchsorted(df.column))

x.iloc[[i, j]] = True
x


   Brazil  Canada    USA
1   False   False  False
2   False   False  False
3    True   False   True
4   False   False  False
5   False   False  False
6   False   False   True
7   False    True  False
user_23366540
Related