Programming is scary...

For a long time, I believed programming was scary and nothing for me. Now, the Excel formulas scare me much more...

Because once you understand a few very basic principles, you can apply them to almost any problem, and it's not so complicated.

In a recent post, I introduced one of these fundamental principles used in many tools. It solved the problem of having to enter the same value manually more than once, which is inefficient and failure-prone.

The principle is called:

"looking up data with a key".

In Excel it's the formula =xlookup(cell with key; column to search for the key; column with return value). These are the steps:

1. we click in the cell we want to have the new value from the other table
2. we enter "=xlookup()"
3. We select the cell with the key value (The element name) and enter: ;
4. We select the column where the formula should look for this value and enter: ;
5. We select the column with the return values and close the brackets.

The same in Python

The moment more than one person works with Excel sheets, formula mistakes can easily creep in and they are very hard to find. Moreover, when we have to work with a lot of data, speed becomes an issue. Therefore, it can make sense to move to programming. As promised in the post, please find attached Python code to do the same as a "xlookup" in Excel.

import pandas as pd
df_model_data = pd.read_excel('Model data.xlsx')
df_room_classification = pd.read_excel('Mapping data.xlsx')
df_merge = pd.merge(df_model_data, df_room_classification, on='Name', how='left')
df_merge.to_excel('merged1.xlsx', index=False)        

So, the 6 lines of code Import two Excel files and merge them on the ‘name’ column.

Possible use case for this are:

• Having one table with unit prices another one with quantities from a project and by merging them calculating the costs for every modeled element.
• As I show in this example, having a list of rooms from a project? and another list with Room classifications. By merging the two lists, we assign the classifications to the project.
• Having data (e.g. structural, building physics, …)? on building element types like doors, windows, walls, columns,... And when merging this with the real project data. We just have to ensure that we use the right to name in the model.

The principle is always the same. We need one key value, and this key value links the two lists together. In the example it’s the room name in the column ‘Name’.

The setup

You can download all the files you need here:

https://github.com/simondilhas/Merge

The easiest way to execute the code is in a Jupyter Notebook (scroll down to the installer and click to download the desktop app) or with the Google Collab web app.

Make sure that all the files (the 2 Excel files and the Jupyter Notebook) are in the same folder, and open the file 'Mapping 2 Excels.ipynb'.

Understanding the code

When you look at the code in the notebook, you see lines that start with a #. This means it's a comment with further explanation.

Another good way to understand the code is to copy it into ChatGPT or Gemini and get a breakdown.

Creating the code and expanding

You can use the LLMs to understand, create and expand the code even more.

Try this prompt in Gemini or ChatGPT:

Create the python code to import two excel lists and merge them on the column 'name' while keeping all the entries of the first list.

I hope I have helped you overcome some of your fears about programming! Now, if you want to visualize the data in a graph, try to find the right prompt and post your results in the comments.

I'm looking forward to seeing the results!