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:
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:
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:
领英推荐
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!
Leiter Fachbereich Digitale Prozesse ? Kolumnist & Autor in diversen Fachzeitschriften der Baubranche ? Vermittler bei digitalen Themen
11 个月Do you think
Helping AEC Pros work efficiently with IFC
11 个月None of them are scary, but I prefer Python myself.
Scaling BIM Businesses & Building elite Brands | Strategy & LinkedIn | BIM Manager at WSP | Prof. Kotler Partner—Leading EOMM Edition for 6 countries of Ex-Yu Region | International Keynote Speaker
11 个月Bridging the Gap Between BIM and Programming! You transfored Excel nightmares into streamlined, efficient processes. Manual → automated = boosting project accuracy. P.S. Automation can redefine our approach to design. It is question how to achieve that.
Agtech | Digital Transformation | Business Development
11 个月One of my worst excel functions!! Thanks gor the tip ????????
Leiter Fachbereich Digitale Prozesse ? Kolumnist & Autor in diversen Fachzeitschriften der Baubranche ? Vermittler bei digitalen Themen
11 个月"Managers who know how to program are better managers" The statement by computer science pioneer and ETH Professor Emeritus Walter Gander is interesting: Interview in German, English subtitles 40 years of the Department of Computer Science ETH (16.8.2021) https://inf.ethz.ch/news-and-events/spotlights/infk-news-channel/2021/08/walter-gander-video-interview2.html