Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

Set value for all columns based on series

I am trying to set all values of a row to the same value base on another dataframe (or series derived from a dataframe).

Simple dfs:

df=pd.DataFrame([[1,2,3],[4,5,6],[7,8,9]],columns=['a','b','c'])
df2=pd.DataFrame([['const',10,'other'],['const',20,'other'],['var',30,'other'],['var',40,'other']],columns=['type','val','z'])

df
   a  b  c
0  1  2  3
1  4  5  6
2  7  8  9

df2
    type   val      z
0  const    10  other
1  const    20  other
2    var    30  other
2    var    40  other

I want to set all columns of df to ‘val’ found in df2 if the ‘type’ column in df2 for the same index is ‘const’

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

The elements I want to change are given by:

df.loc[df2['type']=='const',:]
   a  b  c
0  1  2  3
1  4  5  6

The values I want to change them to are:

df2.loc[df2['type']=='const','val']

0    10
1    20

And the end result would be:

    a   b   c
0  10  10  10
1  20  20  20
2   7   8   9

I was hoping broadcasting the series to the dataframe would do the trick, but it doesn’t:

df.loc[df2['type']=='const',:] = df2.loc[df2['type']=='const','val']


Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "C:\ProgramData\Anaconda3\envs\py37\lib\site-packages\pandas\core\indexing.py", line 723, in __setitem__
    iloc._setitem_with_indexer(indexer, value, self.name)
  File "C:\ProgramData\Anaconda3\envs\py37\lib\site-packages\pandas\core\indexing.py", line 1732, in _setitem_with_indexer
    self._setitem_single_block(indexer, value, name)
  File "C:\ProgramData\Anaconda3\envs\py37\lib\site-packages\pandas\core\indexing.py", line 1968, in _setitem_single_block
    self.obj._mgr = self.obj._mgr.setitem(indexer=indexer, value=value)
  File "C:\ProgramData\Anaconda3\envs\py37\lib\site-packages\pandas\core\internals\managers.py", line 355, in setitem
    return self.apply("setitem", indexer=indexer, value=value)
  File "C:\ProgramData\Anaconda3\envs\py37\lib\site-packages\pandas\core\internals\managers.py", line 327, in apply
    applied = getattr(b, f)(**kwargs)
  File "C:\ProgramData\Anaconda3\envs\py37\lib\site-packages\pandas\core\internals\blocks.py", line 984, in setitem
    values[indexer] = value
ValueError: shape mismatch: value array of shape (2,) could not be broadcast to indexing result of shape (2,3)

Appreciate thoughts on what I am missing. The closest thing I could find is how to set one column at a time:
Pandas – Replace values based on index

>Solution :

You’re close, but matching indices of a DataFrame and a Series doesn’t appear to be the default function. So, with a tiny change, we can ask for a DataFrame back from .loc instead of a Series.

m = df2['type'].eq('const')
df.loc[m] = df2.loc[m, ['val']]
print(df)

Output:

    a   b   c
0  10  10  10
1  20  20  20
2   7   8   9
Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading