I am using a pandas pivot table to generate a table view of text data (approx 5000 elements across multiple classes). I am using a lambda function to format the text in the cell, with one value per row. Code below:
{pt = pd.pivot_table(df,index = [‘field1′,’field2’],columns = [‘field3′,’field4′,’field5’],values = [‘value’],aggfunc = lambda x:’n’.join(x))
pt.to_excel(r’outputlocationhere.xlsx}
The issue I am running into is that some cells in the output pivot table have so many values in them that I can’t display them all in Excel (Excel has a maximum row height of 409.5). Due to the nature of the data, I can’t use any more subclasses in the pivot table to subdivide the data further.
I am trying to work out how I could format the data within the PT cell to work around the limit I am hitting in Excel. One idea I had was to use the lambda function to format the output into two columns within the individual cell. I am really struggling out how to make this work though.
I have a lambda function that works at command line to do what I need but I cannot get this to work within the pd.pivot_table method. See below (very crude):
{>>> f = lambda x: ”.join([“%st”%i if x.index(i)%2 == 0 else “%sn”%i for i in x])
print f([‘a’,’b’,’c’,’d’])
a b
c d
}
Does anyone have any suggestions on how I could use the lambda function to custom format the cell content within the output pivot table?
Ant is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.