CODEWITHSUNDEEP
pythonpandasdataframe
Meruemu
Meruemu

Reputation: 611

How to add '$' to my pandas dataframe values and use a column as index?

I have a table as follows:

           Names  Cider  Juice  Subtotal(Cider)  Subtotal(Juice)  Total
0        Richard   13.0    9.0            71.50            40.50  112.0
0         George    7.0   21.0            38.50            94.50  133.0
0           Paul    0.0   23.0             0.00           103.50  103.5
0           John   22.0    5.0           121.00            22.50  143.5
Total        sum   42.0   58.0           231.00           261.00  492.0
Average      avg   10.5   14.5            57.75            65.25  123.0

Values in [Subtotal(Cider) Subtotal(Juice) Total] are user input of float type.

How can I add a '$' to the values of these columns and use the Names column as my table index? I want a final table like this:

Names   Cider   Juice   Subtotal (Cider)   Subtotal (Juice) Total

Richard   13        9           $ 71.50            $ 40.50 $ 112.00
George     7       21           $ 38.50            $ 94.50 $ 133.00
Paul       0       23           $ 0.00            $ 103.50 $ 103.50
John      22        5           $ 121.00           $ 22.50 $ 143.50
Total     42       58           $ 231.00          $ 261.00 $ 492.00
Average 10.50   14.50           $ 57.75            $ 65.25 $ 123.00

My code runs like this:

import pandas as pd

df = pd.DataFrame(columns=["Names", "Cider", "Juice", "Subtotal(Cider)", "Subtotal(Juice)", "Total"])
people_ordered = input('How many people ordered? ')  # type str

'''Create the 4x3 table from user input'''
for i in range(int(people_ordered)):
    names = input("Enter the name of Person #" + str(i + 1) + " ")  # type str

    cider_orderred = float(input("How many orders of cider did {} have? ".format(names)))  # type str
    juice_orderred = float(input("How many orders of juice did {} have? ".format(names)))  # type str

    # store the values of the subtotals from user inputs
    cider_sub = 5.50 * cider_orderred  # type float
    juice_sub = 4.50 * juice_orderred  # type float
    total = cider_sub + juice_sub  # type float

    # create the 4x6 table
    df1 = pd.DataFrame(
        data=[[names, cider_orderred, juice_orderred, cider_sub, juice_sub, total]],
        columns=["Names", "Cider", "Juice", "Subtotal(Cider)", "Subtotal(Juice)", "Total"])
    # merge the the 4x3 into the 4x6 table

    df = pd.concat([df, df1], axis=0)
# add rows of "Total" and "Average"
df.loc['Total'] = df.sum()
df.loc['Average'] = df[:int(people_ordered)].mean()

# Set the row name to "Total" and "Average"
df.iloc[int(people_ordered),0] = 'Total'
df.iloc[int(people_ordered)+1,0] = 'Average'

# Adding "$" to the prices


df.index = range(len(df.index))
# Set the index according to 'Names'
df.set_index('Names')

print(df)

Upvotes: 0

Views: 76

Answers (2)

Haleemur Ali
Haleemur Ali

Reputation: 28303

Dataframes have a method to_string that accept column specific formatting functions

  1. set the index using set_index, but first fix the index for the last two values of df.Names

    df['Names'].iloc[-2:] = df.index[-2:]
df.set_index('Names', inplace=True)

  2. create the output string using the to_string & formatters

    cols = ['Subtotal(Cider)',  'Subtotal(Juice)',  'Total']
def f(x): return '$ {0:0.2f}'.format(x)
outstr = df.to_string(formatters={k: f for k in cols})
print(outstr)
# outputs:
         Cider  Juice Subtotal(Cider) Subtotal(Juice)    Total
Names
Richard   13.0    9.0         $ 71.50         $ 40.50 $ 112.00
George     7.0   21.0         $ 38.50         $ 94.50 $ 133.00
Paul       0.0   23.0          $ 0.00        $ 103.50 $ 103.50
John      22.0    5.0        $ 121.00         $ 22.50 $ 143.50
Total     42.0   58.0        $ 231.00        $ 261.00 $ 492.00
Average   10.5   14.5         $ 57.75         $ 65.25 $ 123.00

  3. if working in a jupyter notebook, you should use dataframe styling, which similarly allows passing of individual column formatting options. Note that this won't style your dataframe when displayed in the console.

    example: 

    df.style.format({k: f for k in cols})

Doing it via formatting functions has the following benefits:

  • you retain the original data types, only the output string is formatted, so you can continue to use your dataframe for more analysis.
  • you have very granular control on how each field is formatted.

Upvotes: 1

Shaido
Shaido

Reputation: 28367

To add a string, in this case'$', to the front of each value in the specified columns you can do the following, 

df['Subtotal(Cider)'] = '$' + df['Subtotal(Cider)'].astype(str)
df['Subtotal(Juice)'] = '$' + df['Subtotal(Juice)'].astype(str)
df['Total'] = '$' + df['Total'].astype(str)

For the second question, to set the Names column as index simply use

df.set_index('Names', inplace=True)

Note that this will change the names of the Total and Average columns that you set. A simple solution would be to add those two afterwards.

Upvotes: 1

Related Questions