[Python] Loading a Pandas Dataframe into Excel table (and subsequent pivot table/chart)

Running Python in excel, how can a dataframe be used as the data for an excel table, to then make pivot charts with the data.

For example, in excel, cell A1 contains a pd.DataFrame: pd.DataFrame(np.random.randint(0,3,size=(3,3)),columns = list('ABC'),index = [1,2,3]). This dataframe is 4x4 (including the index which is output in an excel column), how can this be loaded into a excel table with 4 columns, 1 header row, and 3 rows? Ability to have a variable number of rows and headers would be nice also.

This can easily be viewed as a range in excel by changing the cell output to 'Excel Value' from 'Python Object', but making that range a table fails due to spill errors and so can't go into a pivot chart.

This could avoided easily by plotting using python, but that looses the excel chart familiarity and portability to other MS programs which is needed (unless anyone knows of a way to create excel pivot charts that keep MS program functionality in python?)

Continue reading...