Create a function that strips the $, converts to float and apply to pandas groupby

user_11524416 0
Viewed 145

I created a function that strips the '$' in the number and converts it to float then add the total sum in the column. And when I tried calling that function to the groupby, I am getting an error. Here's the code:

def total_sales():
    return df['Total sales USD'].str.strip('$').astype(float).sum()

df_order_sales_date = df.groupby(['column1','column2']).apply(total_sales)

Im getting an error that says 'TypeError: total_sales() takes 0 positional arguments but 1 was given'

What should I do? Is it possible to create a function that strips the $, converts the string to float and add the total then apply it to the columns in groupby? Thanks for the help

pandas-groupby pandas function python
1 Answers

You can run the following to sum currency/float data formatted as a string and then adjust the formatting back to:

  1. String
  2. Float

I have commented out option #1, which will return a string, as I suspect the desired result might be option #2 (a float but formatted with a $ similar to how you would format a float in Excel). I have also shown what the output looks like if you run option #1 (a string). I have included print(df_order_sales_date.info()) to show you how although aesthetically the result is the exact same, the data type and the underlying value are actually different`:

import pandas as pd
df = pd.DataFrame({'column1': {0: 'A', 1: 'A', 2: 'B', 3: 'B'},
 'column2': {0: 'a', 1: 'b', 2: 'a', 3: 'a'},
 'Total sales USD': {0: '$10.05', 1: '$20.45', 2: '$5.50', 3: '$15.25'}})
df['Total sales USD'] = df['Total sales USD'].str.strip('$').astype(float)
df_order_sales_date = df.groupby(['column1','column2'])['Total sales USD'].sum().reset_index()
# df_order_sales_date['Total sales USD'] = '$' + df_order_sales_date['Total sales USD'].astype(str) #option1
pd.options.display.float_format = '${:,.2f}'.format #option2
print(df_order_sales_date.info())
df_order_sales_date

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 3 entries, 0 to 2
Data columns (total 3 columns):
 #   Column           Non-Null Count  Dtype 
---  ------           --------------  ----- 
 0   column1          3 non-null      object
 1   column2          3 non-null      object
 2   Total sales USD  3 non-null      float64
dtypes: object(3)
memory usage: 200.0+ bytes
None

Out[3]: 
  column1 column2 Total sales USD
0       A       a          $10.05
1       A       b          $20.45
2       B       a          $20.75

However, if you go with option #1, then that will return:

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 3 entries, 0 to 2
Data columns (total 3 columns):
 #   Column           Non-Null Count  Dtype 
---  ------           --------------  ----- 
 0   column1          3 non-null      object
 1   column2          3 non-null      object
 2   Total sales USD  3 non-null      object
dtypes: object(3)
memory usage: 200.0+ bytes
None
Out[367]: 
  column1 column2 Total sales USD
0       A       a          $10.05
1       A       b          $20.45
2       B       a          $20.75
user_63667700
Related