Merge the data created in a loop (python)

Question

I have a simple dataset:

import pandas as pd
data = [['A', 10,16], ['B', 15,11], ['C', 14,8]] 
df = pd.DataFrame(data, columns = ['Name', 'Apple','Pear']) 

Output
    Name Apple  Pear
0   A   10  16
1   B   15  11
2   C   14  8

I want to rank the quantity of different fruits - apple and pear. The rule:

determine the difference between each place for apple and pear
rank the difference by place. Two places with the closer quantity will receive lower ranking

# apple
dif = abs(df['Apple'].values - df['Apple'].values[:, None])
df_apple  = pd.concat((df['Name'], pd.DataFrame(dif, columns = df['Name'])), axis=1)
df_apple1 = pd.melt(df_apple, id_vars = ['Name'], value_name='Difference_apple')
df_apple1 = df_apple1[df_apple1.Difference_apple != 0]
df_apple1['Ranking_apple'] = df_apple1.groupby('variable')['Difference_apple'].rank(method = 'dense', ascending = True)
df_apple1 = df_apple1[["variable","Name","Ranking_apple"]]
df_apple1

# Output - apple
    variable    Name    Ranking_apple
1   A   B   2.0
2   A   C   1.0
3   B   A   2.0
5   B   C   1.0
6   C   A   2.0
7   C   B   1.0

# pear
dif = abs(df['Pear'].values - df['Pear'].values[:, None])
df_pear  = pd.concat((df['Name'], pd.DataFrame(dif, columns = df['Name'])), axis=1)
df_pear1 = pd.melt(df_pear, id_vars = ['Name'], value_name='Difference_pear')
df_pear1 = df_pear1[df_pear1.Difference_pear != 0]
df_pear1['Ranking_pear'] = df_pear1.groupby('variable')['Difference_pear'].rank(method = 'dense', ascending = True)
df_pear1 = df_pear1[["variable","Name","Ranking_pear"]]
df_pear1

# output-pear
    variable    Name    Ranking_pear
1   A   B   1.0
2   A   C   2.0
3   B   A   2.0
5   B   C   1.0
6   C   A   2.0
7   C   B   1.0

That is the algorithm for each fruit. As I use the same logic, so I can create a loop for each fruit. I am not sure how to merge these two pieces, because I need the final output to look like the following way:

new_df = pd.merge(df_apple1, df_pear1,  how='inner', left_on=['variable','Name'], right_on = ['variable','Name'])

new_df = new_df[["variable","Name","Ranking_apple","Ranking_pear"]]

new_df

# output
variable    Name    Ranking_apple   Ranking_pear
0   A   B   2.0 1.0
1   A   C   1.0 2.0
2   B   A   2.0 2.0
3   B   C   1.0 1.0
4   C   A   2.0 2.0
5   C   B   1.0 1.0

I appreciate any ideas. Thank you

What's the problem? Seems like you have your expected output. Do you just want to generalise? — forgetso
– forgetso, Commented Mar 10, 2021 at 13:07
Yes, I want to have one algorithm for multiple columns. Thank you — Qianru Song
– Qianru Song, Commented Mar 10, 2021 at 13:36

forgetso · Accepted Answer · 2021-03-10 13:14:45Z

If you are looking to generalise your method for any arbitrary number of fruits, you could do the following:

data = [['A', 10,16], ['B', 15,11], ['C', 14,8]] 
df = pd.DataFrame(data, columns = ['Name', 'Apple','Pear']) 

# all fruit
final = pd.DataFrame()
fruitcols = df.columns.values.tolist()
fruitcols.remove('Name')
for col in fruitcols:
    dif = abs(df[col].values - df[col].values[:, None])
    diff_col = 'Difference_{}'.format(col)
    rank_col = 'Ranking_{}'.format(col)
    df_frt  = pd.concat((df['Name'], pd.DataFrame(dif, columns = df['Name'])), axis=1)
    df_frt1 = pd.melt(df_frt, id_vars = ['Name'], value_name=diff_col)

    df_frt1 = df_frt1[df_frt1[diff_col] != 0]
    df_frt1[rank_col] = df_frt1.groupby('variable')[diff_col].rank(method = 'dense', ascending = True)
    df_frt1 = df_frt1[["variable","Name",rank_col]]
    df_frt1
    final = pd.concat([final, df_frt1], axis=1)

final.loc[:,~final.columns.duplicated()]


    variable    Name    Ranking_Apple   Ranking_Pear
1   A           B       2.0             1.0
2   A           C       1.0             2.0
3   B           A       2.0             2.0
5   B           C       1.0             1.0
6   C           A       2.0             2.0
7   C           B       1.0             1.0

Collectives™ on Stack Overflow

Merge the data created in a loop (python)

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related