Error in code, help needed debugging, Python

Question

Problem: My code works when value[3] and [4] have 0 in it. However, when i try and add my actual values in, the code messes up (see below).

Text file explained here.

Joe,Bloggs,J.bloggs@anemailaddress.com,01269512355, 1,15, 0, 0, 0 
FName, LName, Email, Number, Division, Points, ..., ...,

Code objective: The code should take the bottom and top two scoring players in each division and -1 or +1 to the division. +1 if low score and -1 if high score. So, if there was 6 people in a division, 2 would go up, 2 would go down and 2 would stay the same division.

Code:

f = open('test copy.txt', 'r')
lines = []
for line in f.readlines():
    line = [x.strip() for x in line.split(',')]
    line[4] = int(line[4])
    line[5] = int(line[5])
    lines.append(line)
f.close()

ordered = sorted(zip(range(len(lines)), lines), key=lambda x: x[1][3])
lines[ordered[-1][0]][4] += 1
lines[ordered[-2][0]][4] += 1
lines[ordered[0][0]][4] -= 1
lines[ordered[1][0]][4] -= 1

with open('test copy.txt', 'w') as f:
    for line in lines:
        line = [str(x) for x in line]
        f.write(', '.join(line) + '\n')

Text file that works:

Joe,Bloggs,0,0, 1,15, 0, 0, 0,
Sarah,Brown,0,0, 1,12, 0, 0, 0,
Andrew,Smith,0,0, 1,4, 0, 0, 0,
Ray,Charles,0,0, 1,3, 0, 0, 0,
Kevin,White,0,0, 1,8, 0, 0, 0,
Samantha,Collins,0,0, 1,2, 0, 0, 0,

Test file that needs to work but doesn't:

Joe,Bloggs,J.bloggs@anemailaddress.com,01269512355, 1,15, 0, 0, 0
Sarah,Brown,S.brown@anemailaddress.com,01866522555, 1,12, 0, 0, 0
Andrew,Smith,A.smith@anemailaddress.com,01899512785, 1,4, 0, 0, 0
Ray,Charles,R.charles@anemailaddress.com,01268712321, 1,3, 0, 0, 0
Kevin,White,K.white@anemailaddress.com,01579122345, 1,8, 0, 0, 0
Samantha,Collins,S.collins@anemailaddress.com,04269916257, 1,2, 0, 0, 0

desired outcome:

Joe,Bloggs,J.bloggs@anemailaddress.com,01269512355, 0,15, 0, 0, 0
Sarah,Brown,S.brown@anemailaddress.com,01866522555, 0,12, 0, 0, 0
Andrew,Smith,A.smith@anemailaddress.com,01899512785, 1,4, 0, 0, 0
Ray,Charles,R.charles@anemailaddress.com,01268712321, 2,3, 0, 0, 0
Kevin,White,K.white@anemailaddress.com,01579122345, 1,8, 0, 0, 0
Samantha,Collins,S.collins@anemailaddress.com,04269916257, 2,2, 0, 0, 0

Because the value[3] is an integer, that seems to be messing with the code in sorting out which number in value[5] is bigger and then changing value[4] to the proper number.

This is what happens to the text file that needs to work:

Joe, Bloggs, J.bloggs@anemailaddress.com, 01269512355, 0, 15, 0, 0, 0
Sarah, Brown, S.brown@anemailaddress.com, 01866522555, 1, 12, 0, 0, 0
Andrew, Smith, A.smith@anemailaddress.com, 01899512785, 2, 4, 0, 0, 0
Ray, Charles, R.charles@anemailaddress.com, 01268712321, 0, 3, 0, 0, 0
Kevin, White, K.white@anemailaddress.com, 01579122345, 1, 8, 0, 0, 0
Samantha, Collins, S.collins@anemailaddress.com, 04269916257, 2, 2, 0, 0, 0

As you can see, it is different because of the phone number in value[3].

Answer 1

I use pandas and NumPy to solve this question. In the following source code I explain each step:

import numpy as np
import pandas as pd

# Load data:
d = np.genfromtxt('copy.txt', dtype=np.str, delimiter=',')
# Optional: Remove last three columns:
d = d[:,:-3]
# Convert to a dataframe:
d = pd.DataFrame(data = d[:,:], columns=['fname', 'lname', 'email', 'number', 'division', 'points'])
# Set relevant datatypes (for sorting):
d[['division', 'points']] = d[['division', 'points']].astype(int)
# Sorting:
d = d.sort_values(by=['division', 'points'], ascending=[True, False])
# Reindexing:
d.index = range(1, len(d) + 1)

# Print the data structure:
print d[['fname', 'division', 'points']]
#       fname  division  points
# 1       Joe         1      15
# 2    Andrew         1       4
# 3       Ray         1       3
# 4  Samantha         1       2
# 5     Sarah         2      12
# 6     Kevin         2       8

# Duplicate the column:
d['new_division'] = d['division']

def increase_first_elements (group, count):
    group['new_division'][:count] -= 1
    return group

def increase_last_elements (group, count):
    group['new_division'][-count:] += 1
    return group

# Reordering:
# In this example: Select first and last element (1).
# Important: Higher numbers (e.g. 2) work with larger tables (without select-intersections).
d = d.groupby('division').apply(increase_first_elements, 1)
d = d.groupby('division').apply(increase_last_elements, 1)

print d[['fname', 'division', 'new_division', 'points']]
#       fname  division  new_division  points
# 1       Joe         1             0      15
# 2    Andrew         1             1       4
# 3       Ray         1             1       3
# 4  Samantha         1             2       2
# 5     Sarah         2             1      12
# 6     Kevin         2             3       8

# Sorting:
d = d.sort_values(by=['new_division', 'points'], ascending=[True, False])
# Reindexing:
d.index = range(1, len(d) + 1)
# Cleanup:
d[['division']] = d[['new_division']].astype(int)
d = d.drop('new_division', 1)

print d[['fname', 'division', 'points']]
#       fname  division  points
# 1       Joe         0      15
# 2     Sarah         1      12
# 3    Andrew         1       4
# 4       Ray         1       3
# 5  Samantha         2       2
# 6     Kevin         3       8

# Extra: If you want to reset the points:
d[['points']] = 0
print d[['fname', 'division', 'points']]
#       fname  division  points
# 1       Joe         0       0
# 2     Sarah         1       0
# 3    Andrew         1       0
# 4       Ray         1       0
# 5  Samantha         2       0
# 6     Kevin         3       0

Answer 2

Edit: the issue was that the score was in the 6th column, so key=lambda x: x[1][3] should have been key=lambda x: x[1][5].

Original response below:

It looks to me like your code is correct and doing what you expect of it. Your desired output, however, doesn't match your algorithm - the two highest, Samantha and Andrew, should go from 1 and 1 to 2 and 2, and Ray should stay the same at 1.