Is there any method to avoid duplicate result by looping in python?

user_13275213 0
Viewed 78

i have 2 set of data and i would like to find and match by condition and i do not want my matched result to be repeated if have similar criteria.

data as below:

df = pd.DataFrame({'date' : ['16-04-2020', '20-04-2020', '16-04-2020', '16-04-2020', '20-04-2020', '20-04-2020', '16-04-2020'],
                   'item' : ['X-317', 'ry-186', 'opq-468','999-xq','789-44','154-abc','opq-468'],
                   'no': ['36','55','45','77','107','1149','456' ],
                   'result':['','','','','','','']})


df_list = pd.DataFrame({'date' : ['16-04-2020', '16-04-2020', '16-04-2020', '20-04-2020', '16-04-2020', '16-04-2020', '20-04-2020'],
                        'item' : ['X-317', 'ry-186','789-44','154-abc', 'opq-468','opq-468','123-x'],
                        'complete': ['yes','no','on-going','yes','yes','yes','no' ],
                        'code':['alpha', 'vertical','horizon','grand','empire','jade','roms']})

my intention is to match date, complete = 'yes' and item in df is same as df_list, but if there is similar from the result, it will proceed to find next one

i tried below but it keep shows me 2 similar result, and if i add a break, it will not shows the result

listrange = []
for i in range(len(df_list)):
    for j in range(len(df)):
        if ((df_list['date'][i] == df['date'][j] ) and (df_list['complete'][i] == 'yes') and (df_list['item'][i] == df['item'][j])):         
            df['result'][j] = df_list['code'][i]
            # break    
print(df)

OUTPUT(1):
         date     item    no result
0  16-04-2020    X-317    36  alpha
1  20-04-2020   ry-186    55       
2  16-04-2020  opq-468    45   jade
3  16-04-2020   999-xq    77       
4  20-04-2020   789-44   107       
5  20-04-2020  154-abc  1149  grand
6  16-04-2020  opq-468   456   jade

OUTPUT(1):        >>>>>>>>>>>>>>>>>>>>>> with break in the code
         date     item    no  result
0  16-04-2020    X-317    36   alpha
1  20-04-2020   ry-186    55        
2  16-04-2020  opq-468    45  jade
3  16-04-2020   999-xq    77        
4  20-04-2020   789-44   107        
5  20-04-2020  154-abc  1149   grand
6  16-04-2020  opq-468   456

EXPECTED result should be as below:

         date     item    no  result
0  16-04-2020    X-317    36   alpha
1  20-04-2020   ry-186    55        
2  16-04-2020  opq-468    45  empire
3  16-04-2020   999-xq    77        
4  20-04-2020   789-44   107        
5  20-04-2020  154-abc  1149   grand
6  16-04-2020  opq-468   456  jade
pandas dataframe python
2 Answers

What about this :

import pandas as pd

df = pd.DataFrame({'date' : ['16-04-2020', '20-04-2020', '16-04-2020', '16-04-2020', '20-04-2020', '20-04-2020', '16-04-2020'],
                   'item' : ['X-317', 'ry-186', 'opq-468','999-xq','789-44','154-abc','opq-468'],
                   'no': ['36','55','45','77','107','1149','456' ],
                   'result':['','','','','','','']})


df_list = pd.DataFrame({'date' : ['16-04-2020', '16-04-2020', '16-04-2020', '20-04-2020', '16-04-2020', '16-04-2020', '20-04-2020'],
                        'item' : ['X-317', 'ry-186','789-44','154-abc', 'opq-468','opq-468','123-x'],
                        'complete': ['yes','no','on-going','yes','yes','yes','no' ],
                        'code':['alpha', 'vertical','horizon','grand','empire','jade','roms']})

df2 = df.drop('result', axis=1)

yes_ix = df_list[df_list.complete=="yes"].index
df2 = df2.merge(df_list.loc[yes_ix].drop('complete', axis=1), on=["date", "item"], how="left")
df2.rename({"code":"result"}, axis=1, inplace=True)
df2.result = df2.result.fillna("")
print(df2)

First, you filter df_list with the "yes" on "complete".

Secondly, you use pandas.DataFrame.merge to join the dataframes based on "date" and "item". The rest is only pure formatting (renaming columns, replacing NaN by empty strings and the like.

user_69364890

You could do:

import pandas as pd

df = pd.DataFrame({'date' : ['16-04-2020', '20-04-2020', '16-04-2020', '16-04-2020', '20-04-2020', '20-04-2020', '16-04-2020'],
                   'item' : ['X-317', 'ry-186', 'opq-468','999-xq','789-44','154-abc','opq-468'],
                   'no': ['36','55','45','77','107','1149','456' ],
                   'result':['','','','','','','']})


df_list = pd.DataFrame({'date' : ['16-04-2020', '16-04-2020', '16-04-2020', '20-04-2020', '16-04-2020', '16-04-2020', '20-04-2020'],
                        'item' : ['X-317', 'ry-186','789-44','154-abc', 'opq-468','opq-468','123-x'],
                        'complete': ['yes','no','on-going','yes','yes','yes','no' ],
                        'code':['alpha', 'vertical','horizon','grand','empire','jade','roms']})

df['id'] = df.groupby(['item']).transform('cumcount')
df = df.drop('result', 1)
df_list['id'] = df_list.groupby(['item']).transform('cumcount')

res = df.merge(df_list[['date', 'item', 'id', 'code']], how='left',
               on=['date', 'item', 'id']).drop('id', 1).rename(columns={'code': 'result'}).fillna('')

print(res)

Output

         date     item    no  result
0  16-04-2020    X-317    36   alpha
1  20-04-2020   ry-186    55        
2  16-04-2020  opq-468    45  empire
3  16-04-2020   999-xq    77        
4  20-04-2020   789-44   107        
5  20-04-2020  154-abc  1149   grand
6  16-04-2020  opq-468   456    jade

The idea behind creating the id column is to solve the issue of:

but if there is similar from the result, it will proceed to find next one

Note that rows with the same value at the item column, will have different id. At the same time, the id values will allow to match first of each value in df with its corresponding on df_list and so on.

user_40015920
Related