I am creating a pdf report using report lab based on multiple pandas dataframes.
The tables that are displayed in the pdf need to have to columns in a specific order.
here is a sample dataframe:
df = pd.DataFrame({
'Counterparty': ['foo', 'fizz', 'fizz', 'fizz','fizz', 'foo'],
'Commodity': ['bar', 'bar', 'bar', 'bar','bar', 'ab cred'],
'DealType': ['Buy', 'Buy', 'Buy', 'Buy', 'Buy', 'Buy'],
'StartDate': ['07/01/2024', '09/01/2024', '10/01/2024', '11/01/2024', '12/01/2024', '01/01/2025'],
'FloatPrice': [18.73, 17.12, 17.76, 18.72, 19.47, 20.26],
'MTMValue':[10, 10, 10, 10, 10, 10]
})
out = pd.pivot_table(df, values = 'MTMValue', index='Counterparty', columns = 'Commodity', aggfunc='sum').reset_index().rename_axis(None, axis=1).fillna(0)
out['Cumulative Exposure'] = out[out.columns[1:]].sum(axis = 1)
Counterparty ab cred bar Cumulative Exposure
0 fizz 0.0 40.0 40.0
1 foo 10.0 10.0 20.0
I need this:
Counterparty bar ab cred Cumulative Exposure
0 fizz 40.0 0.0 40.0
1 foo 10.0 10.0 20.0
where ab cred will always be before cumulative exposure and after the last commodity, since there can be x amount of commodities added to the data (ab cred is always included as a commodity). More columns can also be added after cumulative exposure at any time.
One easy option would be to pop and reinsert ab cred so that it ends up last:
out = (pd.pivot_table(df, values='MTMValue', index='Counterparty',
columns='Commodity', aggfunc='sum')
.assign(**{'ab cred': lambda x: x.pop('ab cred')})
.reset_index().rename_axis(None, axis=1).fillna(0)
)
Another option would be to sort_index with a custom key:
out = (pd.pivot_table(df, values='MTMValue', index='Counterparty',
columns='Commodity', aggfunc='sum')
.sort_index(axis=1, key=lambda x: x == 'ab cred', kind='stable')
.reset_index().rename_axis(None, axis=1).fillna(0)
)
And, of course, if you know the explicit order that is desired you can always reindex (and also note that you can avoid the fillna and later assignment of the Cumulative Exposure column):
order = ['bar', 'ab cred']
out = (pd.pivot_table(df, values='MTMValue', index='Counterparty',
columns='Commodity', aggfunc='sum', fill_value=0)
.reindex(columns=order)
.assign(**{'Cumulative Exposure': lambda x: x.sum(axis=1)})
.reset_index().rename_axis(None, axis=1)
)
Output:
Counterparty bar ab cred Cumulative Exposure
0 fizz 40.0 0.0 40.0
1 foo 10.0 10.0 20.0
You could also try using groupby, sum, and unstack to produce the same effect as the pivot table. Not sure which is faster.
To set a specific order you could specify the columns in your desired order.
Here’s a short one-liner:
out = df.groupby(['Counterparty', 'Commodity'])['MTMValue'].sum().unstack().rename_axis(None, axis=1).fillna(0)[['bar', 'ab cred']].assign(**{'Cumulative Exposure': lambda x: x.sum(axis=1)}).reset_index()
Out:
Counterparty bar ab cred Cumulative Exposure
0 fizz 40.0 0.0 40.0
1 foo 10.0 10.0 20.0
2