Панды объединяют, масштабируют и поворачивают длинные и широкие кадры данных

Вот еще один способ, который использует только циклы по поставщику, но требует сортировки данных.

import pandas as pd
from io import StringIO
import numpy as np

df1_t = StringIO("""PART#,MY_QTY,MY_PRC
Item1,1,$20
Item1,10,$18
Item1,20,$17
Item2,1,$120
Item2,30,$100
Item2,50,$95
""")

df2_t = StringIO("""PART#,VEND#,QTY1,PRC1,QTY2,PRC2,QTY3,PRC3
Item1,Vend1,1,$10,5,$8,15,$7
Item1,Vend2,1,$15,11,$12,30,$11
Item1,Vend3,1,$20,10,$18
Item2,Vend1,1,$75,20,$60,30,$55
Item2,Vend2,1,$80,12,$70
""")

df1 = pd.read_csv(df1_t)
df2 = pd.read_csv(df2_t)


df2 = pd.wide_to_long(df2, ['QTY','PRC'], i='VEND#', j='v').set_index('QTY',append=True).reset_index().drop('v',
    axis=1)
df1['MY_QTY'] = df1['MY_QTY'].astype(float)
df1 = df1.sort_values(by="MY_QTY")
df2 = df2.sort_values(by="QTY")
df2 = df2.dropna(axis=0, how='any')

vendors = df2['VEND#'].unique()
df3=df1
for vendor in vendors:
    df3 = pd.merge_asof(df3, df2[df2['VEND#']==vendor], left_on="MY_QTY", right_on="QTY", by='PART#',suffixes=('', '_y'))

to_drop = [x for x in df3 if x.startswith('VEND')]
to_drop = to_drop + [x for x in df3 if x.startswith('QTY')]
df3.drop(to_drop, axis=1, inplace=True)
df3 = df3.rename(columns={prc : vendor for prc, vendor in zip([x for x in df3 if x.startswith('PRC')], vendors)})

print(df3)
#     PART#  MY_QTY MY_PRC Vend1 Vend3 Vend3
#0  Item1     1.0    $20   $10   $15   $20
#1  Item2     1.0   $120   $75   $80   NaN
#2  Item1    10.0    $18    $8   $15   $18
#3  Item1    20.0    $17    $7   $12   $18
#4  Item2    30.0   $100   $55   $70   NaN
#5  Item2    50.0    $95   $55   $70   NaN

Вот рабочий пример того, как вы можете это сделать. Это ни в коем случае не эффективно. Другие люди, казалось, пытались объединить два набора данных, но похоже, что вы хотели получить цену самой большой QTY <= MY_QTY для каждой комбинации поставщика / детали.

import pandas as pd
from io import StringIO
import numpy as np

df1_t = StringIO("""PART#,MY_QTY,MY_PRC
Item1,1,$20
Item1,10,$18
Item1,20,$17
Item2,1,$120
Item2,30,$100
Item2,50,$95
""")

df2_t = StringIO("""PART#,VEND#,QTY1,PRC1,QTY2,PRC2,QTY3,PRC3
Item1,Vend1,1,$10,5,$8,15,$7
Item1,Vend2,1,$15,11,$12,30,$11
Item1,Vend3,1,$20,10,$18
Item2,Vend1,1,$75,20,$60,30,$55
Item2,Vend2,1,$80,12,$70
""")

df1 = pd.read_csv(df1_t)
df2 = pd.read_csv(df2_t)

vendors = df2['VEND#'].unique()
items = df2['PART#'].unique()

# for the specific item and vendor in the rows of Dataframe1 (df1), find the 
# largest QTY for that that's less than MY_QTY for the same combination of item
# and vendor in df2
def find_price(row, vendor, df2):
    item = row['PART#']
    quantity = row['MY_QTY']
    # get the row with that specific item / vendor combo
    prices = df2[(df2['PART#']==item) & (df2['VEND#']==vendor)]
    # reshape a little
    prices = pd.wide_to_long(prices, ['QTY','PRC'], i='VEND#', j='v').set_index('QTY',append=True).reset_index().drop('v',axis=1)
    # only get where QTY <= MY_QTY
    prices = prices[prices['QTY']<=quantity]
    if prices.empty:
        return np.nan
    else:
        return prices.loc[prices['QTY'].argmax(),:]['PRC']


# iterate throw the vendors, and use find_price to get the corresponding price
for vendor in vendors:
    df1[vendor] = df1.apply(lambda row: find_price(row, vendor, df2),axis=1)

print(df1)
#   PART#  MY_QTY MY_PRC Vend1 Vend2 Vend3
#0  Item1       1    $20   $10   $15   $20
#1  Item1      10    $18    $8   $15   $18
#2  Item1      20    $17    $7   $12   $18
#3  Item2       1   $120   $75   $80   NaN
#4  Item2      30   $100   $55   $70   NaN
#5  Item2      50    $95   $55   $70   NaN