The previous post had a link to Cyrille Rossant which is worth a closer look. The blog has many detailed posts on scientific applications of Python, as well as links to Galry: a high performance interactive visualization package in Python and his IPython mini-book.
Spreadsheets 4 Simulation is a new blog focussing on engineering applications of Excel and Google Sheets, which has some impressive looking applications and animations, including:
This post is based on exercises published by Cyrille Rossant in his book “Learning IPython for Interactive Computing and Data Visualization”. Cyril also has a blog well worth looking at: http://cyrille.rossant.net/blog/ (Thanks to Alfred Vachris and Boris Vishnevsky for the links).
As has been noted here before, pure Python can be very slow when looping through large blocks of data, but there are simple ways to speed things up dramatically. This is illustrated by alternative techniques to search a list of 10 million 2D coordinates to find the point closest to a specified location.
The screenshot below shows Python code to loop through the coordinates in the list “positions” and return the index of the one closest to “position”.
The positions list is generated with the random function, and the closest function is then run with a position of (.5, .5), as shown below:
The Python routine takes 7.1 s to loop through the 10 million rows of the positions list.
To speed things up, using Numpy, the procedure is:
The total time to execute this procedure on the 10 million rows of the positions array was 218 ms, better than 30 times faster than the original code.
The message is, whenever working with large arrays, use Numpy functions that operate on the entire array with efficient C based code, rather than looping through the arrays with interpreted Python code.
This is what Wikipedia says of the music played by The Pentangle:
Pentangle are usually characterised as a folk-rock band. Danny Thompson preferred to describe the group as a “folk-jazz band.”[25] John Renbourn also rejected the “folk-rock” categorisation, saying, “One of the worst things you can do to a folk song is inflict a rock beat on it. . . Most of the old songs that I have heard have their own internal rhythm. When we worked on those in the group, Terry Cox worked out his percussion patterns to match the patterns in the songs exactly. In that respect he was the opposite of a folk-rock drummer.”[26] This approach to songs led to the use of unusual time signatures: “Market Song” from Sweet Child moves from 7/4 to 11/4 and 4/4 time,[27] and “Light Flight” from Basket of Light includes sections in 5/8, 7/8 and 6/4.[28]
Writing in The Times,Henry Raynor struggled to characterise their music: “It is not a pop group, not a folk group and not a jazz group, but what it attempts is music which is a synthesis of all these and other styles as well as interesting experiments in each of them individually.”
There is no better example of their unique style than their version of “Jack Orion” on their 1970 album “Cruel Sister”, and I have just discovered an uninterrupted recording of the full 18 minute song on You Tube:
Starting from 2009 I have posted a series on frame analysis using Excel, starting from a simple “on-sheet” solution and working through to applications able to solve large 2D or 3D problems. To follow the analysis process it is best to read these posts in sequence, but the links between them are hidden in the comments, and in one case the link seems to be missing, so here is a list of all the frame analysis related posts, in date order:
This covers all the posts here directly related to the frame analysis spreadsheets. There are also many related posts covering analysis of continuous beams, and on-going work on linking the frame analysis spreadsheets to Python based solver routines, and these may be found by selecting Frame Analysis in the Categories drop-down box in the top-right corner.
Excel does not have a built-in function to find the maximum absolute value of a range, perhaps because the Max() and Abs() functions can be combined in an array function:
This solution has a number of drawbacks however:
For all these reasons I decided to write a MaxAbs function in VBA, that can be called either from the worksheet, or from another VBA routine. Here is the code, Version 1:
Function MaxAbs(Dataa As Variant) As Double
Dim MaxVal As Double, Val As Variant
If TypeName(Dataa) = "Range" Then Dataa = Dataa.Value2
For Each Val In Dataa
If Abs(Val) > MaxVal Then MaxVal = Abs(Val)
Next Val
MaxAbs = MaxVal
End Function
Having done that, I wondered if calling the built-in Max function might work better, particularly for big data ranges. My first effort was:
Function MaxAbs2(Dataa As Variant) As Double
Dim MaxVal1 As Double, MaxVal2 As Double
If TypeName(Dataa) = "Range" Then Dataa = Dataa.Value2
MaxVal1 = WorksheetFunction.Max(Dataa)
MaxVal2 = -WorksheetFunction.Min(Dataa)
If MaxVal1 > MaxVal2 Then MaxAbs2 = MaxVal1 Else MaxAbs2 = MaxVal2
End Function
This proved to be slower than the first version, even for very big data ranges, but working with ranges rather than variant arrays:
Function MaxAbsR(Dataa As Range) As Double
Dim MaxVal1 As Double, MaxVal2 As Double
MaxVal1 = WorksheetFunction.Max(Dataa)
MaxVal2 = -WorksheetFunction.Min(Dataa)
If MaxVal1 > MaxVal2 Then MaxAbsR = MaxVal1 Else MaxAbsR = MaxVal2
End Function
made the code faster than the original version for anything more than about 15 rows. The drawback with this version is that if you are working with double or variant arrays in VBA (which I usually am), these would need to be converted to range objects first, so I ended up with the two versions:
The first function can also be easily adapted to provide the minimum absolute value:
Function MinAbs(Dataa As Variant) As Double
Dim MinVal As Double, Val As Variant
If TypeName(Dataa) = "Range" Then Dataa = Dataa.Value2
MinVal = 1E+308
For Each Val In Dataa
If Abs(Val) < MinVal Then MinVal = Abs(Val)
Next Val
MinAbs = MinVal
End Function
Other than changing max to min, and > to <, the only difference is that MinVal is set to a very large number before starting the loop; otherwise it would always return zero.
Newton Excel Bach, not (just) an Excel Blog 