0

Below is my excel file I want to sort from highest to lowest

        A                             B
1) Tales:2                    Transport for London.....
2) Trump:30                   Roseanne Barr Actor leavin..... 
3) HIV:4                      Americas Transport for London........

Above are some entries from excel file. Tales:2 is at the A1 point Trump:40 is at the A2 point and Hiv:4 is at the A3 point. I want it sorted from highest to lowest. For Example Trump:30 should be on first place because it has the highest number that is 30.

jpp
  • 159,742
  • 34
  • 281
  • 339
Tayyab Nasir
  • 75
  • 9

1 Answers1

0

Read data into a pandas dataframe:

import pandas as pd
df = pd.read_excel('file.xlsx')

Calculate sorted indices via extracted numeric data and pd.Series.argsort:

indices = df['A'].str.split(':').str[-1].astype(int).argsort()

Then apply indices to your dataframe, reversing for highest first:

df = df.iloc[indices[::-1]]
jpp
  • 159,742
  • 34
  • 281
  • 339
  • Please answer my following question also https://stackoverflow.com/questions/50618565/find-repeated-words-in-a-excel-column-and-sort-it-according-to-number-of-occuren – Tayyab Nasir May 31 '18 at 07:08
  • ValueError: cannot convert float NaN to integer I get this error while running your suggested code can you please help me out – Tayyab Nasir May 31 '18 at 20:19
  • I guess this error is because length of A is less than length of B in the excel file – Tayyab Nasir May 31 '18 at 20:21