I am trying to make 3 columns for a dataframe, where each of them shows if this row has been found on any of 4 other dataframes, where the number of the month (another column present in all dataframes) must be the same. Each column evaluate if the row has been found according to different subsets of status.
The main dataframe:
| id | phone | month |
|---|---|---|
| 123 | 31022222 | 8 |
| 123 | 31141111 | 8 |
The other dataframes are basically like this:
| calldate | phone | month | status |
|---|---|---|---|
| 12/12/2021 07:07:07 | 31022222 | 8 | voicemail |
| 12/12/2021 07:08:07 | 31022222 | 8 | NI |
| 12/12/2021 07:09:07 | 31022222 | 8 | other |
| 12/12/2021 07:10:07 | 31022222 | 8 | failed |
Each table has different status and many of those status can correspond to one of the 3 columns I want to create
I have tried vectorizing with np.select where conditions are isin but that does not evaluate each phone number and the month number, also I have tried with list comprehensions and a function with several try-catch blocks where each block tries to find the value on certain dataframe with its corresponding conditions.
This:
conditions = [dfm["phone"].isin(df1["phone"]),dfm["phone"].isin(df2["phone"]),dfm["phone"].isin(df3["phone"])]
choices = [True, True, True]
dfm=np.select(conditions, choices, default=False)
or this:
def get_data(cond, param, x):
try:
return intranet.loc[(intranet["estado"].isin(x["intranet"])) & (intranet["Mes"] == param) & (intranet["numeroContacto"] == cond), "estado"].iloc[-1]
except:
try:
return calls.loc[(calls["status"].isin(x["llamadas"])) & (calls["Mes"] == param) & (calls["phone_number"] == cond), "status"].iloc[-1]
except:
try:
return manual.loc[(manual["disposition"].isin(x["manual"])) & (manual["Mes"] == param) & (manual["dst"] == cond), "disposition"].iloc[-1]
except:
try:
return predict.loc[(predict["status"].isin(x["predictivo"])) & (predict["Mes"] == param) & (predict["phone"] == cond), "status"].iloc[-1]
except:
return "False"
dfm["efecty"] = [get_data(x, month, cond) for x, month in np.array(dfm[["phone", "month"]]
This would give me instead of True, just the status found, something that also would be great but not necessary, also I pass on each column a dictionary with all the status accepted for the respective column, this last trial failed due my power bi python interpreter timed out to run the code.
All of this operations are being done in power bi so my computing capacity is limited also because the pc I am working on is old.
I am looking for a result like this:
| id | phone | month | effective | noeffective | nocontact |
|---|---|---|---|---|---|
| 123 | 31022222 | 8 | False | True | False |
| 123 | 31141111 | 8 | False | False | False |
Where true appears when it has been found according to the conditions, false if not.
In short, what I want is: to look for the phone number where the month matches and is present in records with certain statuses, these statuses change across dataframes, so I look if is present in any of the 4 other dataframes according it corresponding statuses and repeat this action for the other 2 column where I evaluate other statuses matching number and phone