CODEWITHSUNDEEP
pythonpandas
Harris
Harris

Reputation: 147

How to conditionally do a vlookup in Pandas dataframe

I am trying to figure out how to do a vlookup to pick out the latest price to fill up a second table. An example below. For item #1, the latest price is at Month 6 (=$6) while item #2 is at Month 5 (=$4). What's the best way to fill up Table B? Note: There might be occasion that item_id cannot be found in Table A if the item is new.

Any guidance? Many Thanks.

Table A (Reference)

| Item_ID | Month | Price |
|---------|-------|-------|
| 1       | 4     | 10    |
| 1       | 5     | 8     |
| 1       | 6     | 6     |
| 2       | 5     | 4     |

Table B (To Fill)

| Shop_ID | Item_ID | Price |
|---------|---------|-------|
| 1       | 1       | 6     |
| 1       | 2       | 4     |

Upvotes: 1

Views: 1510

Answers (2)

jezrael
jezrael

Reputation: 862751

To fill column Price in df2 we can create a Pandas series with Item_ID and Price. Use drop_duplicates for last row for each Item_ID and create Series by set_index and selecting column. Lastly create new column with map.

Full example:

import pandas as pd

# Sample data
data1 = dict(Item_ID=[1,1,1,2], Month=[4,5,6,5], Price = [10,8,6,4])
data2 = dict(Shop_ID=[1,1],Item_ID=[1,2])

# Create dfs
df1 = pd.DataFrame(data1)
df2 = pd.DataFrame(data2)

# Crete a series with Item_ID as index and Price as value
s = df1.drop_duplicates('Item_ID', keep='last').set_index('Item_ID')['Price']

# Create new column in df2
df2['Price'] = df2['Item_ID'].map(s)
print (df2)

Returns:

   Shop_ID  Item_ID  Price 
0        1        1      6 
1        1        2      4

Further Details

If necessary use sort_values first

s = (df1.sort_values(['Item_ID','Month'])
        .drop_duplicates('Item_ID', keep='last')
        .set_index('Item_ID')['Price'])

the serie s looks like this:

Item_ID
1    6
2    4
Name: Price, dtype: int64

Upvotes: 2

chthonicdaemon
chthonicdaemon

Reputation: 19770

You can first find the latest information, then merge it to create the table:

import pandas


tableA = pandas.DataFrame({'Item_ID': {0: 1, 1: 1, 2: 1, 3: 2},
                           'Month': {0: 4, 1: 5, 2: 6, 3: 5},
                           'Price': {0: 10, 1: 8, 2: 6, 3: 4}})
tableB = pandas.DataFrame({'Item_ID': {0: 1, 1: 2}, 
                           'Price': {0: 6, 1: 4}, 
                           'Shop_ID': {0: 1, 1: 1}})

latest = tableA.loc[tableA.groupby('Item_ID')['Month'].idxmax()]
result = tableB[['Shop_ID', 'Item_ID']].merge(latest[['Item_ID', 'Price']],
                                              on='Item_ID')

This yields

       Shop_ID  Item_ID  Price
0        1        1      6
1        1        2      4

Upvotes: 1

Related Questions