Looking for the lowest value in pandas row-CodePudding

I try to look the highest and lowest column

My Input

 id    Place A   Place B   Place C   
  1         67        87        76

My Output

 id    Place A   Place B   Place C   Highest   Lowest
  1         67        87        76   Place B   Place A

For Highest 'df['Highest'] = df.idxmax(axis=1)` works well, but For Lowest For Highest 'df['Lowest'] = df.idxmin(axis=1)` give error like this

~/.local/lib/python3.6/site-packages/pandas/core/frame.py in idxmin(self, axis, skipna)
   8797         """
   8798         axis = self._get_axis_number(axis)
-> 8799         indices = nanops.nanargmin(self.values, axis=axis, skipna=skipna)
   8800 
   8801         # indices will always be np.ndarray since axis is not None and

~/.local/lib/python3.6/site-packages/pandas/core/nanops.py in _f(*args, **kwargs)
     65                 f_name = f.__name__.replace("nan", "")
     66                 raise TypeError(
---> 67                     f"reduction operation '{f_name}' not allowed for this dtype"
     68                 )
     69             try:

TypeError: reduction operation 'argmin' not allowed for this dtype

CodePudding user response：

The error happens because you are reassigning the result of the idxmax to the same dataframe, so at the time you compute idxmin the dataframe contains an extra column Highest which is a string and cannot be compared to the other numbers. The solutions is just to assign to a different dataframe

df2 = df.copy()
df2['Highest'] = df.idxmax(axis=1)
df2['Lowest'] = df.idxmin(axis=1)

CodePudding user response：

If your dataframe is large and you don't want to spend O(N) in space for creating a copy from the dataframe. You can do with O(1) in space and do in one line and creating two columns and insert value like below.

df[['Highest','Lowest']] = pd.concat([df.idxmax(axis=1),  df.idxmin(axis=1)], axis=1)
print(df)

id  Place A  Place B  Place C     Highest   Lowest
1   67       87       76          Place B   Place A

CodePudding user response：

Like in the answer already said, since you run them back to back, the error in 'Lowest' is because of the just created new column 'Highest' which contains a string.

You could do it with df.assign in one go:

df.assign(Highest=df.idxmax(axis=1), Lowest=df.idxmin(axis=1))

Output:

   Place A  Place B  Place C  Highest   Lowest
1       67       87       76  Place B  Place A