dictionary keys to replace strings in pandas dataframe column with dictionary values and perform evaluate
We first
explode
your string to rows seperated by a comma, using this function.Then we
split
the values by a whitespace (' '
) to seperate columns.Finally we
map
your dictionary to the letters and do agroupby.sum
:
new = explode_str(df.dropna(), 'col1', ',')['col1'].str.strip().str.split(' ', expand=True).append(df[df['col1'].isna()])
s = new[1].map(di) * pd.to_numeric(new[0])
df['result'] = s.groupby(s.index).sum()
Output
col1 result
0 3 a, 3 ab, 1 b 37.5
1 4 a, 4 ab, 1 b, 1 d 50.5
2 NaN 0.0
Function used from linked answer:
def explode_str(df, col, sep):
s = df[col]
i = np.arange(len(s)).repeat(s.str.count(sep) + 1)
return df.iloc[i].assign(**{col: sep.join(s).split(sep)})
Here is on way seem over kill
df['col1'].str.split(', ',expand=True).replace({' ':'*','np.nan':'0'},regex=True).\
stack().apply(lambda x : eval(x,di)).sum(level=0)
Out[884]:
0 37.5
1 50.5
2 0.0
dtype: float64
comprehension
from functools import reduce
from operator import mul
def m(x): return di.get(x, x)
df.assign(result=[
sum(
reduce(mul, map(float, map(m, s.split())))
for s in row.split(', ')
) for row in df.col1
])
col1 result
0 3 a, 3 ab, 1 b 37.5
1 4 a, 4 ab, 1 b, 1 d 50.5
2 np.nan 0.0