Início / coding / Perguntas / 78999116
Accepted
iBeMeltin
Asked: 2024-09-18 23:43:26 +0800 CST

Crie uma tabela dinâmica e adicione colunas adicionais de outro dataframe

  • 772

Dados dois dataframes com formato idêntico:

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

Eu fiz isso até agora:

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

comprar Como posso girar estes para criar um visual como este:

Counterparty  Bar  Buzz  Total  col1 col2
foo           40    0      40    39    1
fizz          20    20     40    39    1

de onde col1vem df2e qual col2é a diferença entre o Totalecol1

amostra:

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)
python

2 respostas

  1. Best Answer

    Totalcoluna pode ser gerada somando todas as colunas presentes, exceto a primeira. Deve ser feito primeiro antes de adicionar outras colunas.

    out['Total'] = out[out.columns[1:]].sum(axis = 1)

    col1a coluna é feita com um groupby, mesclar Counterpartye então renomear a coluna:

    out = out.merge(df2.groupby('Counterparty')[['Value']].sum(), on = 'Counterparty')
out = out.rename(columns = {'Value': 'col1'})

    col2é trivial:

    out['col2'] = out['Total'] - out['col1']
    • 1

  2. Você pode mesclar a soma agrupada df2 à sua tabela dinâmica a partir de df1 e, em seguida, usar atribuir para adicionar a coluna ausente.

    df1.pivot_table(index='Counterparty', columns='Product', values='Value', aggfunc='sum').fillna(0).astype(int).assign(Total=lambda x: x.sum(axis=1)).merge(df2.groupby('Counterparty')['Value'].sum().rename('col1'), left_index=True, right_index=True).rename_axis(None, axis=1).reset_index().assign(col2=lambda x: x['Total']-x['col1'])


    Counterparty    bar buzz    Total   col1    col2
0           fizz     20   20       40     39       1
1            foo     40    0       40     39       1
    • 0

relate perguntas