Pandas: Updating Column B value if A contains string

user_12239478 0
Viewed 238

I'm looking to create/update a new column, 'dept' if the text in column A contains a string. It's working without a forloop involved but when I try to iterate it is setting the default instead of the detected value.

Surely I shouldn't manually add the same line 171 times, I've scoured the internet and SO for possible hints and or solutions and can't seem to locate any good info.

Working Code:

df['dept'] = np.where(df.a.str.contains("PHYS"), "PHYS", "Unknown")

But when I try:

depts = ['PHYS', 'PSYCH']
for dept in depts:
    df['dept'] = np.where(df.a.str.contains(dept), dept, "Unknown")
    print(dept)

I get all "Unknowns" but properly prints out each dept. I've also tried to make sure dept is fed in as a string by explicitly stating dept = str(dept) to no avail.

Thanks in advance for any and all help. I feel like this is a simple issue that should be easily sorted but I'm experiencing a block.

pandas numpy python
3 Answers

We usually do

df['dept'] = df.a.str.findall('|'.join(depts)).str[0]
user_79645270

I prefer str.extract:

df['depth'] = df['a'].str.extract(f"({'|'.join(depts)})").fillna("Unknown")

Or:

df['depth'] = df['a'].str.extract('(' + '|'.join(depts) + ')').fillna("Unknown")

Both codes output:

>>> df
           a    depth
0  ewfefPHYS     PHYS
1  QWQiPSYCH    PSYCH
2      fwfew  Unknown
>>>
user_87083640

@U-12-Forward has a great solution if there is only supposed to be one new column entitled specifically with the string 'dept', not the value of each dept variable in the loop.

If the intent is to create a new column for each dept in depts, then remove the quotations around "dept" in the column indexer:

for dept in depts:
    df[dept] = np.where(df.a.str.contains(dept), dept, "Unknown")

The example is confusing because it is not clear whether there is supposed to be a new column for each dept (i.e, PHYS, PSYCH) because of the variable name.

This excerpt will not "work" because it would overwrite df['dept'] on the second assignment with something that is only a combination of 'PSYCH' and 'Unknown' (there would be no 'PHYS').

df['dept'] = np.where(df.a.str.contains("PHYS"), "PHYS", "Unknown")
df['dept'] = np.where(df.a.str.contains("PSYCH"), "PSYCH", "Unknown")

What you are describing would certainly happen if there are no strings in column a that contain the final element in depts because the result of the last np.where would be all False, therefore return a full series of 'Unknown'.

user_50199680
Related