Building an Excel Football League Table — Traditional Methods vs Dynamic Arrays

Image for post

Introduction

I

am going to show you the different ways you can build a football league table in Excel. Some of the methods are old school but others utilise Excel’s new capabilities.

In case you weren’t already aware, Excel has undergone a big change to its calculation engine fairly recently. The concept of dynamic arrays was first introduced back in September 2018, however, for many Microsoft 365 users the first batch of new functions took an awfully long time to appear. Unless you have been an Office Insider, you will not have been able to use them. Even though the update was rolled out to my copy towards the start of the year, there were still swathes of users who were kept waiting.

Since dynamic arrays were introduced in Excel, array formulas no long require you to press Ctrl + Shift + Return every time you edit a cell. This was an annoying practice that made many users, including myself, reluctant to use arrays. They just didn’t feel like a native and integrated part of Excel. Now you can use an array formula like any other — without that additional step.

If you want to find out more about the new functions, I recommend you visit Microsoft’s help page for each one: XLOOKUP, FILTER, UNIQUE, SORTBY, SORT, SEQUENCE, RANDARRAY.

Before we start

Download the workbook from here: https://bit.ly/39mlqkp.

Quick caveat: if you have an older version of Excel, you will find some of the examples do not work because of compatibility issues. This is unavoidable unless you purchase a Microsoft 365 subscription. Personally, I would recommend you do so.

Steps taken

Firstly, a dataset is required containing a list of all the matches played and their respective results. I have used English Premier League data from the 2019/20 season for this example. To conserve space elsewhere, the matches are stored in a separate worksheet called Data — with the table itself named DataTable.

Image for post

2019/20 Premier League Dataset

You’ll notice there’s a calculated column on the end called Result. This formula looks at the home_goal and away_goal fields for each match played and determines whether the outcome was a home win (H), draw (D) or away win (A).

There are three sections: Part A, Part B and Part C. Each contains multiple league tables that output identical values, but the method used differs.

Any kind of system that involves ranking data is typically going to require an unordered and ordered table. The former houses the mathematical calculations and determines the ranking of each row, whilst the latter references it to output the data in the correct order. Part A and Part B are based off this principle. Part C, however, contains two variants that are not dependent on an additional table.

The tables in the workbook use these headers:

POS (position)
TEAM
P (matches played)
W (matches won)
D (matches drawn)
L (matches lost)
F (goals for)
A (goals against)
GD (goal difference)
PTS (points)
RANK* (table position when points are sorted in descending order)

*Table A2 only

Part A

The approaches here are all based on official Excel tables. The way to tell if what looks like a table is indeed a table— is to check if it has a small blue triangle in the bottom-right, or to click on it and the Table Design tab will appear in the ribbon.

We start off by creating **Table A1, **which is unordered and forms the base for **Table A2, Table A3 and Table A4 **to work off. The P, W, D, L columns use COUNTIFS formulas to count the number of matches a team has played, won, drawn and lost respectively. It’s important to note that a single COUNTIFS formula only allows for AND conditions. That means all criteria must be met for a successful count. As we have home and away matches to consider, we need to use two COUNTIFS statements in the same cell to add the counts together. The same concept applies to the SUMIFS function, which has been used for the columns that involve addition: F and A.

#excel #football #premier-league #soccer #dynamic-array

What is GEEK

Buddha Community

Building an Excel Football League Table — Traditional Methods vs Dynamic Arrays

Alex O'Sullivan

1666082925

How to Create Arrays in Python

In this tutorial, you'll know the basics of how to create arrays in Python using the array module. Learn how to use Python arrays. You'll see how to define them and the different methods commonly used for performing operations on them.

This tutorialvideo on 'Arrays in Python' will help you establish a strong hold on all the fundamentals in python programming language. Below are the topics covered in this video:
1:15 What is an array?
2:53 Is python list same as an array?
3:48 How to create arrays in python?
7:19 Accessing array elements
9:59 Basic array operations
- 10:33 Finding the length of an array
- 11:44 Adding Elements
- 15:06 Removing elements
- 18:32 Array concatenation
- 20:59 Slicing
- 23:26 Looping

Python Array Tutorial – Define, Index, Methods

In this article, you'll learn how to use Python arrays. You'll see how to define them and the different methods commonly used for performing operations on them.

The artcile covers arrays that you create by importing the array module. We won't cover NumPy arrays here.

Introduction to Arrays
1. The differences between Lists and Arrays
2. When to use arrays
How to use arrays
1. Define arrays
2. Find the length of arrays
3. Array indexing
4. Search through arrays
5. Loop through arrays
6. Slice an array
Array methods for performing operations
1. Change an existing value
2. Add a new value
3. Remove a value
Conclusion

Let's get started!

What are Python Arrays?

Arrays are a fundamental data structure, and an important part of most programming languages. In Python, they are containers which are able to store more than one item at the same time.

Specifically, they are an ordered collection of elements with every value being of the same data type. That is the most important thing to remember about Python arrays - the fact that they can only hold a sequence of multiple items that are of the same type.

What's the Difference between Python Lists and Python Arrays?

Lists are one of the most common data structures in Python, and a core part of the language.

Lists and arrays behave similarly.

Just like arrays, lists are an ordered sequence of elements.

They are also mutable and not fixed in size, which means they can grow and shrink throughout the life of the program. Items can be added and removed, making them very flexible to work with.

However, lists and arrays are not the same thing.

Lists store items that are of various data types. This means that a list can contain integers, floating point numbers, strings, or any other Python data type, at the same time. That is not the case with arrays.

As mentioned in the section above, arrays store only items that are of the same single data type. There are arrays that contain only integers, or only floating point numbers, or only any other Python data type you want to use.

When to Use Python Arrays

Lists are built into the Python programming language, whereas arrays aren't. Arrays are not a built-in data structure, and therefore need to be imported via the array module in order to be used.

Arrays of the array module are a thin wrapper over C arrays, and are useful when you want to work with homogeneous data.

They are also more compact and take up less memory and space which makes them more size efficient compared to lists.

If you want to perform mathematical calculations, then you should use NumPy arrays by importing the NumPy package. Besides that, you should just use Python arrays when you really need to, as lists work in a similar way and are more flexible to work with.

How to Use Arrays in Python

In order to create Python arrays, you'll first have to import the array module which contains all the necassary functions.

There are three ways you can import the array module:

By using import array at the top of the file. This includes the module array. You would then go on to create an array using array.array().

import array

#how you would create an array
array.array()

Instead of having to type array.array() all the time, you could use import array as arr at the top of the file, instead of import array alone. You would then create an array by typing arr.array(). The arr acts as an alias name, with the array constructor then immediately following it.

import array as arr

#how you would create an array
arr.array()

Lastly, you could also use from array import *, with * importing all the functionalities available. You would then create an array by writing the array() constructor alone.

from array import *

#how you would create an array
array()

How to Define Arrays in Python

Once you've imported the array module, you can then go on to define a Python array.

The general syntax for creating an array looks like this:

variable_name = array(typecode,[elements])

Let's break it down:

variable_name would be the name of the array.
The typecode specifies what kind of elements would be stored in the array. Whether it would be an array of integers, an array of floats or an array of any other Python data type. Remember that all elements should be of the same data type.
Inside square brackets you mention the elements that would be stored in the array, with each element being separated by a comma. You can also create an empty array by just writing variable_name = array(typecode) alone, without any elements.

Below is a typecode table, with the different typecodes that can be used with the different data types when defining Python arrays:

TYPECODE	C TYPE	PYTHON TYPE	SIZE
'b'	signed char	int	1
'B'	unsigned char	int	1
'u'	wchar_t	Unicode character	2
'h'	signed short	int	2
'H'	unsigned short	int	2
'i'	signed int	int	2
'I'	unsigned int	int	2
'l'	signed long	int	4
'L'	unsigned long	int	4
'q'	signed long long	int	8
'Q'	unsigned long long	int	8
'f'	float	float	4
'd'	double	float	8

Tying everything together, here is an example of how you would define an array in Python:

import array as arr 

numbers = arr.array('i',[10,20,30])


print(numbers)

#output

#array('i', [10, 20, 30])

Let's break it down:

First we included the array module, in this case with import array as arr .
Then, we created a numbers array.
We used arr.array() because of import array as arr .
Inside the array() constructor, we first included i, for signed integer. Signed integer means that the array can include positive and negative values. Unsigned integer, with H for example, would mean that no negative values are allowed.
Lastly, we included the values to be stored in the array in square brackets.

Keep in mind that if you tried to include values that were not of i typecode, meaning they were not integer values, you would get an error:

import array as arr 

numbers = arr.array('i',[10.0,20,30])


print(numbers)

#output

#Traceback (most recent call last):
# File "/Users/dionysialemonaki/python_articles/demo.py", line 14, in <module>
#   numbers = arr.array('i',[10.0,20,30])
#TypeError: 'float' object cannot be interpreted as an integer

In the example above, I tried to include a floating point number in the array. I got an error because this is meant to be an integer array only.

Another way to create an array is the following:

from array import *

#an array of floating point values
numbers = array('d',[10.0,20.0,30.0])

print(numbers)

#output

#array('d', [10.0, 20.0, 30.0])

The example above imported the array module via from array import * and created an array numbers of float data type. This means that it holds only floating point numbers, which is specified with the 'd' typecode.

How to Find the Length of an Array in Python

To find out the exact number of elements contained in an array, use the built-in len() method.

It will return the integer number that is equal to the total number of elements in the array you specify.

import array as arr 

numbers = arr.array('i',[10,20,30])


print(len(numbers))

#output
# 3

In the example above, the array contained three elements – 10, 20, 30 – so the length of numbers is 3.

Array Indexing and How to Access Individual Items in an Array in Python

Each item in an array has a specific address. Individual items are accessed by referencing their index number.

Indexing in Python, and in all programming languages and computing in general, starts at 0. It is important to remember that counting starts at 0 and not at 1.

To access an element, you first write the name of the array followed by square brackets. Inside the square brackets you include the item's index number.

The general syntax would look something like this:

array_name[index_value_of_item]

Here is how you would access each individual element in an array:

import array as arr 

numbers = arr.array('i',[10,20,30])

print(numbers[0]) # gets the 1st element
print(numbers[1]) # gets the 2nd element
print(numbers[2]) # gets the 3rd element

#output

#10
#20
#30

Remember that the index value of the last element of an array is always one less than the length of the array. Where n is the length of the array, n - 1 will be the index value of the last item.

Note that you can also access each individual element using negative indexing.

With negative indexing, the last element would have an index of -1, the second to last element would have an index of -2, and so on.

Here is how you would get each item in an array using that method:

import array as arr 

numbers = arr.array('i',[10,20,30])

print(numbers[-1]) #gets last item
print(numbers[-2]) #gets second to last item
print(numbers[-3]) #gets first item
 
#output

#30
#20
#10

How to Search Through an Array in Python

You can find out an element's index number by using the index() method.

You pass the value of the element being searched as the argument to the method, and the element's index number is returned.

import array as arr 

numbers = arr.array('i',[10,20,30])

#search for the index of the value 10
print(numbers.index(10))

#output

#0

If there is more than one element with the same value, the index of the first instance of the value will be returned:

import array as arr 


numbers = arr.array('i',[10,20,30,10,20,30])

#search for the index of the value 10
#will return the index number of the first instance of the value 10
print(numbers.index(10))

#output

#0

How to Loop through an Array in Python

You've seen how to access each individual element in an array and print it out on its own.

You've also seen how to print the array, using the print() method. That method gives the following result:

import array as arr 

numbers = arr.array('i',[10,20,30])

print(numbers)

#output

#array('i', [10, 20, 30])

What if you want to print each value one by one?

This is where a loop comes in handy. You can loop through the array and print out each value, one-by-one, with each loop iteration.

For this you can use a simple for loop:

import array as arr 

numbers = arr.array('i',[10,20,30])

for number in numbers:
    print(number)
    
#output
#10
#20
#30

You could also use the range() function, and pass the len() method as its parameter. This would give the same result as above:

import array as arr  

values = arr.array('i',[10,20,30])

#prints each individual value in the array
for value in range(len(values)):
    print(values[value])

#output

#10
#20
#30

How to Slice an Array in Python

To access a specific range of values inside the array, use the slicing operator, which is a colon :.

When using the slicing operator and you only include one value, the counting starts from 0 by default. It gets the first item, and goes up to but not including the index number you specify.

import array as arr 

#original array
numbers = arr.array('i',[10,20,30])

#get the values 10 and 20 only
print(numbers[:2])  #first to second position

#output

#array('i', [10, 20])

When you pass two numbers as arguments, you specify a range of numbers. In this case, the counting starts at the position of the first number in the range, and up to but not including the second one:

import array as arr 

#original array
numbers = arr.array('i',[10,20,30])


#get the values 20 and 30 only
print(numbers[1:3]) #second to third position

#output

#rray('i', [20, 30])

Methods For Performing Operations on Arrays in Python

Arrays are mutable, which means they are changeable. You can change the value of the different items, add new ones, or remove any you don't want in your program anymore.

Let's see some of the most commonly used methods which are used for performing operations on arrays.

How to Change the Value of an Item in an Array

You can change the value of a specific element by speficying its position and assigning it a new value:

import array as arr 

#original array
numbers = arr.array('i',[10,20,30])

#change the first element
#change it from having a value of 10 to having a value of 40
numbers[0] = 40

print(numbers)

#output

#array('i', [40, 20, 30])

How to Add a New Value to an Array

To add one single value at the end of an array, use the append() method:

import array as arr 

#original array
numbers = arr.array('i',[10,20,30])

#add the integer 40 to the end of numbers
numbers.append(40)

print(numbers)

#output

#array('i', [10, 20, 30, 40])

Be aware that the new item you add needs to be the same data type as the rest of the items in the array.

Look what happens when I try to add a float to an array of integers:

import array as arr 

#original array
numbers = arr.array('i',[10,20,30])

#add the integer 40 to the end of numbers
numbers.append(40.0)

print(numbers)

#output

#Traceback (most recent call last):
#  File "/Users/dionysialemonaki/python_articles/demo.py", line 19, in <module>
#   numbers.append(40.0)
#TypeError: 'float' object cannot be interpreted as an integer

But what if you want to add more than one value to the end an array?

Use the extend() method, which takes an iterable (such as a list of items) as an argument. Again, make sure that the new items are all the same data type.

import array as arr 

#original array
numbers = arr.array('i',[10,20,30])

#add the integers 40,50,60 to the end of numbers
#The numbers need to be enclosed in square brackets

numbers.extend([40,50,60])

print(numbers)

#output

#array('i', [10, 20, 30, 40, 50, 60])

And what if you don't want to add an item to the end of an array? Use the insert() method, to add an item at a specific position.

The insert() function takes two arguments: the index number of the position the new element will be inserted, and the value of the new element.

import array as arr 

#original array
numbers = arr.array('i',[10,20,30])

#add the integer 40 in the first position
#remember indexing starts at 0

numbers.insert(0,40)

print(numbers)

#output

#array('i', [40, 10, 20, 30])

How to Remove a Value from an Array

To remove an element from an array, use the remove() method and include the value as an argument to the method.

import array as arr 

#original array
numbers = arr.array('i',[10,20,30])

numbers.remove(10)

print(numbers)

#output

#array('i', [20, 30])

With remove(), only the first instance of the value you pass as an argument will be removed.

See what happens when there are more than one identical values:

import array as arr 

#original array
numbers = arr.array('i',[10,20,30,10,20])

numbers.remove(10)

print(numbers)

#output

#array('i', [20, 30, 10, 20])

Only the first occurence of 10 is removed.

You can also use the pop() method, and specify the position of the element to be removed:

import array as arr 

#original array
numbers = arr.array('i',[10,20,30,10,20])

#remove the first instance of 10
numbers.pop(0)

print(numbers)

#output

#array('i', [20, 30, 10, 20])

Conclusion

And there you have it - you now know the basics of how to create arrays in Python using the array module. Hopefully you found this guide helpful.

Thanks for reading and happy coding!

#python #programming

Connor Mills

1670560264

Understanding Arrays in Python

Learn how to use Python arrays. Create arrays in Python using the array module. You'll see how to define them and the different methods commonly used for performing operations on them.

The artcile covers arrays that you create by importing the array module. We won't cover NumPy arrays here.

Introduction to Arrays
1. The differences between Lists and Arrays
2. When to use arrays
How to use arrays
1. Define arrays
2. Find the length of arrays
3. Array indexing
4. Search through arrays
5. Loop through arrays
6. Slice an array
Array methods for performing operations
1. Change an existing value
2. Add a new value
3. Remove a value
Conclusion

Let's get started!

What are Python Arrays?

Arrays are a fundamental data structure, and an important part of most programming languages. In Python, they are containers which are able to store more than one item at the same time.

Specifically, they are an ordered collection of elements with every value being of the same data type. That is the most important thing to remember about Python arrays - the fact that they can only hold a sequence of multiple items that are of the same type.

What's the Difference between Python Lists and Python Arrays?

Lists are one of the most common data structures in Python, and a core part of the language.

Lists and arrays behave similarly.

Just like arrays, lists are an ordered sequence of elements.

They are also mutable and not fixed in size, which means they can grow and shrink throughout the life of the program. Items can be added and removed, making them very flexible to work with.

However, lists and arrays are not the same thing.

Lists store items that are of various data types. This means that a list can contain integers, floating point numbers, strings, or any other Python data type, at the same time. That is not the case with arrays.

As mentioned in the section above, arrays store only items that are of the same single data type. There are arrays that contain only integers, or only floating point numbers, or only any other Python data type you want to use.

When to Use Python Arrays

Lists are built into the Python programming language, whereas arrays aren't. Arrays are not a built-in data structure, and therefore need to be imported via the array module in order to be used.

Arrays of the array module are a thin wrapper over C arrays, and are useful when you want to work with homogeneous data.

They are also more compact and take up less memory and space which makes them more size efficient compared to lists.

If you want to perform mathematical calculations, then you should use NumPy arrays by importing the NumPy package. Besides that, you should just use Python arrays when you really need to, as lists work in a similar way and are more flexible to work with.

How to Use Arrays in Python

In order to create Python arrays, you'll first have to import the array module which contains all the necassary functions.

There are three ways you can import the array module:

By using import array at the top of the file. This includes the module array. You would then go on to create an array using array.array().

import array

#how you would create an array
array.array()

Instead of having to type array.array() all the time, you could use import array as arr at the top of the file, instead of import array alone. You would then create an array by typing arr.array(). The arr acts as an alias name, with the array constructor then immediately following it.

import array as arr

#how you would create an array
arr.array()

Lastly, you could also use from array import *, with * importing all the functionalities available. You would then create an array by writing the array() constructor alone.

from array import *

#how you would create an array
array()

How to Define Arrays in Python

Once you've imported the array module, you can then go on to define a Python array.

The general syntax for creating an array looks like this:

variable_name = array(typecode,[elements])

Let's break it down:

variable_name would be the name of the array.
The typecode specifies what kind of elements would be stored in the array. Whether it would be an array of integers, an array of floats or an array of any other Python data type. Remember that all elements should be of the same data type.
Inside square brackets you mention the elements that would be stored in the array, with each element being separated by a comma. You can also create an empty array by just writing variable_name = array(typecode) alone, without any elements.

Below is a typecode table, with the different typecodes that can be used with the different data types when defining Python arrays:

TYPECODE	C TYPE	PYTHON TYPE	SIZE
'b'	signed char	int	1
'B'	unsigned char	int	1
'u'	wchar_t	Unicode character	2
'h'	signed short	int	2
'H'	unsigned short	int	2
'i'	signed int	int	2
'I'	unsigned int	int	2
'l'	signed long	int	4
'L'	unsigned long	int	4
'q'	signed long long	int	8
'Q'	unsigned long long	int	8
'f'	float	float	4
'd'	double	float	8

Tying everything together, here is an example of how you would define an array in Python:

import array as arr 

numbers = arr.array('i',[10,20,30])


print(numbers)

#output

#array('i', [10, 20, 30])

Let's break it down:

First we included the array module, in this case with import array as arr .
Then, we created a numbers array.
We used arr.array() because of import array as arr .
Inside the array() constructor, we first included i, for signed integer. Signed integer means that the array can include positive and negative values. Unsigned integer, with H for example, would mean that no negative values are allowed.
Lastly, we included the values to be stored in the array in square brackets.

Keep in mind that if you tried to include values that were not of i typecode, meaning they were not integer values, you would get an error:

import array as arr 

numbers = arr.array('i',[10.0,20,30])


print(numbers)

#output

#Traceback (most recent call last):
# File "/Users/dionysialemonaki/python_articles/demo.py", line 14, in <module>
#   numbers = arr.array('i',[10.0,20,30])
#TypeError: 'float' object cannot be interpreted as an integer

In the example above, I tried to include a floating point number in the array. I got an error because this is meant to be an integer array only.

Another way to create an array is the following:

from array import *

#an array of floating point values
numbers = array('d',[10.0,20.0,30.0])

print(numbers)

#output

#array('d', [10.0, 20.0, 30.0])

The example above imported the array module via from array import * and created an array numbers of float data type. This means that it holds only floating point numbers, which is specified with the 'd' typecode.

How to Find the Length of an Array in Python

To find out the exact number of elements contained in an array, use the built-in len() method.

It will return the integer number that is equal to the total number of elements in the array you specify.

import array as arr 

numbers = arr.array('i',[10,20,30])


print(len(numbers))

#output
# 3

In the example above, the array contained three elements – 10, 20, 30 – so the length of numbers is 3.

Array Indexing and How to Access Individual Items in an Array in Python

Each item in an array has a specific address. Individual items are accessed by referencing their index number.

Indexing in Python, and in all programming languages and computing in general, starts at 0. It is important to remember that counting starts at 0 and not at 1.

To access an element, you first write the name of the array followed by square brackets. Inside the square brackets you include the item's index number.

The general syntax would look something like this:

array_name[index_value_of_item]

Here is how you would access each individual element in an array:

import array as arr 

numbers = arr.array('i',[10,20,30])

print(numbers[0]) # gets the 1st element
print(numbers[1]) # gets the 2nd element
print(numbers[2]) # gets the 3rd element

#output

#10
#20
#30

Remember that the index value of the last element of an array is always one less than the length of the array. Where n is the length of the array, n - 1 will be the index value of the last item.

Note that you can also access each individual element using negative indexing.

With negative indexing, the last element would have an index of -1, the second to last element would have an index of -2, and so on.

Here is how you would get each item in an array using that method:

import array as arr 

numbers = arr.array('i',[10,20,30])

print(numbers[-1]) #gets last item
print(numbers[-2]) #gets second to last item
print(numbers[-3]) #gets first item
 
#output

#30
#20
#10

How to Search Through an Array in Python

You can find out an element's index number by using the index() method.

You pass the value of the element being searched as the argument to the method, and the element's index number is returned.

import array as arr 

numbers = arr.array('i',[10,20,30])

#search for the index of the value 10
print(numbers.index(10))

#output

#0

If there is more than one element with the same value, the index of the first instance of the value will be returned:

import array as arr 


numbers = arr.array('i',[10,20,30,10,20,30])

#search for the index of the value 10
#will return the index number of the first instance of the value 10
print(numbers.index(10))

#output

#0

How to Loop through an Array in Python

You've seen how to access each individual element in an array and print it out on its own.

You've also seen how to print the array, using the print() method. That method gives the following result:

import array as arr 

numbers = arr.array('i',[10,20,30])

print(numbers)

#output

#array('i', [10, 20, 30])

What if you want to print each value one by one?

This is where a loop comes in handy. You can loop through the array and print out each value, one-by-one, with each loop iteration.

For this you can use a simple for loop:

import array as arr 

numbers = arr.array('i',[10,20,30])

for number in numbers:
    print(number)
    
#output
#10
#20
#30

You could also use the range() function, and pass the len() method as its parameter. This would give the same result as above:

import array as arr  

values = arr.array('i',[10,20,30])

#prints each individual value in the array
for value in range(len(values)):
    print(values[value])

#output

#10
#20
#30

How to Slice an Array in Python

To access a specific range of values inside the array, use the slicing operator, which is a colon :.

When using the slicing operator and you only include one value, the counting starts from 0 by default. It gets the first item, and goes up to but not including the index number you specify.


import array as arr 

#original array
numbers = arr.array('i',[10,20,30])

#get the values 10 and 20 only
print(numbers[:2])  #first to second position

#output

#array('i', [10, 20])

When you pass two numbers as arguments, you specify a range of numbers. In this case, the counting starts at the position of the first number in the range, and up to but not including the second one:

import array as arr 

#original array
numbers = arr.array('i',[10,20,30])


#get the values 20 and 30 only
print(numbers[1:3]) #second to third position

#output

#rray('i', [20, 30])

Methods For Performing Operations on Arrays in Python

Arrays are mutable, which means they are changeable. You can change the value of the different items, add new ones, or remove any you don't want in your program anymore.

Let's see some of the most commonly used methods which are used for performing operations on arrays.

How to Change the Value of an Item in an Array

You can change the value of a specific element by speficying its position and assigning it a new value:

import array as arr 

#original array
numbers = arr.array('i',[10,20,30])

#change the first element
#change it from having a value of 10 to having a value of 40
numbers[0] = 40

print(numbers)

#output

#array('i', [40, 20, 30])

How to Add a New Value to an Array

To add one single value at the end of an array, use the append() method:

import array as arr 

#original array
numbers = arr.array('i',[10,20,30])

#add the integer 40 to the end of numbers
numbers.append(40)

print(numbers)

#output

#array('i', [10, 20, 30, 40])

Be aware that the new item you add needs to be the same data type as the rest of the items in the array.

Look what happens when I try to add a float to an array of integers:

import array as arr 

#original array
numbers = arr.array('i',[10,20,30])

#add the integer 40 to the end of numbers
numbers.append(40.0)

print(numbers)

#output

#Traceback (most recent call last):
#  File "/Users/dionysialemonaki/python_articles/demo.py", line 19, in <module>
#   numbers.append(40.0)
#TypeError: 'float' object cannot be interpreted as an integer

But what if you want to add more than one value to the end an array?

Use the extend() method, which takes an iterable (such as a list of items) as an argument. Again, make sure that the new items are all the same data type.

import array as arr 

#original array
numbers = arr.array('i',[10,20,30])

#add the integers 40,50,60 to the end of numbers
#The numbers need to be enclosed in square brackets

numbers.extend([40,50,60])

print(numbers)

#output

#array('i', [10, 20, 30, 40, 50, 60])

And what if you don't want to add an item to the end of an array? Use the insert() method, to add an item at a specific position.

The insert() function takes two arguments: the index number of the position the new element will be inserted, and the value of the new element.

import array as arr 

#original array
numbers = arr.array('i',[10,20,30])

#add the integer 40 in the first position
#remember indexing starts at 0

numbers.insert(0,40)

print(numbers)

#output

#array('i', [40, 10, 20, 30])

How to Remove a Value from an Array

To remove an element from an array, use the remove() method and include the value as an argument to the method.

import array as arr 

#original array
numbers = arr.array('i',[10,20,30])

numbers.remove(10)

print(numbers)

#output

#array('i', [20, 30])

With remove(), only the first instance of the value you pass as an argument will be removed.

See what happens when there are more than one identical values:


import array as arr 

#original array
numbers = arr.array('i',[10,20,30,10,20])

numbers.remove(10)

print(numbers)

#output

#array('i', [20, 30, 10, 20])

Only the first occurence of 10 is removed.

You can also use the pop() method, and specify the position of the element to be removed:

import array as arr 

#original array
numbers = arr.array('i',[10,20,30,10,20])

#remove the first instance of 10
numbers.pop(0)

print(numbers)

#output

#array('i', [20, 30, 10, 20])

Conclusion

And there you have it - you now know the basics of how to create arrays in Python using the array module. Hopefully you found this guide helpful.

You'll start from the basics and learn in an interacitve and beginner-friendly way. You'll also build five projects at the end to put into practice and help reinforce what you learned.

Thanks for reading and happy coding!

Original article source at https://www.freecodecamp.org

#python

Justyn Ortiz

1598464800

Building an Excel Football League Table — Traditional Methods vs Dynamic Arrays

Image for post

Introduction

I

am going to show you the different ways you can build a football league table in Excel. Some of the methods are old school but others utilise Excel’s new capabilities.

In case you weren’t already aware, Excel has undergone a big change to its calculation engine fairly recently. The concept of dynamic arrays was first introduced back in September 2018, however, for many Microsoft 365 users the first batch of new functions took an awfully long time to appear. Unless you have been an Office Insider, you will not have been able to use them. Even though the update was rolled out to my copy towards the start of the year, there were still swathes of users who were kept waiting.

Since dynamic arrays were introduced in Excel, array formulas no long require you to press Ctrl + Shift + Return every time you edit a cell. This was an annoying practice that made many users, including myself, reluctant to use arrays. They just didn’t feel like a native and integrated part of Excel. Now you can use an array formula like any other — without that additional step.

If you want to find out more about the new functions, I recommend you visit Microsoft’s help page for each one: XLOOKUP, FILTER, UNIQUE, SORTBY, SORT, SEQUENCE, RANDARRAY.

Before we start

Download the workbook from here: https://bit.ly/39mlqkp.

Quick caveat: if you have an older version of Excel, you will find some of the examples do not work because of compatibility issues. This is unavoidable unless you purchase a Microsoft 365 subscription. Personally, I would recommend you do so.

Steps taken

Firstly, a dataset is required containing a list of all the matches played and their respective results. I have used English Premier League data from the 2019/20 season for this example. To conserve space elsewhere, the matches are stored in a separate worksheet called Data — with the table itself named DataTable.

Image for post

2019/20 Premier League Dataset

You’ll notice there’s a calculated column on the end called Result. This formula looks at the home_goal and away_goal fields for each match played and determines whether the outcome was a home win (H), draw (D) or away win (A).

There are three sections: Part A, Part B and Part C. Each contains multiple league tables that output identical values, but the method used differs.

Any kind of system that involves ranking data is typically going to require an unordered and ordered table. The former houses the mathematical calculations and determines the ranking of each row, whilst the latter references it to output the data in the correct order. Part A and Part B are based off this principle. Part C, however, contains two variants that are not dependent on an additional table.

The tables in the workbook use these headers:

POS (position)
TEAM
P (matches played)
W (matches won)
D (matches drawn)
L (matches lost)
F (goals for)
A (goals against)
GD (goal difference)
PTS (points)
RANK* (table position when points are sorted in descending order)

*Table A2 only

Part A

The approaches here are all based on official Excel tables. The way to tell if what looks like a table is indeed a table— is to check if it has a small blue triangle in the bottom-right, or to click on it and the Table Design tab will appear in the ribbon.

We start off by creating **Table A1, **which is unordered and forms the base for **Table A2, Table A3 and Table A4 **to work off. The P, W, D, L columns use COUNTIFS formulas to count the number of matches a team has played, won, drawn and lost respectively. It’s important to note that a single COUNTIFS formula only allows for AND conditions. That means all criteria must be met for a successful count. As we have home and away matches to consider, we need to use two COUNTIFS statements in the same cell to add the counts together. The same concept applies to the SUMIFS function, which has been used for the columns that involve addition: F and A.

#excel #football #premier-league #soccer #dynamic-array

Gerhard Brink

1622622360

Data Validation in Excel

In this tutorial, let’s discuss what data validation is and how it can be implemented in MS-Excel. Let’s start!!!

What Is Data Validation in Excel?

Data Validation is one of the features in MS-Excel which helps in maintaining the consistency of the data in the spreadsheet. It controls the type of data that can enter in the data validated cells.

Data Validation in MS Excel

Now, let’s have a look at how data validation works and how to implement it in the worksheet:

To apply data validation for the cells, then follow the steps.

1: Choose to which all cells the validation of data should work.

2: Click on the DATA tab.

3: Go to the Data Validation option.

4: Choose the drop down option in it and click on the Data Validation.

Once you click on the data validation menu from the ribbon, a box appears with the list of data validation criteria, Input message and error message.

Let’s first understand, what is an input message and error message?

Once, the user clicks the cell, the input message appears in a small box near the cell.

If the user violates the condition of that particular cell, then the error message pops up in a box in the spreadsheet.

The advantage of both the messages is that the input and as well as the error message guide the user about how to fill the cells. Both the messages are customizable also.

Let us have a look at how to set it up and how it works with a sample

#ms excel tutorials #circle invalid data in excel #clear validation circles in excel #custom data validation in excel #data validation in excel #limitation in data validation in excel #setting up error message in excel #setting up input message in excel #troubleshooting formulas in excel #validate data in excel

Jones Brianna

1608787679

Fantasy Football App Development I Fantasy Football Software Development

https://www.mobiwebtech.com/fantasy-football-app-development/

As far as we know football is certainly the most popular and highest earning sport in the world. Investing in Fantasy Football is a great business opportunity. Millions of young sports enthusiasts are now hooked on fantasy sports websites.

#fantasy football app development #fantasy football software development #fantasy football website development #fantasy football app development services #create fantasy football app #hire fantasy football app developers

Building an Excel Football League Table — Traditional Methods vs Dynamic Arrays

Introduction

Before we start

Steps taken

Part A

What is GEEK

Buddha Community

How to Create Arrays in Python

In this tutorial, you'll know the basics of how to create arrays in Python using the array module. Learn how to use Python arrays. You'll see how to define them and the different methods commonly used for performing operations on them.

Table of Contents

What are Python Arrays?

What's the Difference between Python Lists and Python Arrays?

When to Use Python Arrays

How to Use Arrays in Python

How to Define Arrays in Python

How to Find the Length of an Array in Python

Array Indexing and How to Access Individual Items in an Array in Python

How to Search Through an Array in Python

How to Loop through an Array in Python

How to Slice an Array in Python

Methods For Performing Operations on Arrays in Python

How to Change the Value of an Item in an Array

How to Add a New Value to an Array

How to Remove a Value from an Array

Conclusion

Understanding Arrays in Python

Learn how to use Python arrays. Create arrays in Python using the array module. You'll see how to define them and the different methods commonly used for performing operations on them.

Table of Contents

What are Python Arrays?

What's the Difference between Python Lists and Python Arrays?

When to Use Python Arrays

How to Use Arrays in Python

How to Define Arrays in Python

How to Find the Length of an Array in Python

Array Indexing and How to Access Individual Items in an Array in Python

How to Search Through an Array in Python

How to Loop through an Array in Python

How to Slice an Array in Python

Methods For Performing Operations on Arrays in Python

How to Change the Value of an Item in an Array

How to Add a New Value to an Array

How to Remove a Value from an Array

Conclusion

Building an Excel Football League Table — Traditional Methods vs Dynamic Arrays

Introduction

Before we start

Steps taken

Part A

Data Validation in Excel

Data Validation in Excel

What Is Data Validation in Excel?

Data Validation in MS Excel

Fantasy Football App Development I Fantasy Football Software Development