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
replace substring in pandas data frame column - Stack Overflow
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')
use str.contains to create a boolean index to mask the series and then str.replace to replace your substring:
In [172]:
df = pd.DataFrame({'raw_parameter_name':['ABCD;MEAN', 'EFGH;MEAN', '1234;MEAN', 'sdasd;MEAT']})
df
Out[172]:
raw_parameter_name
0 ABCD;MEAN
1 EFGH;MEAN
2 1234;MEAN
3 sdasd;MEAT
In [173]:
df.loc[df['raw_parameter_name'].str.contains(';MEAN$'), 'raw_parameter_name'] = df['raw_parameter_name'].str.replace('MEAN', 'X-BAR')
df
Out[173]:
raw_parameter_name
0 ABCD;X-BAR
1 EFGH;X-BAR
2 1234;X-BAR
3 sdasd;MEAT
Here it matches where the substrin ';MEAN' exists the $ is a terminating symbol
The boolean mask looks like the following:
In [176]:
df['raw_parameter_name'].str.contains(';MEAN$')
Out[176]:
0 True
1 True
2 True
3 False
Name: raw_parameter_name, dtype: bool
Timings
For a 40,0000 row df using str.replace is faster than using apply:
In [183]:
import re
%timeit df['raw_parameter_name'].apply(lambda x: re.sub(';MEAN$',';X-BAR',x))
%timeit df['raw_parameter_name'].str.replace('MEAN', 'X-BAR')
1 loops, best of 3: 1.01 s per loop
1 loops, best of 3: 687 ms per loop
You can use regex module re for example:
import pandas as pd
import re
df = pd.DataFrame({"row_parameter_name":['abcd;MEAN','Dogg11;MEAN',';MEAN']})
Out[126]:
row_parameter_name
0 abcd;MEAN
1 Dogg11;MEAN
2 ;MEAN
df['row_parameter_name'] = df['row_parameter_name'].apply(lambda x: re.sub(';MEAN$',';X-BAR',x))
In [128]: df
Out[128]:
row_parameter_name
0 abcd;X-BAR
1 Dogg11;X-BAR
2 ;X-BAR