Use data from excel sheet to replace values in dataframe

user_11330653 0
Viewed 79

I have a simple excel file with product names. First row is the category (A1: Water, A2: Sparkling, A3:Still, B1: Soft Drinks, B2: Coca Cola, B3: Orange Juice, B4:Lemonade etc.), each cell below is a different product. I would like to replace the cells of another dataframe (df1) with the product categories. For example, Coca Cola would become Soft Drinks. If the product is not in the excel it would not be replaced (ex. Cookie).

A1: Water, A2: Sparkling, A3:Still, B1: Soft Drinks, B2: Coca Cola, B3: Orange Juice, B4:Lemonade etc.

print(df1)

         Product  Quantity
0      Coca Cola  1234
1      Cookie     4
2      Still      333
3      Chips     88

Outcome:

print (df1)

       Product      Quantity
0      Soft Drinks   1234
1      Cookie        4
2      Water         333
3      Snacks        88
pandas dataframe python-3.x excel python
1 Answers

We create a lookup dictionary from the Excel data frame and use it to replace the product names with the categories (products not found in the dictionary are left unreplaced as requested):

df = pd.read_excel(r'c:\temp\product_names.xlsx')
df1 = pd.DataFrame({'Product': ['Coca Cola','Cookie','Still','Chips'], 'Quantity': [1234,4,333,88]})

lu = df.unstack().dropna().reset_index()
df1.Product.replace(dict(zip(lu.iloc[:,2],lu.iloc[:,0])),inplace=True)

Result:

       Product  Quantity
0  Soft Drinks      1234
1       Cookie         4
2        Water       333
3       Snacks        88

PS: This is the csv file to paste into your Excel sheet at A1 to reproduce the example: 

Water,Soft Drinks,Sandwich,Snacks
Sparkling,Coca Cola,Ham and cheese,Chips
Still,Orange Juice,Vegetarian,
,Lemonade,Club,
,,Spinach,
user_39443220
Related