Python pandas - look up value in different df using 2 columns' values, then calculate differenc-CodePudding

I want to add a column to my df to show the difference betweeb the CurrentScore and the base scores corresponding to the same Date, Sector, and Classification. The base scores are in a separate dataframe called base_score_df with the Dates as its index. If the base_score_df is missing that day's base scores, I want the result to be null.

The main df:

import pandas as pd
import numpy as np
df = pd.DataFrame({'Date': '2022-2-1 2022-2-1 2022-2-2 2022-2-2 2022-2-2 2022-2-3 2022-2-3 2022-2-3'.split(),
                   'Name': 'Walmart Google Walmart Microsoft Target Walmart Google Microsoft'.split(),
                   'Sector': 'Retail Tech Retail Tech Retail Retail Tech Tech'.split(),
                   'Classification': '3 4 3 5 5 4 4 4'.split(),
                   'CurrentScore': '200 197 202 188 186 193 202 201'.split()
                   })
print(df)

       Date       Name  Sector Classification CurrentScore
0  2022-2-1    Walmart  Retail              3          200
1  2022-2-1     Google    Tech              4          197
2  2022-2-2    Walmart  Retail              3          202
3  2022-2-2  Microsoft    Tech              5          188
4  2022-2-2     Target  Retail              5          186
5  2022-2-3    Walmart  Retail              4          193
6  2022-2-3     Google    Tech              4          202
7  2022-2-3  Microsoft    Tech              4          201

The base_score_df:

base_score_df=pd.DataFrame({'Date': '2022-2-1 2022-2-3'.split(),
                   'Retail 3': '100 97'.split(),
                   'Retail 4': '102 100'.split(),
                   'Retail 5': '103 101'. split(),
                   'Tech 3': '105 107'.split(),
                   'Tech 4': '110 109'.split(),
                   'Tech 5': '112 113'.split()
                    })
base_score_df.set_index(['Date'], inplace=True)
print(base_score_df)

         Retail 3 Retail 4 Retail 5 Tech 3 Tech 4 Tech 5
Date                                                    
2022-2-1      100      102      103    105    110    112
2022-2-3       97      100      101    107    109    113

My solution is to (1) concatenate Sector and Classification into a "Sector Classification" column, (2) use a for loop, itertuples, and apply() to look up the base scores row by row to put into a new "Base Score" column in the df, (3) calculate the difference in another column

Code for (2):

for row in df.iterruples(index=False,name='SP'):
    def base_score_lookup(row):
        scoredate=row['Date'],
        header=row['Sector Classification']
        return base_score_df.loc[scoredate,header]

base_score_df['Base Score']=df.apply(base_score_lookup,axis=1)

The problem is, if a date is missing in the base_score_df, the code doesn't run. I just want to use a null value in that case and move on to the next row. And I wonder the code can be written differently for faster speed. Thanks in advance.

CodePudding user response：

Here's what you can do, explanation in the comments:

import pandas as pd
import numpy as np

df = pd.DataFrame({'Date': '2022-2-1 2022-2-1 2022-2-2 2022-2-2 2022-2-2 2022-2-3 2022-2-3 2022-2-3'.split(),
                   'Name': 'Walmart Google Walmart Microsoft Target Walmart Google Microsoft'.split(),
                   'Sector': 'Retail Tech Retail Tech Retail Retail Tech Tech'.split(),
                   'Classification': '3 4 3 5 5 4 4 4'.split(),
                   'CurrentScore': '200 197 202 188 186 193 202 201'.split()
                   })

base_score_df=pd.DataFrame({'Date': '2022-2-1 2022-2-3'.split(),
                   'Retail 3': '100 97'.split(),
                   'Retail 4': '102 100'.split(),
                   'Retail 5': '103 101'. split(),
                   'Tech 3': '105 107'.split(),
                   'Tech 4': '110 109'.split(),
                   'Tech 5': '112 113'.split()
                    })

# ensure date column is in the same format
df['Date']            = pd.to_datetime(df.Date)
base_score_df['Date'] = pd.to_datetime(base_score_df.Date)

# melt the base score df into a long format
base_score_df = pd.melt(base_score_df,
                        id_vars=['Date'],
                        value_vars=[_ for _ in base_score_df.columns if _ != 'Date'])

base_score_df.columns = ['Date', 'category', 'BaseScore']

# split the category into Sector and Classification
base_score_df['Sector'], base_score_df['Classification'] = zip(*base_score_df.category.str.split(' '))
base_score_df.drop('category', axis=1, inplace=True)

# merge back with original dataframe
df = pd.merge(df,
              base_score_df,
              on=['Date', 'Sector', 'Classification'],
              how='left')

# calculate score difference
df['ScoreDiff'] = df['CurrentScore'].astype(float) - df['BaseScore'].astype(float)

# output
df

Date    Name    Sector  Classification  CurrentScore    BaseScore   ScoreDiff
0   2022-02-01  Walmart Retail  3   200 100 100.0
1   2022-02-01  Google  Tech    4   197 110 87.0
2   2022-02-02  Walmart Retail  3   202 NaN NaN
3   2022-02-02  Microsoft   Tech    5   188 NaN NaN
4   2022-02-02  Target  Retail  5   186 NaN NaN
5   2022-02-03  Walmart Retail  4   193 100 93.0
6   2022-02-03  Google  Tech    4   202 109 93.0
7   2022-02-03  Microsoft   Tech    4   201 109 92.0