CODEWITHSUNDEEP
pythonpandasdataframe
Mark K
Mark K

Reputation: 9348

Pandas read (Excel) cells, and return looked up values

A column in Excel file shows the short-form of some descriptions. They are one-to-one relationship in a dictionary.

I want to look them up each, and write the looked up values to a new file, side by side with the short forms.

Xlrd and xlwt are basic so I used them:

product_dict = {
"082" : "Specified brand(s)",
"035" : "Well known brand",
"069" : "Brandless ",
"054" : "Good middle class restaurant",
"062" : "Modest class restaurant"}

import xlwt, xlrd

workbook = xlrd.open_workbook("C:\\file.xlsx")
old_sheet = workbook.sheet_by_index(0)

book = xlwt.Workbook(encoding='cp1252', style_compression = 0)
sheet = book.add_sheet('Sheet1', cell_overwrite_ok = True)

for row_index in range(1, old_sheet.nrows):
    new_list = []   
    Cell_a = str(old_sheet.cell(row_index, 0).value)

    for each in Cell_a.split(", "):

        new_list.append(product_dict[each])

    sheet.write(row_index, 0, Cell_a)
    sheet.write(row_index, 1, "; ".join(new_list))

book.save("C:\\files-1.xls")

It looks ok. But I want to learn the Pandas way to do the same.

How does the Pandas way looked like, in addition to below? Thank you.

data = {'Code': ["082","069","054"]}
df = pd.DataFrame(data)

enter image description here

Upvotes: 0

Views: 77

Answers (2)

anky
anky

Reputation: 75080

With the data given, I would first map the dictionary to a new column, then aggregate with ','.join:

final=df.assign(New=df.Code.map(product_dict)).agg(','.join).to_frame().T

          Code                                                New
0  082,069,054  Specified brand(s),Brandless ,Good middle clas...

Where:

print(df.assign(New=df.Code.map(product_dict)))

is:

  Code                           New
0  082            Specified brand(s)
1  069                    Brandless 
2  054  Good middle class restaurant

Upvotes: 1

Jeremy H
Jeremy H

Reputation: 423

If you have a lookup dictionary in the form of a python dictionary, you can do this:

import pandas as pd

lookup_dict = {'1': 'item_1', '2':'item_2'}

# Create example dataframe
df_to_process = pd.DataFrame()
df_to_process['code'] = ['1, 2', '1', '2']

# Use .apply and lambda function to split 'code' and then do a lookup on each item
df_to_process['code_items'] = df_to_process['code'].apply(lambda x: '; '.join([lookup_dict[code] for code in x.replace(' ','').split(',')]))

With your examples:

import pandas as pd

product_dict = {
"082" : "Specified brand(s)",
"035" : "Well known brand",
"069" : "Brandless ",
"054" : "Good middle class restaurant",
"062" : "Modest class restaurant"}

data = {'Code': ["082","069","054"]}
df = pd.DataFrame(data)

df['code_items'] = df['Code'].apply(lambda x: '; '.join([product_dict[code] for code in x.replace(' ','').split(',')]))

Upvotes: 1

Related Questions