Custom Pandas function for formatting

Question

My data consists of a lot of Dataframes having the format as below:

raw_data = {
        'subject_id': ['1', '2', '3', '4', '5'],
        'first_name': ['Alex', 'Amy', 'Allen', 'Alice', 'Ayoung'], 
        'last_name': ['Anderson', 'Ackerman', 'Ali', 'Aoni', 'Atiches'],
        'salary': ['2000','200000','3000','300','10000'],
        'percentage': [24,434,56,12,245]}
df = pd.DataFrame(raw_data, columns = ['subject_id', 'first_name', 'last_name','salary','percentage'])

0 1   Alex       Anderson     2000    24
1 2   Amy        Ackerman     200000  434
2 3   Allen      Ali          3000    56
3 4   Alice      Aoni         300     12
4 5   Ayoung     Atiches      10000   245

My goal is to have a custom function for formatting them to the following: `

0 1   Alex    Anderson    $2,000.00       24.00%
1 2   Amy     Ackerman    $200,000.00 434.00%
2 3   Allen   Ali         $3,000.00       56.00%
3 4   Alice   Aoni        $300.00     12.00%
4 5   Ayoung  Atiches     $10,000.00      245.00%`

Examples of Dataframes I have:

Description A B C D School 35 1.01% 0.17% -$139,394 Fishing 5 0.57% 0.21% -$30,572

School c Cur NeT OOS Diff Scs 663 Med 16-EM $360 $312 Scs_2 720 Pharmacy 16-SOP $360 $312

current :

df['salary'] = df['salary'].apply(lambda x : f"${x:,.2f}")
df['percentage'] = df['percentage'].apply(lambda n : f"{n:.2%}")

I know that lambda functions will do this, but I want to have a custom function def transform: to handle all data types and format every Dataframe that I have. My plan is to use try and catches to handle the datatypes. But need some help with the function.

Answer 1

If all your data frame are the same ( colnames and types) you can use list comprehension to change format of your data frame. Here is an example solution with two data frames:

raw_data = {
    'subject_id': ['1', '2', '3', '4', '5'],
    'first_name': ['Alex', 'Amy', 'Allen', 'Alice', 'Ayoung'], 
    'last_name': ['Anderson', 'Ackerman', 'Ali', 'Aoni', 'Atiches'],
    'salary': ['2000','200000','3000','300','10000'],
    'percentage': [24,434,56,12,245]}

raw_data1 = {
    'subject_id': ['1', '2'],
    'first_name': ['Jimmy', 'Kira'], 
    'last_name': ['Anderson', 'Ackerman'],
    'salary': ['2000','200000'],
    'percentage': [24,434]}

df = pd.DataFrame(raw_data, columns = ['subject_id', 'first_name', 'last_name','salary','percentage'])
df1 = pd.DataFrame(raw_data1, columns = ['subject_id', 'first_name', 'last_name','salary','percentage'])

here is a function that modifies input DF`s:

def transform(df):
    df['salary'] = df['salary'].apply(lambda x : f"${float(x):,.2f}")
    df['percentage'] = df['percentage'].apply(lambda x : f"{float(x):.2%}")
    return df

Now you can iterate over the list of your data frames and change the formating , I will also concantenate the result into one data frame, but you can keep the result in the plain list for next steps if needed.

#create list of dataframes 
list_of_dfs = [df, df1]
#iterate over list and concat the result 
pd.concat([transform(x) for x in list_of_dfs], axis = 0)

Example output:

In case if your data frames doesn't have a common column to format, you can create a mapping with column names and desired format and include it in the function like this:

def transform(df):
    perc_format = ['B', 'C', 'D']
    currency_format = ['D1', 'Diff']
    for col in df.columns:
        if col in perc_format:
            df[col] = df[col].apply(lambda x : f"{float(x):.2%}")
        elif col in currency_format:
            df[col] = df[col].apply(lambda x : f"${float(x):,.2f}")
    return df