Python Importing Billboard Text to Excel

import csv

with open('Billboard.csv','a+') as Billboard:
    while True:
        try:
            Team1 = input("Enter Name of Team 1")
            Team2 = input("Enter Name of Team 2")
            choices = [Team1, Team2]
            PlayerNo = str(int(input("Enter Number of Player who scored")))
            Scored = input("Enter team who scared")
            if len(Team1+Team2+PlayerNo+Scored)+4 <=45 and Scored in choices:
                break
            print("String too long or team scored is not in the teams you entered")
        except ValueError:
            print("Enter a number for PlayerNo. not String")
    csv_writer = csv.writer(Billboard)
    goal = ['']*5+[i for i in "GOAL!"]
    csv_writer.writerow('')
    csv_writer.writerow(goal)
    if len(Team1)>7:
        Team1 = Team1[::(len(Team1)//7)+1]
    if len(Team2)>7:
        Team2 = Team2[::(len(Team2)//7)+1]
    teams = [i for i in Team1]+['']*(7-len(Team1))+[':']+[i for i in Team2]
    csv_writer.writerow(teams)
    message = ['']*((15-len(str(PlayerNo)+Scored)-1)//2)+[i for i in Scored] + ['']+[i for i in str(PlayerNo)]
    csv_writer.writerow(message)

Hello everyone. In this lesson, I would like to teach you how to write the python code to a past OCR pseudocode question. The algorithm through pseudocode would be a lot simpler and the question didn’t require much validation so I decided to add this to the python program so this actually feels like a complete program. I still go by the brief of the question which is why there strangely enough isn’t a score on the billboard but hopefully that can become an extra challenge to you to add that into the program after I’ve taught you all of the theory. Lets begin.

There are many ways to write to a csv file, however, I have decided to use the csv module as this feels more reliable. I decided to use the writerow methods rather than the Dictwriterow simply because we would never need to read from the file(also I have very little experience with Dictwriterow so I need to learn it more before I can explain it)

with open('Billboard.csv','a+') as Billboard:
    while True:
        try:
            Team1 = input("Enter Name of Team 1")
            Team2 = input("Enter Name of Team 2")
            choices = [Team1, Team2]
            PlayerNo = str(int(input("Enter Number of Player who scored")))
            Scored = input("Enter team who scared")
            if len(Team1+Team2+PlayerNo+Scored)+4 <=45 and Scored in choices:
                break
            print("String too long or team scored is not in the teams you entered")
        except ValueError:
            print("Enter a number for PlayerNo. not String")

All this code here has got to do with the input and validation of the data we want to send into the Billboard. Firstly, we open the csv file which we have made, in my case, I made a csv file called BillBoard.csv and YOU HAVE TO SAVE IT IN THE SAME FOLDER AS THE PYTHON CODE, if you don’t you’ll just get an error message about the file not being found. Also another warning, if you run the code while the excel file is open, you’re going to get an error on the lines of ‘access not granted’ because when you have the excel file open, python can’t update values inside of it, so close the excel file before running your code. The a+ represents append and the + means allow for updating and appending. This can be interchanged to other letters to represent different functions such as ‘w’=write. Finally this entire instruction has been condensed to the variable Billboard. by saying as Billboard at the end, we are basically saying Bilboard = open(‘Billboard.csv’,’a+’).

I use while True because I want this code to repeat definitely until the user inputs some valid data which marks all the criteria to let everything work. I use a try and except method here because when trying to validate the Player number, if you attempt to change the type of it to integer and it’s a string, the program will crash, so to combat this, I have put this code here to make sure that this type of error can be solved easily. So, we have 2 team names inputted and they are put into a choices list for validation later. We then have player number input which is then converted to an integer and then back to string, this will trigger a type error if the data is string and so run the except code, if the values are actually all integers, then python should be able to convert it between the 2 types and continue to run the code. Finally, we input the team that has scored.

if len(Team1+Team2+PlayerNo+Scored)+4 <=45 and Scored in choices:
                break
            print("String too long or team scored is not in the teams you entered")
        except ValueError:
            print("Enter a number for PlayerNo. not String")

This is the main code that makes sure all the data is correct. The line ‘if len(Team1+Team2+PlayerNo+Scored)+4 <=45’ comes from the brief of the question as the array that we have the message on is 3 x 15 which means we can’t allow for messages greater than 45, I’ve added 4 to the end because we would want spaces in between all the data so we can distinguish each from each other. The other validation is ‘Scored in choices’ which runs a simple check to make sure that the team that is entered to have scored is actually one of the teams in the game. It does this by iterating through the list choices and comparing the value of Scored to each value in it(so the 2 teams in this case) and if it is found, then true is returned while if it isn’t then we are returned false. So this code would mean that if we input for example Chelsea and Manchester as our teams, we can’t say that Tottenham scored since they aren’t in the game.

If the if statement if all satisfied, we run the break command which would kick us out of the while True loop that I had created. At this point, if the if statement hadn’t been satisfied this means there must have been an error in the input so the print statement prints a prompt to the user about what error they may have made before it iterates through the while loop all over again. We then have the except part of the code which only runs when we have a ValueError(the only possible type of error in this situation) and this just gives the user a different prompt to help then debug their problems.

NOTE: I didn’t do this in the code but its just come to my attention that you may run into some issues if your inputs have whitespaces in them(spaces) so when at the end of your input statements, put the method .strip() to remove all whitespaces. This basically means if you input for example ‘ Chelsea ‘ the program will automatically remove the whitespaces either side of the value to give ‘Chelsea’.

csv_writer = csv.writer(Billboard)
goal = ['']*5+[i for i in "GOAL!"]
csv_writer.writerow('')
csv_writer.writerow(goal)
if len(Team1)>7:
    Team1 = Team1[::(len(Team1)//7)+1]
if len(Team2)>7:
    Team2 = Team2[::(len(Team2)//7)+1]
teams = [i for i in Team1]+['']*(7-len(Team1))+[':']+[i for i in Team2]
csv_writer.writerow(teams)
message = ['']*((15-len(str(PlayerNo)+Scored)-1)//2)+[i for i in Scored] + ['']+[i for i in str(PlayerNo)]
csv_writer.writerow(message)

This final part of the code has got to do with writing to the database with the data that we now have had inputted. The first thing we will need is a means of writing to the csv file which is where I have made use the csv.writer() method to write to the csv file. We need to specify the file to write to using a parameter so I specify it as billboard which is the open(‘Billboard.csv’,’a+’) from the beginning of the code. Now we need to come up with the message which we want to be stored on the billboard, the brief tells us the first line should say ‘GOAL!’ so using list comprehension, we made a list where each data would represent a tile on the excel sheet. Since CSV means data are comma separated, the commas in the list would split up into separate excel tiles. The [‘’]*5 tells the list to start with 5 blank elements and then the + means to add the next instruction onto the end of this. We go into list comprehension here which has a lot of rules attached to it so I’ll attempt to make it as understandable as possible, we first specify what should be added into the list so in this case I say i but it could be 2+i or i**2, it’s whatever you want added to the list and then after that you set the operation of which will give the list the values for i. So I say for i in “GOAL!” which basically means that it will iterate through GOAL! with each index being i in the iterations so the first time i=G then i=O then i=A etc until the end of the string and they are added onto the end of the list each time. This separates the string into separate elements. This creates a list that looks like [‘’,’’,’’,’’,’’,’G’,’O’,’A’,’L’,’!’] which is all we need to add this to the end of the list.

csv_writer.writerow('')
csv_writer.writerow(goal)

These 2 lines are involved in writing to the excel file. We call the variable csv_writer which we declared earlier and tell it to write first ‘’, this creates a blank line in the excel file, I did this so we separate out the billboards from each other. Else the excel would be one straight line of billboards but now they will be separated. Next, we use the next instruction to write the goal code on the next line, This will write goal onto the screen.

if len(Team1)>7:
    Team1 = Team1[::(len(Team1)//7)+1]
if len(Team2)>7:
    Team2 = Team2[::(len(Team2)//7)+1]
teams = [i for i in Team1]+['']*(7-len(Team1))+[':']+[i for i in Team2]
csv_writer.writerow(teams)

This code is responsible for the second line of the code. I decided that the second line should just present the 2 teams to make it more neat. This means that I have 15 panels to show the 2 teams on, considering that I have a space in between them too, this means I have 14 panels to show the teams so 7 panels per team. This is why I put the if statements to check if the team names are longer than 7(for the 7 panels) and if they are, I run an index statement to shorten them so they can fit in the space. An index statement is built up on 3 paramters [a:b:c], a and b are used as start and stop positions when you index the string and c is what we are using in this case. C in this case specifies the interval between the characters that we are going to use. So if we had ‘Chelsea’ and c was 2, we are telling it to get every second character so we would get ‘Cesa’ as the return of the value. We use the calculation (len(Team1)//7)+1 to work out the value we want. Since DIV is //, the value is always rounded down so I added one onto the end of it so that we. By the time we have finished both the if statements, we have got 2 teams whose lenght when added will be ≤14. We now need to do a similar list comprehension to before to create the billboard values. So first we do [i for i in Team1] to write the name of the team into the list, then I want the colon that separates the team to be in the direct centre of the billboard (so it needs to be in the 8th space of the billboard so i add blank element to the list to get us to the 8th element. We calculate the number of blank elements to add using the calculation (7-len(Team1)), note that everything needs to be in brackets in calculations so the list comprehension can identify what is a calculation and what is a value we want added to the list. We then add the second team name. This is the completed list comprehension and after that we append this to the excel file in a similar way to the last.

message = ['']*((15-len(str(PlayerNo)+Scored)-1)//2)+[i for i in Scored] + ['']+[i for i in str(PlayerNo)]
csv_writer.writerow(message)

Finally, we need to add the Player number and the team that scored on the final line of the code. Try decode what I’ve done on this line. If you need any help, continue reading. 15-len(str(PlayerNo)+Scored)-1)//2 calculates the index where we would want to start so that the data fits in the direct centre of the bilboard so we add that value as blank entities and then we list comprehend the team and player number in the same way that we have been doing it before. We have completed the billboard, well done!

CHALLENGE: Try to add the a score tracker for the game at the bottom of the billoard. Good luck!!!