Python Pandas Get Row Position Name instead of Index Value

Question

Hello I have the below dataframe and I am trying to calculate the absolute change for each City (row) and print the city name for the city with the largest value. I am able to get the absolute change but instead of getting the city name I am only able to get the index value. Is there a way I can get the city name instead of the index value. In the example below I am getting index value 3 instead of "Dallas".

df = pd.DataFrame({'City': ['Chicago', 'Atlanta', 'New York', 'Dallas'],
                   'col1': [10, 15, 80, 200],
                   'col2': [45, 75, 90, 5],
                   'col3': [25, 35, 60, 420],
                   'col4': [60, 105, 325, 55]})

Output:

       City  col1  col2  col3  col4
0   Chicago    10    45    25    60
1   Atlanta    15    75    35   105
2  New York    80    90    60   325
3    Dallas   200     5   420    55

Obtain Max Absolute Value of col1-col4

diff_row = df.max(axis=1) - df.min(axis=1)
print(diff_row.idxmax())

Current Output:

Desired Output:

Dallas

EdChum · Accepted Answer · 2017-06-21 15:58:25Z

2

Pass the index label to loc and the col of interest:

In[198]:
df.loc[diff_row.idxmax(), 'City']

Out[198]: 'Dallas'

answered Jun 21, 2017 at 15:58

EdChum

397k204 gold badges836 silver badges583 bronze badges

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

1 Comment

MBasith Over a year ago

Thanks, exactly what I was looking for.

piRSquared · Accepted Answer · 2017-06-21 16:16:39Z

1

Taking a step back and using numpy.ptp (peak to peak) to do the subtraction of max less min.

df.set_index('City').apply(np.ptp, 1).idxmax()

'Dallas'

We can push more over to numpy with

df.City.values[np.ptp(df.set_index('City').values, 1).argmax()]

'Dallas'

Timing

%timeit df.City.values[np.ptp(df.set_index('City').values, 1).argmax()]
%timeit df.set_index('City').apply(np.ptp, 1).idxmax()
1000 loops, best of 3: 399 µs per loop
1000 loops, best of 3: 1.03 ms per loop

%%timeit 
diff_row = df.max(axis=1) - df.min(axis=1)
df.loc[diff_row.idxmax(), 'City']
1000 loops, best of 3: 1.24 ms per loop

edited Jun 21, 2017 at 16:16

answered Jun 21, 2017 at 16:05

piRSquared

296k68 gold badges509 silver badges654 bronze badges

2 Comments

MBasith Over a year ago

This was also a cool way to do it. Unfortunately I needed to accomplish this without numpy. But will keep this for future reference. Thanks for the response!

piRSquared Over a year ago

@MBasith if you have pandas, you have numpy and can always reference it with pd.np. pandas imports numpy. Also, if you found this useful, feel free to up-vote the answer.

Collectives™ on Stack Overflow

Python Pandas Get Row Position Name instead of Index Value

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related