Importing arrays from Excel to Python Pandas using Pyxll
I'm desperately trying to use Pyxll to write some excel function that takes a bunch of arrays, loads them in Python, converts them to pandas DataFrames, plays with the data a bit and then returns the final DataFrame.Now, while returning the DataFrame, I found pyxll examples , but whatever from the way i try i can't seem to convert the excel arrays i load in pandas DataFrames i can work with.
For example, I tried using the code below but no luck. Maybe if I had some way to find out what loading is in Python and what it looks like, I might have had a better chance of understanding how to manipulate data, but I don't know how to view the output in the Canopy output area.
Does anyone know a simple way to import data from excel into python, process it, then return it to excel without saving the file, load it in python, process the data, and overwrite the existing file?
@xl_func("string[] name, var[] day, string[] method, string[] currency, numpy_array amounts, date[] dates: dataframe")
def test(name, day, method, currency, amounts, dates):
df_name = DataFrame(name, columns = ['Name'])
df_method = DataFrame(method, columns = ['Method']).ix[1:]
df_currency = DataFrame(currency, columns = ['Currency']).ix[1:]
df = df_name.join(df_method).join(df_currency)
cols = ['Name', 'Currency', 'Method']
df = df[cols]
return DataFrame(dates)
source to share
Have a look at (my) xlwings library . It makes sending DataFrames back and forth as easy as:
>>> from xlwings import Workbook, Range
>>> import pandas as pd
>>> wb = Workbook() # Pass in the path of a file to work with an existing Workbook
>>> df = pd.DataFrame([[1., 2.], [3., 4.]], columns=['one', 'two'])
>>> Range('A1', index=False).value = df # send it over to Excel
>>> data = Range('A1').table.value # read it back
>>> pd.DataFrame(data[1:], columns=data[0])
one two
0 1 2
1 3 4
See in particular the docs on DataFrames and how to call it from VBA .
source to share
PyXLL can accept and return pandas frames using custom types.
Take a look at this example: https://github.com/pyxll/pyxll-examples/blob/master/pandas
Check the log file to see the result. For interactive playback in Python using the IPython prompt, use this example: https://github.com/pyxll/pyxll-examples/tree/master/ipython
It is much better to stick with functions rather than resort to named ranges.
You can use PyXLL to register keyboard shortcuts. See this example for a shortcut that automatically resizes the output of an array formula: https://github.com/pyxll/pyxll-examples/blob/master/shortcuts/resize_array_formula.py
If you need more help, please contact support for a quick response.
source to share