How to add a total row for only one column in a data frame-CodePudding

I have a data frame named bill like this:

print(bill)
item  item_name  qty   price  total
1     hamburgers  2    10      20
2     fries       2    3       6
3     water       1    2       2

I want to add a row at the bottom with total bill, but only for total column like so:

item  item_name  qty  price  total
1     hamburgers  2   $ 10   $20
2     fries       2   $ 3    $6
3     water       1   $ 2    $2 
                  total bill $28

I do this:

bill.loc['total bill'] = bill['total'].sum()

but the total is added to each column like so:

item  item_name  qty  price  total
1     hamburgers  2   10     20
2     fries       2   3      6
3     water       1   2      2 
total bill     28  28   28  28

But I wish total bill to be only under total. I googled how to to this but I haven't found anything to add total under just one column. Further I will like to replace all values for bill['price'] and bill['total'] with $values, adding $sign

CodePudding user response：

You can use:

df.loc['total bill'] = df[['total']].sum().reindex(df.columns, fill_value='')

Output:

           item   item_name qty price  total
0             1  hamburgers   2    10     20
1             2       fries   2     3      6
2             3       water   1     2      2
total bill                                28

If you further want the $ sign:

cols = ['price', 'total']
df[cols] = df[cols].astype(str).radd('$').where(df[cols].ne(''), '')

Output:

           item   item_name qty price total
0             1  hamburgers   2   $10   $20
1             2       fries   2    $3    $6
2             3       water   1    $2    $2
total bill                              $28