Given that this is the top Google result when searching for "Pandas replace is not working" I'd like to also mention that:
replace does full replacement searches, unless you turn on the regex switch. Use regex=True, and it should perform partial replacements as well.
This took me 30 minutes to find out, so hopefully I've saved the next person 30 minutes.
Answer from Reddspark on Stack OverflowThis is genuinely driving me crazy.
I have a data frame of unit prices in string format i'm trying to get them to a float
item_df['Unit Price'] = item_df['Unit Price'].replace('$','')and all the '$' are still there.
THEN when I do this:
item_df['Unit Price'][1] = item_df['Unit Price'][1].replace('$','')The '$' is gone from that index ಠ_ಠ. What the hell is going on?? Am I taking crazy pills or missing some fundamental concept?
Any help would be much appreciated.
Thanks,
Given that this is the top Google result when searching for "Pandas replace is not working" I'd like to also mention that:
replace does full replacement searches, unless you turn on the regex switch. Use regex=True, and it should perform partial replacements as well.
This took me 30 minutes to find out, so hopefully I've saved the next person 30 minutes.
You need to assign back
df = df.replace('white', np.nan)
or pass param inplace=True:
In [50]:
d = {'color' : pd.Series(['white', 'blue', 'orange']),
'second_color': pd.Series(['white', 'black', 'blue']),
'value' : pd.Series([1., 2., 3.])}
df = pd.DataFrame(d)
df.replace('white', np.nan, inplace=True)
df
Out[50]:
color second_color value
0 NaN NaN 1.0
1 blue black 2.0
2 orange blue 3.0
Most pandas ops return a copy and most have param inplace which is usually defaulted to False
I am trying to clean my twitter_handle column where some of the names have ?langen at the end of them. This is what I tried...
updated['twitter_handle'] = updated['twitter_handle'].str.replace('?langen', '', regex=True)re.error: nothing to repeat at position 0
Hello Very new to pandas. Trying to replace ampersand in my excel file
Why did I have to add regex=True to get this to work. It wouldn’t update otherwise.
df = df.replace(‘%26’ , ‘&’ , regex = True)
df['funding_total_usd'].replace({'-',0})Does not work.
I have a dataframe:
{'country': {0: 'Afghanistan?*', 1: 'Albania?*'},
'region': {0: 'Asia', 1: 'Europe'},
'subregion': {0: 'Southern Asia', 1: 'Southern Europe'},
'rate_per_1000': {0: 6.7, 1: 2.1},
'count': {0: '2,474', 1: '61'},
'year': {0: 2018, 1: 2020},
'source': {0: 'NSO', 1: 'NSO'}}
country region subregion rate_per_1000 count year source
0 Afghanistan?* Asia Southern Asia 6.7 2,474 2018 NSO
1 Albania?* Europe Southern Europe 2.1 61 2020 NSOThere are multiple bad characters here that I want to get rid of. I made a short function for .apply() to get rid of them, however I am looping over a defined list of bad characters. This gives a bad code smell to me, I think this operation could be more vectorized in some way. This is what I've tried:
bad_chars = ['?', '*', ',']
def string_cleaner(col):
if col.dtype == 'object':
for char in bad_chars:
col = col.str.replace(f'{char}', '')
return col
homicide_by_country = homicide_by_country.apply(string_cleaner)
homicide_by_country
country region subregion rate_per_1000 count year source
0 Afghanistan Asia Southern Asia None 2474 None NSO
1 Albania Europe Southern Europe None 61 None NSOMy desired outcome is a more pythonic/pandonic technique for accomplishing the same outcome.
You may notice for some reason my rate_per_1000 columns goes blank. I haven't troubleshot that problem yet but if you spot something obvious I'm all ears.
Dears
I do have a dataframe:
data1 = [['aaa567'], ['bbb333'], ['ccc23432']] df1 = pd.DataFrame(data1, columns=['text'])
I do have a second dataframe
data2 = [['peter', 567], ['paul', 333], ['mary', 23432]] df2 = pd.DataFrame(data2, columns=['name', 'number'])
text
0 aaa567
1 bbb333
2 ccc23432
name number
0 peter 567
1 paul 333
2 mary 23432
I now want to replace the certain strings from df1 with the coresponding 'name' of df2.
the result should look like this:
text
0 aaapeter
1 bbbpaul
2 cccmary
do you have any hints how to do that? I assume this could be done with 'iloc', but i am clueless ...
tnx for any help :)
I'm trying to remove the special characters '$' and ','. Can the code below be shortened so I can perform the operation just once?
df['ListPricePerUnit'] = df['ListPricePerUnit'].str.replace('$','').str.replace(',','')
df['PurchasePricePerUnit'] = df['PurchasePricePerUnit'].str.replace('$','').str.replace(',','')Full code: https://gist.github.com/VictorLG98/0712146c957921b97bc1e5bf3f41c256
The code:
participants = []
for puid in match_detail['metadata']['participants']:
participants.append(self.watcher.summoner.by_puuid(self.my_region, puid)['name'])
par = [i for i in participants]
champ1 = [i['championName'] for i in match_detail['info']['participants']]
role1 = [i['individualPosition'] for i in match_detail['info']['participants']]
kills = [i['kills'] for i in match_detail['info']['participants']]
deaths1 = [i['deaths'] for i in match_detail['info']['participants']]
assists1 = [i['assists'] for i in match_detail['info']['participants']]
wards1 = [i['wardsPlaced'] for i in match_detail['info']['participants']]
gold = [i['goldEarned'] for i in match_detail['info']['participants']]
minions = [i['totalMinionsKilled'] for i in match_detail['info']['participants']]
neu_minions = [i['neutralMinionsKilled'] for i in match_detail['info']['participants']]
suma1 = [x + y for x, y in zip(minions, neu_minions)]
dano_total = [i['totalDamageDealtToChampions'] for i in match_detail['info']['participants']]
dano_recibido = [i['totalDamageTaken'] for i in match_detail['info']['participants']]
win = [i['win'] for i in match_detail['info']['participants']]
data = {
'Invocador': par,
'Champion': champ1,
'Role': role1,
'Kills': kills,
'Deaths': deaths1,
'Assists': assists1,
'Wards': wards1,
'Gold Earned': gold,
'Farm': suma1,
'Daño total': dano_total,
'Daño recibido': dano_recibido,
'Win': win
}
df = pd.DataFrame(data)
df['Role'].replace(to_replace=dict(UTILITY='SUPPORT'), inplace=True)
df['Win'].replace({'True': 'VICTORY', 'False': 'DEFEAT'}, inplace=True)The first replace is working fine but the last is not working, i don't know why.
Result
Try this if you decide to use pandas:
readFile = pd.read_csv("C:/Users/siddhesh.kalgaonkar/Desktop/data01.txt",header=None)
readFile.columns = ['IP']
readFile['IP'] = readFile['IP'].replace(regex='((?<=[0-9])[0-9]|(?<=\.)[0-9])',value='X')
print(readFile)
and this without pandas:
readFile = open("C:/Users/siddhesh.kalgaonkar/Desktop/data01.txt","r")
for line in readFile:
lines = line.strip()
finalline = re.sub(pattern='((?<=[0-9])[0-9]|(?<=\.)[0-9])',repl='X',string=lines)
print(finalline)
(?<=[0-9])[0-9] this part matches if the current position in the string is a digit and is preceded by a digit.
| or
(?<=\.)[0-9]) this part matches if the current position in the string is is a digit and is preceded by a period.
This is pure(almost) python:
list(map(lambda x: x[0] + '.'.join(['X' * len(c) for c in x[1:].split('.')]), my_df['IP']))
Explanation:
- Use the map to iterate over each row in my_df['IP'] column.
- Per each IP value, split into first char and others using the x[0], x[1:]
notation.
- Get each part in x[1:] using the split method.
- For each part get its length and accordingly create a string made of X's.
- Rejoin this X's string into one string with '.' between them.
If using python2 you dont need the list.
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.
Essentially, I'm reading a csv file that has ' ' to indicate no value, which I want to turn into NaN. The problem is, it works exactly as expected on a minimum reproducable, but not on the real data and I don't understand why.
Minimum reproducable:
import pandas as pd
import numpy as np
df = pd.DataFrame({'Pers_Nr': ['01', ' ', '03'],
'Name': ['George', 'Joe', ' ']})
print(df)
new_df = df.replace(' ', np.nan, regex=True)
print(new_df)output as expected:
Pers_Nr Name 0 01 George 1 Joe 2 03 Pers_Nr Name 0 01 George 1 NaN Joe 2 03 NaN
But then on real data, the same line of code produces this:
Pers_Nr Name St_Kl Faktor AN 0 00001 Youssef. R 1 0 1 00002 G. Frederik Leonard 4 2 00003 O. Dietmar 4 Pers_Nr Name St_Kl Faktor AN 0 00001 NaN 1 NaN 0 1 00002 NaN NaN NaN 4 2 00003 NaN NaN NaN 4
and I just don't get it. So thanks so much for any help! let me know if you need more info
RESOLVED: I didn't realize I was using a pandas replace() function. To fix this, I had to add an argument of inplace=True in the pandas replace() function in order to modify the dataframe inside of my custom function called "clean_df".
Hi All,
I have a program that merges excel files together into a new excel file and before the actual merge I have a custom function that replaces any commas with an empty character. When I run this program it generates the right file, but when I do a CTRL+F in excel I see a bunch of commas in the data.
Which means the replace() function is not working for some reason in my custom function called "clean_df". I'm at a loss for this since I have no error, any thoughts on what I'm doing wrong?
If it helps, I am practicing to not return any values from a function. I just want the custom function clean_df to remove the commas on each file in the for loop.
Below is my code:
import pandas as pd, glob
def merge():
# replace filepath to the folder where your input files are stored
path = r"C:\Users\me\OneDrive\Documents\Data
Conversion\Test Merge"
# create list of input excel files for merging
file_list = glob.glob(path + "/*.xlsx")
# create empty dataframe list
excel_list = []
# loop through all the files in the dataframe list and read them as excel
for file in file_list:
df = pd.read_excel(file)
# remove all commas from file
clean_df(df)
excel_list.append(df)
# merge all excel files
excel_merged = pd.concat(excel_list, ignore_index=True)
# replace filepath to the output folder you want to store the newly merged file
output_path = r"C:\Users\me\OneDrive\Documents\Data
Conversion\Test Merge"
# write new excel file
excel_merged.to_excel(
f"{output_path}\mergedfile.xlsx", index=False
)
def clean_df(df):
df.replace(",", "", regex=True)
merge()