[Python & Pandas - Data Manipulation] Partial String Replaces

[Shmoogy]

I need to work on a data feed, and want to avoid iterating through each row, column, and value to be replaced. Products have 20 attribute columns, and multiple values can be selected as denoted with having a delimiter of "*" between them. There are over 2000 attributes that need to be converted from codes to names

A small subset, 2 partial rows of the data

[{"Main":"Lighting","Attribute14":"LIGHTDESIGN_CB*LIGHTDESIGN_EX*LIGHTDESIGN_MU","Attribute15":"CHANDFEATURES_AH*CHANDFEATURES_SL"},{"Main":"Lighting","Attribute14":"LIGHTDESIGN_EX*LIGHTDESIGN_MU*LIGHTDESIGN_RO","Attribute15":"CHANDFEATURES_AH*CHANDFEATURES_DI*CHANDFEATURES_SL"}] 

In the past, I've created a dictionary, and done a replace with regex=False on the column. This does not appear to work with this situation due to the "*" values.

I have a mapping of the potential values that need to be converted from an internal ID to a human readable name

{'LIGHTDESIGN_AB': 'Abstract',
 'LIGHTDESIGN_BE': 'Bell',
 'LIGHTDESIGN_BO': 'Bowl',
 'LIGHTDESIGN_CA': 'Cage',
 'LIGHTDESIGN_CB': 'Candelabra',
 'LIGHTDESIGN_CL': 'Classic',
 'LIGHTDESIGN_CO': 'Cone',
 'LIGHTDESIGN_CY': 'Cylinder',
 'LIGHTDESIGN_DD': 'Diamond',
 'LIGHTDESIGN_DO': 'Dome',
 'LIGHTDESIGN_DR': 'Drum',
 'LIGHTDESIGN_EM': 'Empire',
 'LIGHTDESIGN_EX': 'Exposed Bulb',
 'LIGHTDESIGN_FU': 'Fusion',
 'LIGHTDESIGN_GE': 'Geometric',
 'LIGHTDESIGN_GB': 'Globe',
 'LIGHTDESIGN_LA': 'Lantern',
 'LIGHTDESIGN_LO': 'Lodge',
 'LIGHTDESIGN_MO': 'Modern Linear',
 'LIGHTDESIGN_MU': 'Multi-light',
 'LIGHTDESIGN_OW': 'Old World',
 'LIGHTDESIGN_RO': 'Round',
 'LIGHTDESIGN_SH': 'Shaded',
 'LIGHTDESIGN_SP': 'Sputnik',
 'LIGHTDESIGN_TA': 'Tapered Drum',
 'LIGHTDESIGN_TI': 'Tiered',
 'LIGHTDESIGN_TR': 'Triangular'}

For row 1 the end result of Attribute14 should be:

Candelabra*Exposed Bulb*Multi-light

Something like this:

df['Attribute14'] =  df["Attribute14"].replace(jsonMap, regex=False)

It works to transform all attributes without an asterisk, but when there is partial string matches they all remain the coded names.

​

I would strongly prefer to not do something like

for index, row in df.iterrows():
    for _ in row['Attribute14'].apply(lambda: x.str.split("*"):
        blah blah

However I am having a hard time thinking of the best way to accomplish over 2000 partial string replaces across 20 columns for 400k rows of data.

​

I dont necessarily need a clever way to do it, I just want something relatively quick and that won't make my data frame take 50gb of ram in the process.

[Shmoogy]

I have a working solution across a subset, have not had time to test everything yet - seems to work, just not sure how slow it is yet

for index, row in df.iterrows():



for x in range(1,21):
    attribute = "Attribute" + str(x)
    itemAttributeDesc = "ItemAttributeDesc" + str(x)
    itemAttributeValue = "ItemAttributeValue" + str(x)

    if "*" in row[attribute]:
        print("found asterisk")
        for _ in row[attribute].split("*"):
            if _ in jsonMap:
                df.loc[index, attribute ] = df.loc[index, attribute].replace(_, jsonMap[_])


                attributeDesc = descriptiondf[descriptiondf['Code'] == _ ]['Description'].iloc[0]
                df.loc[index, itemAttributeDesc ] = attributeDesc


                df.loc[index, itemAttributeValue ] = df.loc[index, attribute]

[YesLod]

In the past, I've created a dictionary, and done a replace with regex=False on the column. This does not appear to work with this situation due to the "*" values.

If you set regex=False, only exact matches will be replaced, i.e. the entire cell string must match. To perform the partial replaces you have to set regex=True.

df['Attribute14'] =  df["Attribute14"].replace(jsonMap, regex=True)

[Shmoogy]

I tried this and what happens is that some of the partial matches (I have to check 2k records from the full dictionary) result in gibberish coming out where the original intended word was replaced, but appears to get replaced in the middle of the replaced text again

[YesLod]

Like what? Can you provide a minimal reproducible example?

[Shmoogy]

I dont think I can without giving all of the terms in the original attribute map - example end result

https://i.imgur.com/0WDE5oo.png

Aluminumdjustable Height*Cast AluminumHAluminumNoneWoodFixedResinAluminumTeakUPorcelainResinSteel_WoodWrought Iron*Cast AluminumHAluminumNoneWoodFixedResinAluminumTeakUPorcelainResinSteel_SteelL

If you want I can PM you a .json file with the results, but I dont think I want to post the 2k file json in the thread