Create new row in a dataframe using the value from different dataframe

Question

I'm new in Python or coding per se. I need your help. So I have 2 dataframe here containing different columns for data learning process. The first data frame contains Intents and Responses with PID. The second data frame spntains Intent and Phrases. I want to use PID information in the first dataframe to create new column in second dataframe that contains team.

first dataframe (var 'finalchat')

intent              response                                 Pid
PizzaDelivery       What Pizza do you like?                  1.4.6
WeatherForecast     What do you want to know the weather?    2
Emergency Number    Dial 911                                 10.8.9

second dataframe (var 'intentdatas')

intent              response                                 
PizzaDelivery       I want to order a pizza                  
WeatherForecast     What's the weather in Manila             
Emergency Number    What's the number to dial?                  

I want to get the first value in of Pid in the first dataframe. Which are 1, 2, and 3 and compare it with hardcoded condition where if its both TRUE then it will create a column in second dataframe with is 'Team'.

Here's the code I'm trying out but when I run it. It doesn't show anything.

row_list = []
for index,row in intentdatas.iterrows():
    if ([re.search("1",index) is not None for index in 
    finalchat['Pid'].astype(str).str[0]] == 'TRUE'):
    row_list.append({'intent': row['intent'], 'phrases': row['phrases'], 
    'Team': 'Pizza'})
elif ([re.search("2",index) is not None for index in 
     finalchat['Pid'].astype(str).str[0]] == 'TRUE'):
     row_list.append({'intent': row['intent'], 'phrases': row['phrases'], 
     'Team': 'Weather'})
elif ([re.search("10",index) is not None for index in 
     finalchat['Pid'].astype(str).str[1]] == 'TRUE'):
    row_list.append({'intent': row['intent'], 'phrases': row['phrases'], 
    'Team': 'Emergency'})

intentdatas.head(10)

Expected out put

Intent              Response                          Team                   
PizzaDelivery       I want to order a pizza           Pizza     
WeatherForecast     What's the weather in Manila      Weather      
Emergency Number    What's the number to dial?        Emergency

Someone told that I can't compare a list to Singleton which is the TRUE statement. Maybe that's why there's nothing outputs whenever i print the intentdatas. Please help me. thanks!

Answer 1

You can merge your data frames, then map teams from Pid values.

team_map = {"1":"Pizza", "2":"Weather", "10":"Emergency"}

df = intentdatas.merge(finalchat[["intent","Pid"]], on="intent")

df.assign(team=df.Pid.apply(lambda x: team_map[x.split(".")[0]])).drop("Pid", 1)

             intent                      response          team
0     PizzaDelivery       I want to order a pizza         Pizza
1   WeatherForecast  What's the weather in Manila       Weather
2  Emergency Number    What's the number to dial?     Emergency