Substituting substrings with other column substring in pandas

user_16588028 0
Viewed 70

I have a dataframe with some template strings and corresponding string variables to substitute. For example, given:

template,variable
"{color} shirt in {size}", "blue,medium"
"{capacity} bottle in {color}", "24oz,teal"
"{megapixel}mp camera", "24.1"

I'd like to produce the following:

"blue shirt in medium"
"24oz bottle in teal"
"24.1mp camera"

It's guaranteed that the number of template substrings in the first column will equal the number of variables in the string in the second column. The format for the strings is consistent to the examples above.

My first thought was to create a multindex dataframe using extractall and then joining :

templates = df['template'].str.extractall('({\w+\})')
variables = df['variable'].str.extractall('(\w+)')
multi_df = templates.join(variables, how='inner')

But I couldn't quite figure out where to go from there. Or is there perhaps an easier way?

pandas python regex
4 Answers

Use string.Formatter to extract variables from template column and build the dictionary to be able substitution.

>>> df
                       template        value  # I modified your column name
0       {color} shirt in {size}  blue,medium
1  {capacity} bottle in {color}    24oz,teal
2          {megapixel}mp camera         24.1

from string import Formatter

def extract_vars(s):
    return tuple(fn for _, fn, _, _ in Formatter().parse(s) if fn is not None)

df['variable'] = df['template'].apply(extract_vars)
df['value'] = df['value'].str.split(',')
df['combined'] = df.apply(lambda x: dict(zip(x['variable'], x['value'])), axis=1)

At this point, your dataframe looks like:

                       template           value           variable                               combined
0       {color} shirt in {size}  [blue, medium]      [color, size]    {'color': 'blue', 'size': 'medium'}
1  {capacity} bottle in {color}    [24oz, teal]  [capacity, color]  {'capacity': '24oz', 'color': 'teal'}
2          {megapixel}mp camera          [24.1]        [megapixel]                  {'megapixel': '24.1'}

Finally, evaluate your strings:

>>> df.apply(lambda x: x['template'].format(**x['combined']), axis=1)
0    blue shirt in medium
1     24oz bottle in teal
2           24.1mp camera
dtype: object
user_152399510

I'd suggest a method to apply on each row that

  • extract key for formatting
  • replace using the keys, and the values
def formatter(row):
    keys = [k.strip("{}") for k in re.findall(r'({\w+})', row['template'])]
    return row['template'].format(**dict(zip(keys, row['variable'].split(","))))


values = [
    ["{color} shirt in {size}", "blue,medium"],
    ["{capacity} bottle in {color}", "24oz,teal"],
    ["{megapixel}mp camera", "24.1"]
]
df = pd.DataFrame(values, columns=['template', 'variable'])
df['result'] = df.apply(formatter, axis=1)
print(df)

Ouptuts

                       template     variable                result
0       {color} shirt in {size}  blue,medium  blue shirt in medium
1  {capacity} bottle in {color}    24oz,teal   24oz bottle in teal
2          {megapixel}mp camera         24.1         24.1mp camera
user_72126860

You can use defaultdict + str.format_map:

from collections import defaultdict

print(
    df.apply(
        lambda x: x["template"].format_map(
            defaultdict(lambda i=iter(x["variable"].split(",")): next(i))
        ),
        axis=1,
    )
)

Prints:

0    blue shirt in medium
1     24oz bottle in teal
2           24.1mp camera
dtype: object
user_100359850

Maybe relatively simpler codes:

# replace e.g {color} by simply {}
# As the substitution is positional, we can skip the hassle to build mapping of format keys to substitution values.
df['template1'] = df['template'].str.replace(r'\{.*?\}', '{}', regex=True)

# apply on each row with code similar to '{} shirt in {}'.format('blue', 'medium')
df['result'] = df.apply(lambda x: x['template1'].format(*x['variable'].split(',')), axis=1)

# remove temporary working column template1
df = df.drop('template1', axis=1)

Result:

print(df)

                       template     variable                result
0       {color} shirt in {size}  blue,medium  blue shirt in medium
1  {capacity} bottle in {color}    24oz,teal   24oz bottle in teal
2          {megapixel}mp camera         24.1         24.1mp camera
user_150706970
Related