I have a dataframe DF1 which looks like this:

DF2 looks like this:

I want to create DF3 based on DF1 & DF2 by doing the following:

1. Filter rows with where the Flag = True
2. Create a new column 'Calculated_Cost' which will multiply the 'Cost' column in DF1 with percentage column of DF2 & create multiple rows based on the number of rows in DF2

The Final output would look like this:

CodePudding user response：

Use:

df1['Cost'] = df1['Cost'].str.lstrip('$').astype(int)
df2['Percentage'] = df2['Percentage'].str.rstrip('%').astype(int).div(100)

df = pd.concat([df1[df1['Flag']].merge(df2, how='cross'), df1[~df1['Flag']]])
df['Calculated_Cost'] = df['Cost'].mul(df.pop('Percentage'))
print (df)
  Account Name Task Type   Flag  Cost Country  Calculated_Cost
0    Account 1    Repair   True   100      US             30.0
1    Account 1    Repair   True   100  Canada             20.0
2    Account 1    Repair   True   100   India             50.0
3    Account 2    Repair   True   200      US             60.0
4    Account 2    Repair   True   200  Canada             40.0
5    Account 2    Repair   True   200   India            100.0
2    Account 3    Repair  False   300     NaN              NaN

CodePudding user response：

I am sure there is a more efficient way to do this... but I got it done using the following code:

import pandas as pd

df1 = pd.DataFrame(
    {
     'Account Name': ['Account 1', 'Account 2', 'Account 3'],
     'Task Type': ['Repair', 'Repair', 'Repair'],
     'Flag': ['True', 'True', 'False'],
     'Cost': ['$100', '$200', '$300']
       }
    )

df2 = pd.DataFrame(
    {
     'Country': ['US', 'Canada', 'India'],
     'Percentage': ['30%', '20%', '50%']
       }
    )

df1['Cost'] = df1['Cost'].str.lstrip('$').astype(int)
df2['Percentage'] = df2['Percentage'].str.rstrip('%').astype(int).div(100)
filtered_df_true = df1.loc[df1['Flag'] == 'True'] 
filtered_df_false = df1.loc[df1['Flag'] == 'False']
df3 = filtered_df_true.assign(key=1).merge(df2.assign(key=1), how = 'outer', on='key')
df3['Calculated Cost'] = df3['Cost']*df3['Percentage']
frames = [df3, filtered_df_false]
result = pd.concat(frames)
result.pop('key')
result.pop('Percentage')
print(result)