Создайте сводную таблицу и добавьте дополнительные столбцы из другого фрейма данных. ⇐ Python

[Anonymous]

Дано два идентично отформатированных фрейма данных:
df1
Counterparty Product Deal Date Value
foo bar Buy 01/01/24 10.00
foo bar Buy 01/01/24 10.00
foo bar Sell 01/01/24 10.00
foo bar Sell 01/01/24 10.00
fizz bar Buy 01/01/24 10.00
fizz bar Buy 01/01/24 10.00
fizz buzz Sell 01/01/24 10.00
fizz buzz Sell 01/01/24 10.00

df2
Counterparty Product Deal Date Value
foo bar Buy 01/01/24 11.00
foo bar Buy 01/01/24 09.00
foo bar Sell 01/01/24 09.00
foo bar Sell 01/01/24 10.00
fizz bar Buy 01/01/24 12.00
fizz bar Buy 01/01/24 08.00
fizz buzz Sell 01/01/24 09.00
fizz buzz Sell 01/01/24 10.00

Я уже сделал это:
out = pd.pivot_table(df1, values = 'Value', index='Counterparty', columns = 'Product', aggfunc='sum').reset_index().rename_axis(None, axis=1)
out = out.fillna(0)

Counterparty bar buzz
0 fizz 20.0 20.0
1 foo 40.0 0.0

купить Как можно повернуть их, чтобы создать такой визуальный элемент:
Counterparty Bar Buzz Total col1 col2
foo 40 0 40 39 1
fizz 20 20 40 39 1

где col1 берется из df2, а col2 — это разница между итогом и col1
пример:
df1 = pd.DataFrame({
"Counterparty": ["foo", "foo", "foo", "foo", "fizz", "fizz", "fizz", "fizz"],
"Product": ["bar", "bar", "bar", "bar", "bar", "bar", "buzz", "buzz"],
"Deal": ["Buy","Buy", "Sell", "Sell", "Buy", "Buy", "Sell", "Sell"],
"Date": ["01/01/24", "01/01/24", "01/01/24", "01/01/24", "01/01/24", "01/01/24", "01/01/24", "01/01/24"],
"Value": [10, 10, 10, 10, 10, 10, 10, 10]
})

df2 = pd.DataFrame({
"Counterparty": ["foo", "foo", "foo", "foo", "fizz", "fizz", "fizz", "fizz"],
"Product": ["bar", "bar", "bar", "bar", "bar", "bar", "buzz", "buzz"],
"Deal": ["Buy","Buy", "Sell", "Sell", "Buy", "Buy", "Sell", "Sell"],
"Date": ["01/01/24", "01/01/24", "01/01/24", "01/01/24", "01/01/24", "01/01/24", "01/01/24", "01/01/24"],
"Value": [11, 9, 9, 10, 12, 8, 9, 10]
})

out = pd.pivot_table(df1, values = 'Value', index='Counterparty', columns = 'Product', aggfunc='sum').reset_index().rename_axis(None, axis=1)
out = out.fillna(0)


Подробнее здесь: https://stackoverflow.com/questions/789 ... -dataframe