Replace part of string in a column if string is at a certain position in Pandas
python - Replacing Substring with another string from column Pandas - Stack Overflow
python - Pandas DataFrame - replace substring in column if a substring exists - Stack Overflow
A most efficient way of using conditional replacement in pandas
I have this sample column:
Reference A3V345 1/2 763SDDY 2/2 645BRW0 1OF4 645BRW0 2OF4 GRYUGBM-A 67AQSD-B EW21Z31 4GH5477BM 1/3
I would like to create a new column where the result is the same value in the reference column with "-B, -A, 2OF4, 1OF4, 2/2, 1/2" removed or replaced with ""; the desired output would be this:
new_value A3V345 763SDDY 645BRW0 645BRW0 GRYUGBM 67AQSD EW21Z31 4GH5477BM
So far I have attempted at least three different things with different error messages in the output:
1. str.endswith and concatenation of strings to identify position and somehow slice value after True value in cell
output_table_1["new_value"] = output_table_1["Reference"].str.endswith((" 1/2", " 2/2", " 1/3", "1OF4", "2OF4", "-A", "-B"), na = False)
output_table_1["concat"] = output_table_1["Reference"] + str(output_table_1["new_value"])
output_table_1The output is a long text I didn't expect to see, for instance, for 4GH5477BM this is the result:
4GH5477BM 1/30 False\n1 False\n2 False\n3 False\n4 False\n ... Cell expanded: 4GH5477BM 1/30 False\n1 False\n2 False\n3 False\n4 False\n ... \n1014 False\n1015 False\n1016 False\n1017 True\n1018 True\nName: new_value, Length: 1019, dtype: bool
2. str.replace if condition (only one parameter as example) applies
if output_table_1[output_table_1["Reference"].str.endswith(" 1/2", na=False)]:
output_table_1["new_value"] = output_table_1["Reference"].apply(lambda x: x.replace(" 1/2", ""))
else:
output_table_1["new_value"] = output_table_1["Reference"]
Output:
ValueError: The truth value of a DataFrame is ambiguous. Use a.empty, a.bool(), a.item(), a.any() or a.all().3. str.find to check position at the end of string
positionA = output_table_1["Reference"].str.len() - 4
positionB = output_table_1["Reference"].str.len()
output_table_1["set_value"] = output_table_1["Reference"].str.find(" 1/2", start = positionA, end = positionB)The output is NaN for all the cells in that column, even though I believe it should return -1 if there was no match when searching the substring. Even if it worked, I still would be limited as it only accepts one string to search. As in the first try, I wanted to return the index where the occurrence happened and then use slicing to delete just before the searched substring.
I still lack skill in Python, more so in Pandas. Any help will be appreciated.
Use the same idea as yours (apply(), replace()), just modify a bit about using replace().
new_df["String"] = new_df.apply(
lambda row: row["String"].replace("id", row["int_id"]) if row["type"] == 1 else row["String"].replace("id", row["ext_id"]),
axis=1
)
output:
Type String ext_id int_id 0 1 UK2820BC 2393 2820 1 1 UK1068BC 4816 1068 2 0 UK4166BC 4166 3625 3 0 UK2803BC 2803 1006 4 1 UK2697BC 1189 2697
Instead of apply, we could use str.split + np.where to replace values according to "Type" value:
tmp = df['String'].str.split('id', expand=True)
df['String'] = tmp[0] + np.where(df['Type'].astype(bool), df['int_id'].astype(str), df['ext_id'].astype(str)) + tmp[1]
Output:
Type String ext_id int_id
0 1 UK2820BC 2393 2820
1 1 UK1068BC 4816 1068
2 0 UK4166BC 4166 3625
3 0 UK2803BC 2803 1006
4 1 UK2697BC 1189 2697
You can use series.replace with replacement dictionary
repl = {fr'(?i){k}': v for k, v in lookup}
df.columns = df.columns.to_series().replace(repl, regex=True)
p1_param_one1 p2_param_one2 p3_param_one3 p4_param_two1 p5_param_two2 p6_param_three1
0 1 45 76 4321 3 6
1 2 3 5 6 2 5
You can use a regex and str.replace:
dic = dict(lookup)
pat = '|'.join(dic)
df.columns = df.columns.str.replace(pat, lambda x: dic.get(x.group(0)), regex=True)
output (df.columns):
Index(['p1_param_one1', 'p2_param_one2', 'p3_param_one3', 'p4_param_two1',
'p5_param_two2', 'p6_param_three1'],
dtype='object')