Merging two dataframes of different sizes, as per some condition

user_11883670 0
Viewed 78

SOLVED (see comment by Unbridledscum below)

I am currently attempting to join the information of two dataframes together.

My initial dataframe contains the product ID (SKU_id) as well as the product name as follows:

(Pardon the truncation of SKU_id)

df
Out[127]: 
          SKU_id          Product Name
0   7.336012e+09  OLD STYLE BEER      
1   1.820001e+09  BUDWEISER BEER LONG 
2   1.820000e+09  BUDWEISER BEER      
3   3.410018e+09  MILLER GENUINE DRAFT
4   3.410018e+09  MILLER GENUINE DRAFT
5   8.066096e+09  CORONA EXTRA BEER NR

My other dataframe contains all other product information such as unit sales etc (As well as SKU_id).

data.head()
Out[159]: 
       Store_id      SKU_id  WEEK  Units_Sold  QTY  PRICE Promotion 
20462  5         1820000016  166   1           1    3.79   NaN        
20463  5         1820000016  167   3           1    3.79   NaN        
20464  5         1820000016  168   6           1    3.79   NaN        
20465  5         1820000016  169   2           1    3.79   NaN        
20466  5         1820000016  170   1           1    3.79   NaN

I now want to join these two dataframes. More specifically I want to add the name of the particular SKU_id, which is present in the first dataframe (df) and not the second (data).

Both dataframes include the particular SKU_id's. I want to find a way to look at what the SKU_id is in the second dataframe (data) and then add the corresponding name from the first dataframe (df)

The output I am looking for is something like this:

       Store_id      SKU_id  WEEK  Units_Sold  QTY  PRICE Promotion  Product Name
20462  5         1820000016  166   1           1    3.79   NaN       BUDWEISER BEER LONG  
20463  5         1820000016  167   3           1    3.79   NaN       BUDWEISER BEER LONG  
20464  5         1820000016  168   6           1    3.79   NaN       BUDWEISER BEER LONG  
20465  5         1820000016  169   2           1    3.79   NaN       BUDWEISER BEER LONG  
20466  5         1820000016  170   1           1    3.79   NaN       BUDWEISER BEER LONG

The problem is the two dataframes are of different sizes and I have no clue how to code a way to look at the SKU_id and add the corresponding name. Any suggestions? Thanks in advance

pandas dataframe python
4 Answers

Here is an example where df1 dataframe from your question and df2 is the second that you have provided

for i,val in enumerate(df2['SKU_id']):
    df2.loc[i,"Product"]=df1.loc[df['SKU_id'] ==val, 'Product Name'].iloc[0]
df2.head()

Dataframe1 that I used as an example:

    SKU_id      Product Name
0   7336012000  OLD STYLE BEER
1   1820000016  BUDWEISER BEER LONG

Dataframe2 that I used as an example:

 Store_id   SKU_id
0   5   1820000016
1   5   1820000016
2   5   7336012000

This will give you something like:

 Store_id   SKU_id  Product
0   5   1820000016  BUDWEISER BEER LONG
1   5   1820000016  BUDWEISER BEER LONG
2   5   7336012000  OLD STYLE BEER
user_147346130

Looks like you need to do two things here, but not necessarily in this order:

  1. Merge the two tables
  2. Filter on the SKU

So you need something like this.

data.merge(df, how="left").query('SKU_id == 12345')
user_166429610

There is a builtin function in pandas to do exactly what you're looking for:

.merge()

data = data.merge(df, how = 'left', on = 'SKU_id')
user_157872350

new_df = pd.merge(data, df, how = 'left', on = 'SKU_id') Try this this may help

user_143100380
Related