Getting to know Python in Excel

Defining arrays with the NumPy library

As stated, the preloaded Python library NumPy allows you to define arrays. We’ll now look at the arrays available in NumPy. In the following examples, you will enter some simple arrays and see how they interact. While arrays are often used for mathematical operations, for the first example, you will enter numbers and characters. The syntax is similar to the syntax for a nested list, but it is enclosed in the np.array function, where np is the short name, or alias, for the NumPy library.

For this example, you will enter the longest line of Python you have entered so far:

array1 = np.array([["Red","Orange","Yellow","Green","Blue","Indigo","Violet"],[1,2,3,4,5,6,7]])

How do you enter such a long line of code? You can do so in two ways: Either ensure that you do not press the Enter key between the sections of this code or break up the way the line is displayed. Let’s look at how to do this.

Adding line breaks in Python

Python has been designed to be easy to use and often offers multiple ways to accomplish the same task. For example, it gives you two ways to split a line:

• You can explicitly define where you want the line to be split.

• You can use implicit splits when Python expects the line to continue.

Figure 2-50 shows the array1 code mentioned earlier typed or copied into cell E21 as one long line.

The appearance of the code will vary depending on your display settings. A single line of Python code may be displayed over several lines in the Excel formula bar. The Python Editor shows the Python line numbers so you can see the Python code in each line (see Figure 2-51).

To control where the code is broken over the lines and improve readability, you can use a backslash (\). The following example deliberately uses excessive breaks to show how you can use them:

array1 = np.array([              [ "Red","Orange",                 "Yellow","Green",                        "Blue","Indigo",                        "Violet"                                                      ],              [1,2,3,4,5,6,7]])

Figure 2-52 shows how this code looks in the formula bar, which has been extended in this case to show all the code.

In the Python Editor, the code is now on different line numbers (see Figure 2-53).

You can also break the code into lines at logical points due to the way Python is programmed. For example, if you remove the backslash after the first square bracket, as shown in Figure 2-54, the code still evaluates successfully.

In fact, because all the continuation lines in this case occur within brackets, they can all be removed (see Figure 2-55). The same is true with parentheses and curly braces. The code is much easier to read when it is split this way.

Indentation is an important concept in Python code. The indentation in this example allows for readability in this single Python command to create an array. This example follows an important Python rule that says the first line cannot be indented. To see what happens if the first line is indented, look at Figure 2-56, which shows the Python Editor with the Python code in cell E21 indented on the first line. This produces an error and the following error message:

IntentationError: unexpected Indent (4036100023.py,line 1)

The error message indicates that there is an unexpected indent. You can easily rectify this problem by removing the space before the first line of code.

You will learn more about the indentation of multiple Python commands and its impact when we look at more complex Python code later in the chapter.

Viewing properties of arrays

Now that you have created your first array and arranged the Python code, you can investigate your creation. Use the Excel Value view to look at the data in cell E21 (see Figure 2-57).

The np.array function has created an array with the values in the first list in a row above the values in the second list. When you use the Python Object view, you can access the data type card to see the properties of the Python object (see Figure 2-58).

Note that the Python object in this example is called an ndarray. An ndarray is an N-dimensional array, a multidimensional array that typically contains items of the same size and type. We will look at operations with numeric ndarrays soon, but it is useful to first work with different data types to see how the lists are arranged. To see them side by side in columns, you can enter a new ndarray array2 that consists of lists of pairs in cell E25 (see Figure 2-59):

array2  = np.array([
             ["Red",1],
             ["Orange",2],
             ["Yellow",3],
             ["Green",4],
             ["Blue",5],
             ["Indigo",6],
             ["Violet",7]])

You can view cell E25 by using the Excel Value view to see the values of array2 in columns (see Figure 2-60).

You can view the data type card for the corresponding object to see that the data is now stored in a 7x2 ndarray (see Figure 2-61).

Trying out array examples

Now that you know how to organize data into ndarrays, you can try creating some numeric ndarrays and performing some simple operations.

In cell E33, enter the following Python code to create a simple ndarray in a columnar format:

num_array1 = np.array([1,2,3,4,5])

The resulting ndarray is the column vector shown in Figure 2-62.

Next, create another numeric ndarray in a row by entering the following code in cell G33:

num_array2 = np.array([[1,2,3,4,5]])

Figure 2-63 shows the row vector that results.

Now let’s use another NumPy function, np.dot(), to multiply the vectors. Vectors must be multiplied in the correct order. Let’s start by attempting the wrong order to see the error message produced. Checking the error messages in these simple examples will make it easier to spot what is going on in more complicated code.

Enter the following Python code in cell E39:

array_dot_result = np.dot(num_array1,num_array2)

As you can see in Figure 2-64, the Python Editor shows the following error message:

ValueError: shapes (5,) and (1,5) not aligned: 5 (dim 0) != 1(dim 0)

The shape of the 5x1 vector is a (5,) shape because Python sees it as a one-dimensional array. If you needed to multiply the arrays, you would need to reshape the arrays by using further NumPy functionality. The point of this example, though, is not to solve the error but to recognize it.

You can multiply these arrays if you change the order of the vectors and enter the following code:

array_dot_result = np.dot(num_array2,num_array1)

Figure 2-65 shows the results.

Now that the corresponding shapes (1,5) and (5,) are aligned, the calculation result is 55 (=(1x1)+(2x2)+(3x3)+(4x4)+(5x5)). The data type card for the result shows a 1x1 ndarray (see Figure 2-66).

As long as you follow alignment rules, you can multiply any arrays. For example, you can create a 3x3 ndarray in cell E41 by entering this code:

num_array3 = np.array([[1,2,3],[4,5,6],[7,8,9]])

Figure 2-67 shows the results.

You can create a 3x1 ndarray in cell I41 by entering the following code:

num_array4 = np.array([1,2,3])

Figure 2-68 shows the results.

You can now use np.dot() to multiply the arrays in cell K41:

array_dot_result2 = np.dot(num_array3,num_array4)

Figure 2-69 shows the results—another 3x1 array:

14 =(1x1)+(2x2)+(3x3)
32 =(1x4)+(2x5)+(3x6)
50 =(1x7)+(2x8)+(3x9)

The examples you have worked through in this section are simple, but they are scalable. You can use NumPy library functions to manipulate massive arrays of data. You will see more NumPy examples in Chapter 3, “Using Python libraries.”