I am new to python from R. They are quite different. I wrote a python code to deal with Excel file. The code was running well, but I think it's kind of the style of R. I want somebody to show me writing the code in python way.

The data structure looks like:

      Name     Details
1     AAA      first(100%-8)
2     BBB      first(50%-8),second(50%-8)
3     CCC      sixth(30%-8),seventh(60%-7),first(10%-7.75)
4     DDD      third(100%-6)
5     EEE      fifth(70%-7.5),second(30%-7.5)
6     FFF      first(70%-8),ninth(30%-6.75)
...   ...      ..........

As you can see, Mr.first gave Mr.AAA 8 points with 100% weights. Otherwise, in the 2nd,3rd,6th row, Mr.first gave different peoples with different scores. So the average scores that Mr.first gave is (8+8+7.75+8)/4 = 7.94, which is the average score of his group.

What I am looking for is: for the Mr.AAA, his final score is not 8 * 100%,it's 8 * (7.5/7.94)*100%, where 7.5 is a constant and 7.94 is the average score of the group of Mr.first. Similarly, for the Mr.BBB, his final score is 8 * (7.5/7.94)*50% + 8 * (7.5/7.75)*50%. Hope you get it.

So, question is pretty simple.

My code:

#-*- coding:utf-8 -*-
import xlrd
import re
data = xlrd.open_workbook(filename)   #read the data
table = data.sheets()[0]              #read the sheet
nrows = table.nrows                   #get the number of total rows
regr = r'[\u4e00-\u9fa5a-zA-Z]+' # regular expression for CHN and ENG names
regr1 = r'[0-9]+'                # for scores and percentage
score = {}                       # The dict: {AAA:{first:[1.0,8.0]},......}
group = {}                       # The dict: {first:[8,8,7.75,8],......}

for i in range(2,nrows):
   target = table.cell(i,10).value  # the details data [first(100%-8)]
   person = table.cell(i,2).value   # the Name data    [AAA]
   c = target.split(',')            # If in details data there are more than  
                                    # one person, then split them        
   score[person] = {}               # set an empty dict
   for j in c:
         d = re.findall(regr,j)     # get the name 
         d = "".join(d)             # transfer the list to string
         value = re.findall(regr1,j)   #get the score and percentage
         value1 = int(value[0])/100 # get the percentage 
         value2 = '.'.join([x for x in value[1:]])   # get the score
         value2 = float(value2)                      # change to float

#This part is for calculating the group average
for key in group:
   total = 0
   length = len(group[key])
   group[key] = [x for x in group[key]]
   for x in group[key]:
      total = total + x
   group[key] = total/length

output = {}       #set an empty dict to store output: {AAA:7.56,......}

#this part is for calculating the final score
for key in score:
   average = 0
   for subkey in score[key]:
      average = score[key][subkey][0] * score[key][subkey][1] 
                *7.5/group[subkey] + average
      output[key] = average

print(output)                                       #print the output

Finally here: For the reason of secrecy, I can not provide the raw data. But if you need, I can create the raw data. I am here, because I feeling that my code is tedious. I hope someone can help me to write it more elegantly. it's for future work.


  • \$\begingroup\$ just paste the data and make it a code block dont write html people will think you have data in HTML form \$\endgroup\$ – Vivek Kalyanarangan Nov 29 '16 at 9:02
  • \$\begingroup\$ are you sure your data looks like an HTML table instead of 2d rows and columns? \$\endgroup\$ – Vivek Kalyanarangan Nov 29 '16 at 9:04
  • \$\begingroup\$ you should definetly check pandas and maybe check the modern pandas introduction by tom augspurger and also the apply function might be interesting. Additionaly, the split-apply-combine workflow is explained here \$\endgroup\$ – Quickbeam2k1 Nov 29 '16 at 9:07
  • \$\begingroup\$ can you flatten your data or rearrange it into a dataframe? Then several operations become more "natural" \$\endgroup\$ – Quickbeam2k1 Nov 29 '16 at 9:11
  • 6
    \$\begingroup\$ As we all want to make our code more efficient or improve it in one way or another, try to write a title that summarizes what your code does, not what you want to get out of a review. Please see How to get the best value out of Code Review - Asking Questions for guidance on writing good question titles. \$\endgroup\$ – BCdotWEB Nov 29 '16 at 11:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy