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,
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)
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
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