Explore

Programming Excel with PYTHON

Dr. Pythonic Excel

⁠

https://www.linkedin.com/pulse/transform-data-compelling-narratives-unleashing-power-peter-sigurdson-howbe/?authuser=1⁠

⁠

https://www.linkedin.com/pulse/powering-hr-insights-leveraging-powerbi-python-r-data-sigurdson-bekec?authuser=1⁠

⁠

https://coda.io/@peter-sigurdson/python-workbook-for-beginners-from-first-principles?authuser=1⁠

⁠

Based on the search results, here are some of the top companies in Toronto that are currently hiring for new graduates in various roles:

Python Developer Roles: Skatescribe Corp., Symcor Inc., Deloitte, Capital One, and Mackenzie Financial are some of the companies hiring for Python Developer roles

1⁠

⁠

5⁠

⁠

14⁠

⁠

20⁠

Data Analyst Roles: There are numerous opportunities for Data Analyst roles in companies like TD, RBC, Scotiabank, CIBC, BMO Financial Group, TELUS, and Deloitte

3⁠

⁠

4⁠

⁠

6⁠

⁠

12⁠

⁠

15⁠

⁠

18⁠

⁠

21⁠

⁠

24⁠

Business Analyst Roles: Companies like TD, RBC, Scotiabank, CIBC, BMO Financial Group, TELUS, Deloitte, Tata Consultancy Services, Capgemini, and Capital One are hiring for Business Analyst roles

3⁠

⁠

7⁠

⁠

10⁠

⁠

13⁠

⁠

16⁠

⁠

19⁠

⁠

22⁠

Please note that the job market is dynamic and the availability of roles may change. It's recommended to regularly check job portals and company websites for the most current opportunities. Also, consider tailoring your resume and cover letter to each specific role to increase your chances of getting an interview.

⁠

Anaconda is a distribution of Python and other software packages. It's designed to make it easy to install, distribute, and manage Python packages.

The name "Anaconda" comes from the Greek mythological snake who swallowed everything whole, which symbolizes the all-encompassing nature of Python in Anaconda. Python, on the other hand, is a high-level programming language that is interpreted and object-oriented.

It is widely used for scientific computing, web development, artificial intelligence, and more. Anaconda lends its popularity for Python scripting and data science tools.

Lecture 1: Introduction to Python and Excel Automation with Anaconda and Jupyter Notebook

Welcome to today's session on Pythonic Excel.

In this lab workbook, we'll cover the first steps in our Journey to become Pythonic Excel Data Engineers:

What is Python?

Installing Anaconda Python distribution.

Starting and using Jupyter Notebook.

Importing libraries to connect to Excel.

Making the connection to an Excel Workbook.

Generating an Excel worksheet with random numbers and applying basic statistics.

What is Python?

Python is a high-level, interpreted programming language known for its simplicity and readability. It's a versatile language used in various domains including data analysis, artificial intelligence, machine learning, web development, and scripting, among others. Python's extensive array of libraries makes it particularly valuable for scientific computing and business analytics.

Installing Anaconda Python distribution

Anaconda is an open-source distribution of Python (and R) for scientific computing and data science. It simplifies package management and deployment. To install Anaconda, follow these steps:

Visit the official Anaconda website:

Anaconda.com⁠

⁠

Download the Anaconda installer for your specific OS.

Follow the installation instructions provided in the installer.

Once installed, verify the installation by opening your terminal (or command prompt) and typing conda list. You should see a list of installed packages.

Starting and Using Jupyter Notebook

Jupyter Notebook is the tool we will do our PYTHON Excel programming in.

It is an interactive computing environment that enables users to create notebook documents containing live code, equations, visualizations, and narrative text.

To start Jupyter Notebook:

jupyter notebook

⁠

Open your terminal or Anaconda Prompt.

Type jupyter notebook and press Enter.

Your default web browser will open with the Jupyter dashboard.

Create a new notebook by clicking on 'New' > 'Python 3'.

Importing Libraries to Connect to Excel

To connect Python with Excel, we will use two libraries: openpyxl, for handling Excel files, and pandas, for data manipulation and analysis.

To import these libraries, you'll first need to install them using pip or conda. In your Jupyter notebook, type:

Upgrade to

Python SDK v1.2⁠

with pip install --upgrade openai

!pip install openpyxl pandas

Then import them in your notebook:

Upgrade to

Python SDK v1.2⁠

with pip install --upgrade openai

import openpyxl

import pandas as pd

Making the Connection to an Excel Workbook

To work with an Excel workbook in Python, we need to load it into our program. At this point, you should have an Excel file to work with. If not, create a simple .xlsx file and save it in a known directory. Here's how to open and read an Excel file using openpyxl:

Upgrade to

Python SDK v1.2⁠

with pip install --upgrade openai

from openpyxl import load_workbook

# Replace 'path_to_file.xlsx' with the path to your own Excel file

workbook = load_workbook('path_to_file.xlsx')

sheet = workbook.active

Excel Worksheet Generation with Random Numbers

Let's create an Excel worksheet with 5 columns of 10 random numbers each and apply named ranges and statistical calculations.

First, we'll generate the random numbers:

Upgrade to

Python SDK v1.2⁠

with pip install --upgrade openai

import numpy as np

# Setting the seed for reproducibility

np.random.seed(0)

# Generate a DataFrame with 5 columns and 10 rows of random numbers

df = pd.DataFrame(np.random.rand(10, 5), columns=['A', 'B', 'C', 'D', 'E'])

Now, we'll output this DataFrame to a new Excel file:

Upgrade to

Python SDK v1.2⁠

with pip install --upgrade openai

# Write the DataFrame to a new Excel file

df.to_excel('random_numbers.xlsx', index=False)

Next, to create named ranges and apply statistical functions, we first need to load the workbook we just created, then we can define named ranges and calculate the statistics:

Upgrade to

Python SDK v1.2⁠

with pip install --upgrade openai

# Reopen the workbook to create named ranges and calculations

workbook = load_workbook('random_numbers.xlsx')

sheet = workbook.active

# Create named ranges for each column in Excel

for column in 'ABCDE':

sheet[f'{column}11'] = f"=AVERAGE({column}1:{column}10)"

sheet[f'{column}12'] = f"=MIN({column}1:{column}10)"

sheet[f'{column}13'] = f"=MAX({column}1:{column}10)"

sheet[f'{column}14'] = f"=SUM({column}1:{column}10)"

sheet[f'{column}15'] = f"=COUNT({column}1:{column}10)"

sheet[f'{column}16'] = f"=MEDIAN({column}1:{column}10)"

# Save the workbook with the named ranges and calculations

workbook.save('random_numbers.xlsx')

In the Excel file random_numbers.xlsx, you'll now have 5 columns labeled A, B, C, D, and E with random numbers. Row 11 shows the average of each column, row 12 the minimum, row 13 the maximum, row 14 the sum, row 15 the count, and row 16 the median.

Remember to always save your work in Jupyter by clicking the 'save' icon or by pressing Ctrl+S (Cmd+S on a Mac).

With that, you've completed the first lab workbook. We've gone over the basics of what Python is and how to use it to generate and process data in Excel.

Next time, we will explore more advanced data manipulation and visualization techniques using Python with Excel. Happy coding!

Problem Determination:

Error Message:

Traceback (most recent call last): File "C:\anaconda3\Scripts\jupyter-notebook-script.py", line 6, in from notebook.notebookapp import main ModuleNotFoundError: No module named 'notebook.notebookapp'

Dr. Pythonic Excel:

Start with pip install --upgrade --force-reinstall notebook

If it is still not starting: Proceed down the Problem Determination Stack.

(You can email a screen shot of the error message to me for assistance):

The error message you are encountering suggests that the Jupyter Notebook module is not installed properly on your system or it's not correctly recognized by your Python environment.

This can happen due to a variety of reasons including a corrupted installation, environment path issues, or incompatible Python versions.

Here are the steps to troubleshoot and resolve the issue:

Verify Installation: Ensure that Jupyter is indeed installed in your Anaconda distribution.

conda list jupyter

Check Environment: Make sure that you're working within the correct conda environment where Jupyter is installed. If you're using a specific environment, activate it:

conda activate your_environment_name

Replace your_environment_name with the name of your conda environment.

Update Packages: If Jupyter is installed, try updating the Jupyter Notebook and its dependencies:

conda update --all

Reinstall Jupyter: If updating doesn’t work, you can try to reinstall Jupyter:

conda uninstall jupyter

conda install jupyter

PATH Variable: Sometimes the path to the scripts is not properly set; verify that the PATH variable includes the path to your Anaconda scripts directory.

Check for Jupyter Configuration: There might be an issue with the Jupyter configuration. You can find Jupyter’s configuration directory by running:

jupyter --config-dir

Navigate to that directory and inspect the configuration files for any anomalies.

Install with pip: If none of the above works, you might try pip installation. Using pip within Anaconda is not always recommended because you should typically use conda for all installations, but it can be a last resort:

pip install --upgrade jupyter

Ensure you have pip installed and updated, and preferably, use pip within a virtual environment to avoid conflicts with other packages.

Anaconda Navigator: You could also try to launch Jupyter Notebook through Anaconda Navigator if you prefer a GUI.

Remember that it's crucial to run these commands in an environment where you have administrative privileges (e.g., 'Run as administrator' on Windows).

If you encounter ongoing difficulties, it may also be worth looking at logs, if any, created by Jupyter, or increasing the verbosity of the output to get a clearer picture of the problem. If you’re running Jupyter Notebook within a business or institutional network, there might be additional security restrictions or proxy configurations to consider.

The error message you're encountering indicates that Python cannot find the notebook.notebookapp module, which is essential for running the Jupyter Notebook.

This could be due to several reasons such as a faulty Jupyter installation, issues with the PATH environment variable, or a broken Anaconda environment.

Here is a step-by-step approach to troubleshoot and resolve this issue:

Check Installation: Ensure that Jupyter has been properly installed in your Anaconda environment. You might want to install or reinstall it using the following command in your Anaconda Prompt:

conda install jupyter

If you prefer to use pip, you may reinstall Jupyter Notebook using pip:

pip install --upgrade --force-reinstall notebook

These commands should also ensure that all dependencies including the notebook.notebookapp are installed.

Activate Anaconda Environment: Make sure you have activated the Anaconda environment where Jupyter is installed. You can activate the default (base) environment with this command:

conda activate

If you have a specific environment where Jupyter is installed, activate it using conda activate myenv where myenv is your environment's name.

Check PATH Variable: Ensure that the Anaconda directory is in your PATH environment variable. It allows you to run Anaconda’s software from any command prompt or terminal session.

Check for Jupyter Configuration: Sometimes there might be a configuration issue with Jupyter itself. You can check the Jupyter configuration by using the following command:

jupyter notebook --debug

This command may provide additional debug information which could lead to resolving the issue.

Use Anaconda Navigator: If you are still encountering issues, try launching Jupyter Notebook from Anaconda Navigator, a graphical user interface that comes with Anaconda.

If you follow these steps and the problem persists, there might be a deeper issue with your Anaconda installation. In that case, you may need to uninstall and then reinstall Anaconda, ensuring that you download the latest version from the official Anaconda website. Remember to back up your environments and important data before uninstalling.

Want to print your doc?
This is not the way.

Try clicking the ⋯ next to your doc name or using a keyboard shortcut (

CtrlP

) instead.