Bust Common Myths About Java Programming
Loading
Tensile Strength and Capacity Control of the W-Shape Sections According to AISC 360-16
Loading
Concrete Cylinder Strength Vs Cube Strength
Loading
Earthquake Design Action Calculation
EM Wave Propagation Calculator
İKSA YAPILARI PROJELENDİRME HİZMET BEDELİ (2024)
section properties with units
GEOTEKNİK RAPOR (EK-B) ASGARİ HİZMET BEDELİ (2024)
Forward Kinematics of Robotic Arm with 6 Degrees of Freedom
ZEMİN İYİLEŞTİRME/DERİN TEMEL PROJELENDİRME ASGARİ HİZMET BEDELİ (2024)
İKSA YAPILARI PROJELENDİRME HİZMET BEDELİ (2023)
Loading
Dezi et. al (2010)
🤾
Projectile motion
In today's data-driven world, the ability to efficiently manipulate and analyse data is crucial for any engineering project. Spreadsheets are commonly used for data entry and basic analysis due to their user-friendly interface, while Python is a powerful tool for more complex data processing and automation.
Combining the capabilities of spreadsheets with the flexibility of Python scripting can significantly enhance your workflow. In this article, we'll explore how to connect spreadsheets and Python scripts in CalcTree to streamline your engineering calculation workflow, using the example of our Timber Nailed Joint Designer to AS 1720.1.
Why Connect Spreadsheets and Python?
Spreadsheets and Python each have their own strengths as well as weaknesses.
Spreadsheets, are widely used in various industries and are intuitive for data entry and visualisation. You can easily organise data in the "visual" tabulated interface, from which common data manipulation can be performed.
Python on the other had, has several benefits:
- Third-party libraries; these are perhaps the biggest strength of programming languages - there is a wealth of awesome libraries (pre-written code) that we can use to speed up our scripting and increase drastically expand the functionality of code. Most libraries are open-source and free to use in a commercial context. In the context of Python, there are some libraries that enable more efficient data manipulation likes Pandas while some support specific engineering applications like OpenSeesPy.
- Scalability, big datasets are painfully slow to process and use in spreadsheets. Any programming language is simply a way to write instructions for your computer to perform similar processes. In this way, it unlocks the speed and efficiency of your computer making it much faster to process and manipulate data.
- Flexibility, Python offers more functionality for data analysis than spreadsheets. For example, 'for' and 'while' cycles are useful functions to replicate formulas under certain conditions. Excel does not support an equivalent function, and so you have to execute the formula each time in a new cell.
By connecting spreadsheets and Python scripts, you can leverage the strengths of both platforms.
How to Connect Spreadsheets and Python
Outside of CalcTree, there are several ways to connect spreadsheets and Python scripts. Here are three common methods:
1: Using Python Libraries
Python libraries such as "pandas", "openpyxl", and "xlrd" provide functionality to read from and write to Excel files directly within Python scripts.
For example, using the "pandas" Python library to interact with an Excel file:
2: Using APIs
Some spreadsheet applications, such as Google Sheets and Microsoft Excel, offer APIs that allow you to interact with spreadsheet data programmatically. You can use these APIs to read from and write to spreadsheets using Python scripts.
For example, using the "gspread" Python library to interact with Google Sheets:
3: Using Python Scripts as Macros
Some spreadsheet applications allow you to run Python scripts as macros within the spreadsheet environment.
For example, using the "xlwings" Python library to interact with Excel:
Connecting Spreadsheets and Python with CalcTree
In this example we will compute the modification factor
of a timber member in accordance with AS 1720.1. We will define input parameters in Excel using validation lists and execute the
calculation using Python. Below outlines the steps to connect spreadsheets and python with CalcTree.
Step 1: Add a Spreadsheet Integration to CalcTree
In CalcTree, add a "Spreadsheet" source using the Integrations panel on the right-hand side of your page.
Step 2: Map the Input Parameters into CalcTree
Click on the spreadsheet source in the Integrations panel to open up the spreadsheet 'source viewer', which is a view of the spreadsheet that appears at the bottom of your page. In the source viewer, map the required inputs to your CalcTree page by clicking on the "+" in each cell.
Alternatively you can map whole tables onto your page.
For our example, I have mapped the following table with input parameters onto the page:
Step 3: Add Python Integration to CalcTree
In CalcTree, add a "Code" source using the Integrations panel on the right-hand side of your page.
Step 4: Define Input Parameters in Python
Click on the Code source in the Integrations panel to open up the Python 'source viewer', which is a view of the Python script that appears at the bottom of your page. Use the "_page_" prefix to match input parameters in Python with CalcTree’s page parameters. Assign these to new variables in your script for clarity.
Step 5: Define the logic in Python to calculate
Step 6: Add Output Python Parameters to CalcTree Page
Your output parameters will appear in the integrations panel on the right-hand side of your page, in the page parameter list under Code source. To add these to your page, you can simply click and drag them to the page.
This will display the results of your Python code on the page for the user to see.
For our example, I have brought the
output parameter onto the page:
k13
:0.60Your calculation is ready! View the complete timber nail calculation at the live link here.
Conclusion
Spreadsheets are intuitive for data entry and visualisation, but they can be limited when it comes to complex analysis and automation. Python, on the other hand, provides extensive libraries for data manipulation and can deal much more efficiently with large datasets. By connecting spreadsheets and Python scripts, you can leverage the strengths of both platforms. CalcTree provides a user-friendly no-code cloud environment to connect the two. The result, is a powerful web-based app!