This emulates the input you are showing in your screenshot:
import pandas as pd
import numpy as np
df = pd.DataFrame({"type": ["a", "c", "d", "a", "b", "a", "a", "c"],
"tags": ["col_t1, col_red, large", np.nan, "col_t2, col_black, small",
"col_t4, large, col_yellow", "col_gold, col_fancy,", "col_t1, thick, col_k",
np.nan, "col_t3, fancy, red"],
"theme": ["t2", "t1", "t2", "t3", "t2", "t1", np.nan, "t3"]})
df.set_index(np.arange(1, len(df)+1), inplace=True)
print df
Output:
tags theme type
1 col_t1, col_red, large t2 a
2 NaN t1 c
3 col_t2, col_black, small t2 d
4 col_t4, large, col_yellow t3 a
5 col_gold, col_fancy, t2 b
6 col_t1, thick, col_k t1 a
7 NaN NaN a
8 col_t3, fancy, red t3 c
Code that produces the desired output:
prefix = "col_"
# Iterate over rows with non-empty theme
for row in df[df["theme"].notnull()].itertuples():
if pd.isnull(row.tags):
# Replace NaN in tags column with a single tag from theme column
df.loc[row.Index, "tags"] = prefix + row.theme
else:
# Extract existing tags with prefix
inferred_tags = [t.replace(prefix, "") for t in row.tags.split(",") if prefix in t]
if row.theme not in inferred_tags:
df.loc[row.Index, "tags"] = row.tags.rstrip(" ,") + ", " + prefix + row.theme
print df
Output:
tags theme type
1 col_t1, col_red, large, col_t2 t2 a
2 col_t1 t1 c
3 col_t2, col_black, small t2 d
4 col_t4, large, col_yellow, col_t3 t3 a
5 col_gold, col_fancy, col_t2 t2 b
6 col_t1, thick, col_k t1 a
7 NaN NaN a
8 col_t3, fancy, red t3 c
Hopefully this is what you are looking for. itertuples() is claimed to be faster for iterating over all rows than iterrows(). Also, keep in mind that I used numpy and specifically np.nan to emulate NaNs in your input, but if your data comes from csv, you won't need numpy.
--- UPDATE ---
As explained in the comments, the code should replace tags that match themes. Here is the updated solution:
prefix = "col_"
# Find all unique themes (notnull() excludes nan from the list)
themes = df[df["theme"].notnull()]["theme"].unique()
# Add prefex to all themes for comparison with tags; convert to set
prefixed_themes = set([prefix + t for t in themes])
# Iterate over rows with non-empty theme
for row in df[df["theme"].notnull()].itertuples():
if pd.isnull(row.tags):
# Replace NaN in tags column with a single tag from theme column
df.loc[row.Index, "tags"] = prefix + row.theme
else:
# Extract existing tags with prefix (do not remove prefix; remove all spaces)
inferred_tags = row.tags.replace(" ", "").split(",")
# Use sets to check if there is any intersection between tags and themes
if len(set(inferred_tags).intersection(prefixed_themes)) > 0:
# Iterate over inferred_tags to find and replace matches with themes
for idx, t in enumerate(inferred_tags):
if t in prefixed_themes:
inferred_tags[idx] = prefix + row.theme
df.loc[row.Index, "tags"] = ", ".join(inferred_tags)
else:
# In this case, add theme to tags (no replacement)
df.loc[row.Index, "tags"] = row.tags.rstrip(" ,") + ", " + prefix + row.theme
print df
Output:
tags theme type
1 col_t2, col_red, large t2 a
2 col_t1 t1 c
3 col_t2, col_black, small t2 d
4 col_t4, large, col_yellow, col_t3 t3 a
5 col_gold, col_fancy, col_t2 t2 b
6 col_t1, thick, col_k t1 a
7 NaN NaN a
8 col_t3, fancy, red t3 c
Notice that the code checks tags against all values present in the theme column (with added prefix); if a value (like t4) is not in the theme column, it is not considered a legal theme tag and therefore col_t4 in item 4 is not replaced during processing. If you need all col_t* to be replaced, you need to be specific about it. Hopefully, this is a useful solution and you can take it from here.
