Pandas: Find the maximum range in all the columns of dataframe
pandas, python
Solution
Something like `(df.max() - df.min()).idxmax()` should get you a maximum column:
>>> df = pd.DataFrame(np.random.random((5,4)), index=pd.Series(range(1,6), name="week"), columns=["City{}".format(i) for i in range(1,5)])
>>> df
City1 City2 City3 City4
week
1 0.908549 0.496167 0.220340 0.464060
2 0.429330 0.770133 0.824774 0.155694
3 0.893270 0.980108 0.574897 0.378443
4 0.982410 0.796103 0.080877 0.416432
5 0.444416 0.667695 0.459362 0.898792
>>> df.max() - df.min()
City1 0.553080
City2 0.483941
City3 0.743898
City4 0.743098
dtype: float64
>>> (df.max() - df.min()).idxmax()
'City3'
>>> df[(df.max() - df.min()).idxmax()]
week
1 0.220340
2 0.824774
3 0.574897
4 0.080877
5 0.459362
Name: City3, dtype: float64
If there might be more than one column at maximum range, you'll probably want something like
>>> col_ranges = df.max() - df.min()
>>> df.loc[:,col_ranges == col_ranges.max()]
City3
week
1 0.220340
2 0.824774
3 0.574897
4 0.080877
5 0.459362
instead.
Problem
I'm new very new to programming, so hopefully I'll ask my question clearly and perhaps you can guide me to the answer. I have a dataframe "x", where the index represents the week of the year, and each column represents a numerical value of a city. I'm attempting to find the column that has the maximum range (ie: maximum value - minimum value). I can imagine this will need a loop to find the maximum and minimum of each column, store this as an object (or as a new row at the bottom perhaps?), and then find the max in that object (or row). The dataframe looks like this: ``` City1 City2 ... CityN week 1 2 3 4 ... 53 ``` Feedback on etiquette or wording is also appreciated.