Extract and map similar textresult with base text, convert into two columns using Pandas

user_10780422 0
Viewed 89

The following is gui result dataframe.

Item_id  Similarity_Id      Result      
100    0            textboxerror
101    100          text_input_issue
102    0            menuitemerror
103    100          text_click_issue
104    100          text_caps_error
105    102          menu_drop_down_error
106    100          text_lower_error
107    102          menu_item_null

In the above dataframe, Item_id and Result are correlated. Each Item_id has one Results. Based on the similarity_Id, I need to create two different columns. One column sentence one is base sentence and sentence2 is similarity sentences. For example. In similarity_Id four sentences in Result have same similarity_Id. Item_id of 101,103,104 and 106 have similar Result of Item_id 100. So, in sentence 1 , I need to have Result respective to Similarity_Id 100, in sentence2 I need similar Results of Item_id 100.

The final result needs to be as follows, 

index   sentence1   sentence2              Similarity_Id
1   textboxerror    text_click_issue        100
2   textboxerror    text_caps_error         100
3   textboxerror    text_caps_error         100
4   textboxerror    text_lower_error        100
5   menuitemerror   menu_drop_down_error    102
6   menuitemerror   menu_item_null          102
7   textboxerror    Null                     0
8   menuitemerror   Null                     0

I tried groupby and merge,melt and unique. But, desired result not comes.

df1 = pd.read_cav("/test.csv")
group = df1.groupby('Result')
df2 = group.apply(lambda x: x['Result'].unique())

print ("df2: \n", df2)
print (df1.Result.apply(pd.Series))

df3 = df1.Result.apply(pd.Series).merge(df1, left_index = True, right_index = True).drop(["Result"], axis = 1) \
    .melt(id_vars = ['Item_id', 'Similarity_Id'], value_name = "Result").drop("variable", axis = 1)\
        .dropna()
print (df3)

How can I achieve this. Thanks, Sundara

pandas dataframe nlp python
1 Answers

We can use pd.merge to left merge the dataframe df with itself on Similarity_ID and Item_id then use DataFrame.rename to rename the columns as required:

df1 = (
    pd.merge(
        df[['Similarity_Id', 'Result']], df[['Item_id', 'Result']],
        left_on='Similarity_Id', right_on='Item_id', how='left')
    .rename(columns={'Result_x': 'sentence1', 'Result_y': 'sentence2'})
    .filter(items=['sentence1', 'sentence2', 'Similarity_Id'])
)

# print(df1)
              sentence1      sentence2  Similarity_Id
0          textboxerror            NaN              0
1      text_input_issue   textboxerror            100
2         menuitemerror            NaN              0
3      text_click_issue   textboxerror            100
4       text_caps_error   textboxerror            100
5  menu_drop_down_error  menuitemerror            102
6      text_lower_error   textboxerror            100
7        menu_item_null  menuitemerror            102
user_128331660
Related