The issue with some of the other answers is that they don't work with all Dataframes, only with Series, or Dataframes that can be implicitly converted to a Series. I understand this is because the .str construct exists in the Series class, but not in the Dataframe class.
To work with Dataframes, you can make your regular expression case insensitive with the (?i) extension. I don't believe this is available in all flavors of RegEx but it works with Pandas.
d = {'a':['test', 'Test', 'cat'], 'b':['CAT', 'dog', 'Cat']}
df = pd.DataFrame(data=d)
a b
0 test CAT
1 Test dog
2 cat Cat
Then use replace as you normally would but with the (?i) extension:
df.replace('(?i)cat', 'MONKEY', regex=True)
a b
0 test MONKEY
1 Test dog
2 MONKEY MONKEY
Answer from geekly on Stack OverflowThe issue with some of the other answers is that they don't work with all Dataframes, only with Series, or Dataframes that can be implicitly converted to a Series. I understand this is because the .str construct exists in the Series class, but not in the Dataframe class.
To work with Dataframes, you can make your regular expression case insensitive with the (?i) extension. I don't believe this is available in all flavors of RegEx but it works with Pandas.
d = {'a':['test', 'Test', 'cat'], 'b':['CAT', 'dog', 'Cat']}
df = pd.DataFrame(data=d)
a b
0 test CAT
1 Test dog
2 cat Cat
Then use replace as you normally would but with the (?i) extension:
df.replace('(?i)cat', 'MONKEY', regex=True)
a b
0 test MONKEY
1 Test dog
2 MONKEY MONKEY
I think need convert to lower and then replace by condition with isin:
d = {'a':['test', 'Test', 'cat', 'CAT', 'dog', 'Cat']}
df = pd.DataFrame(data=d)
m = df['a'].str.lower().isin(['cat','test'])
df.loc[m, 'a'] = 'baby'
print (df)
a
0 baby
1 baby
2 baby
3 baby
4 dog
5 baby
Another solution:
df['b'] = df['a'].str.replace('test', 'baby', flags=re.I)
print (df)
a b
0 test baby
1 Test baby
2 cat cat
3 CAT CAT
4 dog dog
5 Cat Cat
python - pandas "case insensitive" in a string or "case ignore" - Stack Overflow
Any way to have pandas remove_duplicates and merge to be case insensitive without changing the original values?
pandas - Python: replace case insensitive flag doesn't work - Stack Overflow
python - What's the easiest way to do a case-insensitive string replacement in Pandas? - Stack Overflow
Series.str.contains has a case parameter that is True by default. Set it to False to do a case insensitive match.
df2 = df1['company_name'].str.contains("apple", na=False, case=False)
If you want to do the exact match and with strip the search on both sides with case ignore,
df[df['Asset Name'].str.strip().str.match('searchstring'.strip(), case=False)]
Howdy!
I have a code that takes a dataframe and generates a Dimension Table out of its columns. A dimension table is a dataframe that contains only distinct values and an associated ID. This table is then merged back to the original table so it replaces the original value by its ID.
I'm having an issue where df.remove_duplicates() and pd.merge() considers the same string in different capitalizations different things ("A" and "a" are different). When loading this data to PowerBI, PowerBI considers them the same ("A" and "a" are equivalent, and considered as duplicates)
Since I'm dealing with a large table I'm bound to have this happen on multiple columns. Is there any way that I can make python ignore capitalization when performing a remove_duplicates or a merge? That way I'd have either capitalization (either "A" or "a" is fine) but only for the cases in which there are duplicates with different capitalizations.
I'd like to avoid making changes to the original values if possible, since my current solution is to just make everything uppercase and I'd like to avoid that.