-1
$\begingroup$

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.

$\endgroup$

1 Answer 1

1
$\begingroup$

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:

enter image description here

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
$\endgroup$
4
  • $\begingroup$ my only concern was how will the function understand what columns to iterate on(format) and what columns to skip automatically. $\endgroup$
    – m2rik
    Feb 27, 2020 at 14:53
  • 1
    $\begingroup$ @m2rik I dont think that you can do it "easy" way, since for currency and percantage formatting you can use both floats or ints, there is now way to understand what type is used for which formatting, this is why I have added function with column name mapping. If your dataframes have different names but similar column position, maybe its better to rename df to common format and then do the formatting? $\endgroup$ Feb 27, 2020 at 15:33
  • $\begingroup$ Yes, that was my concern too. Iterating over this function my plan is to create different datatype functions to handle the types and then apply the conversion. Something like format(df['salary'],integer) where def integer(x) is a function to handle int datatype, similarly others for floats and str. What do you think? $\endgroup$
    – m2rik
    Feb 27, 2020 at 16:14
  • $\begingroup$ @m2rik I prefer specifying lists with column names like in the example before if possible and not to mess around with lots of individual functions, but if you know ahead that each type strictly corresponds to special formatting, you can do it with custom functions. $\endgroup$ Feb 28, 2020 at 0:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.