How to sort items by column from a text file in python

Question

I have the following items in a text file with names of cities and there quarter revenues:

 Atlanta   40 50 23 18
 Denver    56 78 34 11
 Miami     40 34 18 30
 Orlando   55 67 23 11
 Memphis   44 23 56 11
 Boston    55 67 33 23
 Tampa     45 67 54 77

I am having issues with sorting the quarter revenues in the list. My current program looks like this.

with open('revenue.txt', 'r') as f:
text = [line.split() for line in f]
print("{:<12}{:<14}{:>10}{:>9}".format("City", "Revenue", "Total", 
"Average"))
for a in text:
    city = a[0]
    revenue = [int(i) for i in a[1:5]]
    total = sum(revenue)
    avg = sum(revenue)/len(revenue)
    print("{:<12}{}{:>6}{:>9.2f}".format(city, revenue, total, avg))

print("\n""Sort the cities by their Quarter 1 revenues.")

print("\n""Sort the cities by their Quarter 3 revenues.")

print("\n""Sort the cities by their total revenues.")

I am trying to figure out how I can sort 1st quarter, 3rd quarter,and total columns and then print the city that is associated with them. For example:

Sort the cities by their Quarter 1 revenues
['Miami','Atlanta','Memphis','Tampa','Boston','Orlando','Denver']
#  [40,40,44,45,55,55,56]

Any help will be appreciated.

Answer 1

You can use pandas. It is so simple:

import pandas

with open('revenue.txt', 'r') as f:
    text = [line.split() for line in f]

df = pandas.DataFrame(text, columns = ["City", "Revenue", "Total", "Average", "Other"])
df_sorted_by_Total = df.sort_values("Total", ascending=True) # Sort by values of the City column

print(df_sorted_by_Total)
print(df_sorted_by_Total["City"].tolist()) # Print City column as list

Output:

      City Revenue Total Average Other
4  Memphis      44    23      56    11
2    Miami      40    34      18    30
0  Atlanta      40    50      23    18
3  Orlando      55    67      23    11
5   Boston      55    67      33    23
6    Tampa      45    67      54    77
1   Denver      56    78      34    11
['Memphis', 'Miami', 'Atlanta', 'Orlando', 'Boston', 'Tampa', 'Denver']

Answer 2

I'd use a dictionary along with a list to increase readability in your sort and how usable your code is. I went ahead and implemented a function for printing the table (because you do it several times inside your function). I also added a list of dictionaries as your new dataset. Then I used the function sorted to build your newly organized list.

with open('revenue.txt', 'r') as f:
    def print_table(data):
        for value in data:
            print("{city:<12}{revenue}{total:>6}{avg:>9.2f}".format(**value))
    text = [line.split() for line in f]
    data = []
    for a in text:
        revenue = [int(i) for i in a[1:5]]
        data.append({
            'city': a[0],
            'revenue': revenue,
            'total' : sum(revenue),
            'avg' : sum(revenue)/len(revenue)
        })
    print("\n""Original data set.")
    print_table(data)

    print("\n""Sort the cities by their Quarter 1 revenues.")
    print_table(sorted(data,key=lambda a: a['revenue'][0]))   

    print("\n""Sort the cities by their Quarter 3 revenues.")
    print_table(sorted(data,key=lambda a: a['revenue'][2]))   

    print("\n""Sort the cities by their total revenues.")
    print_table(sorted(data,key=lambda a: a['total']))   

Answer 3

You can sort it like this:

text = [ 
["Atlanta", 40,50,23,18 ],
["Denver ", 56,78,34,11 ],
["Miami  ", 40,34,18,30 ],
["Orlando", 55,67,23,11 ],
["Memphis", 44,23,56,11 ],
["Boston ", 55,67,33,23 ],
["Tampa  ", 45,67,54,77 ]]


for a in text:
    city = a[0]
    revenue = [int(i) for i in a[1:5]]
    total = sum(revenue)
    avg = sum(revenue)/len(revenue)
  #  print("{:<12}{}{:>6}{:>9.2f}{:>9.2f}{:>9.2f}".format(city, revenue, total, avg))
    a.append(total) # append it to your former array
    a.append(avg)   # append it to your former array


print ("1.Q")
printIt(sorted(text, key=lambda x: x[1])) # sort by 1. Quarter number
print ("2.Q")
printIt(sorted(text, key=lambda x: x[2])) # sort by 2. Quarter number
print ("3.Q")
printIt(sorted(text, key=lambda x: x[3])) # etc.
print ("4.Q")
printIt(sorted(text, key=lambda x: x[4]))
print ("Total")
printIt(sorted(text, key=lambda x: x[5]))
print ("Avg")
printIt(sorted(text, key=lambda x: x[6]))

sorted(..) will create a new list object - you could simply store it into something you want to use - for demonstational purposes just printed them the easy way.

Output:

1.Q
[ ['Atlanta', 40, 50, 23, 18, 131, 32.75], 
  ['Miami  ', 40, 34, 18, 30, 122, 30.5],
  ['Memphis', 44, 23, 56, 11, 134, 33.5], 
  ['Tampa  ', 45, 67, 54, 77, 243, 60.75], 
  ['Orlando', 55, 67, 23, 11, 156, 39.0], 
  ['Boston ', 55, 67, 33, 23, 178, 44.5], 
  ['Denver ', 56, 78, 34, 11, 179, 44.75]]

2.Q
[ ['Memphis', 44, 23, 56, 11, 134, 33.5], 
  ['Miami  ', 40, 34, 18, 30, 122, 30.5],
  ['Atlanta', 40, 50, 23, 18, 131, 32.75],
  ['Orlando', 55, 67, 23, 11, 156, 39.0], 
  ['Boston ', 55, 67, 33, 23, 178, 44.5], 
  ['Tampa  ', 45, 67, 54, 77, 243, 60.75], 
  ['Denver ', 56, 78, 34, 11, 179, 44.75]]

3.Q
[ ['Miami  ', 40, 34, 18, 30, 122, 30.5],
  ['Atlanta', 40, 50, 23, 18, 131, 32.75], 
  ['Orlando', 55, 67, 23, 11, 156, 39.0], 
  ['Boston ', 55, 67, 33, 23, 178, 44.5], 
  ['Denver ', 56, 78, 34, 11, 179, 44.75],
  ['Tampa  ', 45, 67, 54, 77, 243, 60.75], 
  ['Memphis', 44, 23, 56, 11, 134, 33.5]]

4.Q
[ ['Denver ', 56, 78, 34, 11, 179, 44.75], 
  ['Orlando', 55, 67, 23, 11, 156, 39.0],
  ['Memphis', 44, 23, 56, 11, 134, 33.5],
  ['Atlanta', 40, 50, 23, 18, 131, 32.75],
  ['Boston ', 55, 67, 33, 23, 178, 44.5],
  ['Miami  ', 40, 34, 18, 30, 122, 30.5], 
  ['Tampa  ', 45, 67, 54, 77, 243, 60.75]]

Total
[ ['Miami  ', 40, 34, 18, 30, 122, 30.5], 
  ['Atlanta', 40, 50, 23, 18, 131, 32.75],
 ['Memphis', 44, 23, 56, 11, 134, 33.5], 
  ['Orlando', 55, 67, 23, 11, 156, 39.0],
  ['Boston ', 55, 67, 33, 23, 178, 44.5],
  ['Denver ', 56, 78, 34, 11, 179, 44.75], 
  ['Tampa  ', 45, 67, 54, 77, 243, 60.75]]

Avg
[ ['Miami  ', 40, 34, 18, 30, 122, 30.5], 
  ['Atlanta', 40, 50, 23, 18, 131, 32.75],
  ['Memphis', 44, 23, 56, 11, 134, 33.5],
  ['Orlando', 55, 67, 23, 11, 156, 39.0],
  ['Boston ', 55, 67, 33, 23, 178, 44.5],
  ['Denver ', 56, 78, 34, 11, 179, 44.75], 
  ['Tampa  ', 45, 67, 54, 77, 243, 60.75]] 

This applies what is shown in How to sort a list of objects based on an attribute of the objects? to your problem. Flagged question as duplicate.