Author Topic: arrays in excel  (Read 679 times)

jelkhoury

  • Newbie
  • *
  • Posts: 14
  • Karma: +0/-0
    • View Profile
arrays in excel
« on: January 05, 2015, 08:25:31 AM »
Hi

I m new to PyXLL and just managed to use some of my python functions in XL
My question is when I m using arrays as inputs to my function they have to be horizontal(eg rows ) in excel or they fail.
Does anyone know if you can use 2 columns in XL as a 2d numpy array input  ? and if so how?

Thanks

Tony

  • Administrator
  • Hero Member
  • *****
  • Posts: 610
  • Karma: +14/-1
    • View Profile
Re: arrays in excel
« Reply #1 on: January 05, 2015, 10:48:34 AM »
Hi,

all arrays in Excel are passed to your Python functions as lists of lists, or 2d arrays if using numpy. When you pass a single column you will get a list of rows of length 1.

e.g. If you pass a column (1,2,3) in Python you will get a list of lists like [[1], [2], [3]]. If you use the numpy_array type then you will get this as a 2d numpy array. If you want them column-wise you will need to transpose the array.

There is some information in the docs about handling arrays:
https://www.pyxll.com/docs/index.html#using-arrays

If you're not sure about what data type your arguments are coming through as it can sometimes be helpful to print them and check the log file, just to check they are what you expected.

Best regards,
Tony

jelkhoury

  • Newbie
  • *
  • Posts: 14
  • Karma: +0/-0
    • View Profile
Re: arrays in excel
« Reply #2 on: January 08, 2015, 09:40:19 AM »
Thank you Tony
So if I understand well an excel range of 2 columns and 3 rows will be an array looking like this  : [[1,2],[3,4],[5,6]] for PYXLL
If I need it to be an array of 2 lists of length 3 I need to transpose the XL range : [[1,3,5],[2.4.6]]
If I have an interest rates curve looking like this [[date1,date2,date3],[rate1,rate2,rate3]]
and a function to interpolate between the curve points I should put [date.i] in a row and [rate.i] in a row below
if I have [datei] in a clolumn and [ratei] in a column next to it I need to transpose it


Thank You again

jelkhoury

  • Newbie
  • *
  • Posts: 14
  • Karma: +0/-0
    • View Profile
Re: arrays in excel
« Reply #3 on: January 08, 2015, 12:10:20 PM »
I am using scipy's interp1D for my interpolation function which uses list arrays as input
Instead of transposing my curves in xl I transposed the numpy_array(list array) in my python code before calling interp1d