My example;

df_samecompanies['Codes'] 

df['Volume Order'] = df['Volume'].rank(ascending=False)
df['Trade Order']  = df['Trade'].rank(ascending=False)
df['Trade Order2']  = df['Trade'].rank(ascending=True)
df['Max Ordered Number']  = df[['Volume Order', 'Trade Order']].max(axis=1)
df['Final Sorted Number'] = df[['Max Ordered Number', 'Trade Order2']].apply(tuple, axis=1).rank(ascending=False)
df.drop('Trade Order2', axis=1)

Output;

       Code  Volume  Trade  Value  Volume Order  Trade Order  Max Ordered Number  Final Sorted Number
0     ApplA     500   1000   <NA>             2            2                   2                    4
1    Amazon    1000    500   <NA>             1            4                   4                    2
2  Facebook     250    750   <NA>             3            3                   3                    3
  3   ApplE     100   1500   <NA>             4            1                   4                    1

I have 2 dataframes. There is no common column name. But there are common values. My first dataframe is located above. İts name is "df".

The other dataframe's name is "buffer".

my purpose is to embed the "Buffer" dataframe into the dataframe named ""df". As you can see, there is no common column name. But there are common values. By opening 3 new columns in the dataframe named df, the dataframe named "Buffer" vlookup the values ​​of the "Place" column into those 3 new columns.

Columns name will be "PLACE U10" - "PLACE U20" - PLACE "U30"

My expected output;

       Code  Volume  Trade  Value  Volume Order  Trade Order  Max Ordered Number  Final Sorted Number   PLACE U10   PLACE U20   PLACE U30
0     ApplA     500   1000   <NA>             2            2                   2                    4        U10           U20        U30
1    Amazon    1000    500   <NA>             1            4                   4                    2        U10           U20        U30 
2  Facebook     250    750   <NA>             3            3                   3                    3        U10           U20
  3   ApplE     100   1500   <NA>             4            1                   4                    1       U10            U20        U30

as you see, facebook doesn't has "u30".

Can you help me to embed dataframe named buffer into dataframe named df with common values ​​without common column name? If necessary, I can make the column names the same.

CodePudding user response：

You can pivot on dataframe buffer using .pivot(), then use .join() to join df with the pivoted table of buffer, aligning index of Code in df with index of Name in the pivoted table, as follows:

buffer_pivot = buffer.pivot(index='Name', columns='Place', values='Place').add_prefix('PLACE ')

df = df.set_index('Code').join(buffer_pivot).reset_index()

Data Input

print(buffer)

       Name Place
0     ApplA   U30
1    Amazon   U30
2     ApplA   U20
3  Facebook   U10
4     ApplE   U10
5    Amazon   U20
6     ApplA   U10
7  Facebook   U20
8    Amazon   U10
9     ApplE   U20

Result:

print(buffer_pivot)

Place    PLACE U10 PLACE U20 PLACE U30
Name                                  
Amazon         U10       U20       U30
ApplA          U10       U20       U30
ApplE          U10       U20       NaN
Facebook       U10       U20       NaN


print(df)


       Code  Volume  Trade  Volume Order  Trade Order  Max Ordered Number  Final Sorted Number PLACE U10 PLACE U20 PLACE U30
0     ApplA     500   1000           2.0          2.0                 2.0                  4.0       U10       U20       U30
1    Amazon    1000    500           1.0          4.0                 4.0                  2.0       U10       U20       U30
2  Facebook     250    750           3.0          3.0                 3.0                  3.0       U10       U20       NaN
3     ApplE     100   1500           4.0          1.0                 4.0                  1.0       U10       U20       NaN