Conditional vlookup in Python 3

user_10731747 0
Viewed 1204

please help. There is dataframe:

ArtNo   Description     Price

AAA     Lore Ipsum      10
BBB     Lore Ipsum      9
CCC     Lore Ipsum      8
DDD     AAA Lore Ipsum  0
EEE     BBB Lore Ipsum  0
FFF     CCC Lore Ipsum  0
GGG     ZZZ Lore Ipsum  0
HHH     AAA Lore Ipsum  0

I need to add a column with multiplied price instead of zero by article in Description column from article in ArtNo column. If there is no match (between ArtNo column and first word in Description to space), apply to "multiplied column" the same figure from Price column in same row:

ArtNo   Description     Price   Price (multiplied) ???

AAA     Lore Ipsum      10      10  
BBB     Lore Ipsum      9       9   
CCC     Lore Ipsum      8       8   
DDD     AAA Lore Ipsum  0       10  
EEE     BBB Lore Ipsum  0       9   
FFF     CCC Lore Ipsum  0       8
GGG     ZZZ Lore Ipsum  0       0
HHH     AAA Lore Ipsum  0       10

In excel it works like this:

IF (Price != 0; Price multiplied = Price;
    IF(ISERROR(VLOOKUP(MID(Description;1;FIND(
        ' ';Description;1));TABLE;3;0));Price multiplied = Price;
    ESLE: Price multiplied = VLOOKUP(MID(Description;1;FIND(
        ' ';Description;1));TABLE;3;0)
    )
)

Thanks in advance.

vlookup pandas python
2 Answers

You can construct a series mapping and apply this to the first word of Description.

zeros = df['Price'].eq(0)
art_price_map = df[~zeros].set_index('ArtNo')['Price']
first_word = df['Description'].str.split(n=1).str[0]

df['Price (multiplied)'] = df['Price'].mask(zeros, first_word.map(art_price_map))\
                                      .fillna(0).astype(int)

print(df)

  ArtNo     Description  Price  Price (multiplied)
0   AAA      Lore Ipsum     10                  10
1   BBB      Lore Ipsum      9                   9
2   CCC      Lore Ipsum      8                   8
3   DDD  AAA Lore Ipsum      0                  10
4   EEE  BBB Lore Ipsum      0                   9
5   FFF  CCC Lore Ipsum      0                   8
6   GGG  ZZZ Lore Ipsum      0                   0
user_92095460

You can do it with pd.merge like so:

 #create new dataframe with ArtNo created from part of the Description
 df2 = df.copy()[['Description']]
 df2.columns = ['ArtNo']
 df2['ArtNo'] = df2['ArtNo'].str.split(n=1).str[0]

 #merge price from the first dataframe
 df2 = pd.merge(df2, df[['ArtNo', 'Price']], how='left', on='ArtNo')

 #create a new column 'Price (multiplied)' and fill NANs from original 'Price' column
 df['Price (multiplied)'] = df2['Price'].values
 df['Price (multiplied)'] = df['Price (multiplied)'].fillna(df['Price']).astype(int)
user_73903890
Related