Python Pandas rollup/sum of columns-CodePudding

I am using Pandas in Python3 and looking how to add a total column to the end of the table for each asset type (ETH and BTC).

positions = _pd.read_sql_query('''
        SELECT id,
               JSON_EXTRACT(data, '$.purchase_date') as purchase_date,
               JSON_EXTRACT(data, '$.asset') as asset,
               JSON_EXTRACT(data, '$.size') as size,
               JSON_EXTRACT(data, '$.filled_price') as filled_price,
               JSON_EXTRACT(data, '$.exchange') as exchange,
               ROUND(JSON_EXTRACT(data, '$.size') * JSON_EXTRACT(data, '$.filled_price'), 2) as cost
          FROM positions
      ORDER BY purchase_date ASC
    ''', _conn)

Which output is like:

 ---- --------------------- ------- ------------ -------------- ------------------- --------- 
| id |    purchase_date    | asset |    size    | filled_price |     exchange      |  cost   |
 ---- --------------------- ------- ------------ -------------- ------------------- --------- 
| 1  | 2021-02-20T17:52:49 |  ETH  | 2.05       |   1921.91    |   Coinbase Pro    |  3939.92|
| 2  | 2021-02-22T22:55:15 |  ETH  | 3.794      |    1620.0    |   Coinbase Pro    | 6146.28 |
| 3  | 2021-04-17T22:27:45 |  BTC  | 0.9549     |   47356.46   |      BlockFi      | 45220.68|

I like to have totals at the end of the table like:

Total ETH: 5.844
Total BTC: 0.9549

Is this possible using native Pandas?

CodePudding user response：

like this?

 positions['TOTAL'] = positions.groupby('asset')['size'].transform('sum')

CodePudding user response：

Here is what worked.

    asset_totals = _pd.DataFrame(positions.groupby('asset')
                                .agg(asset=('asset', min),
                                     total_size=('size', sum),
                                     total_cost=('cost', sum))
                                .sort_values('total_size', ascending=False))

Note, using asset=('asset', min) is sort of "hacky" as we taking the min of asset type.