+- Python Forum (https://python-forum.io)
+-- Forum: Python Coding (https://python-forum.io/forum-7.html)
+--- Forum: Data Science (https://python-forum.io/forum-44.html)
+--- Thread: (Solved) Converting Dollar Amount Str to Int with Pandas (/thread-31029.html)
(Solved) Converting Dollar Amount Str to Int with Pandas - calvinsomething - Nov-19-2020
(SOLVED)
I've found similar problems to what I am having on stackoverflow, but nothing that solves what I'm dealing with.
I'm trying to remove the Euro sign as well as convert M and K amounts to 1000000 and 1000 respectively. Parts of this code seems to work individually, but I have to execute it all at once so that the single function can determine whether the amount has an 'M' or 'K' before removing it from the value, so that it can convert it to a float afterwards and still add the same amount of zeroes.
Here is the latest iteration of what I'm trying to do in code:
Quote:def fix(x):
if 'M' in x:
return float(x.replace('[€M]', '', regex=True))*10000000
if 'K' in x:
return float(x.replace('[€K]', '', regex=True))*10000
df1 = df[['Name', 'Value', 'Wage']]
df1.apply(fix)
df1.head(6)
Thank you for anyone who can help me with this!
RE: Converting Dollar Amount Str to Int with Pandas - calvinsomething - Nov-19-2020
Changed the title to be more specific. I have an update if anyone else stumbles across this searching for an answer.
First I'll detail better what is still NOT working:
def fix(x):
x.replace('€', '')
if 'M' in x:
x.replace('M', '')
return x
if 'K' in x:
x.replace('K', '')
return x
df1 = pd.DataFrame(df, columns=['Name', 'Value', 'Wage'])
df1['Wage'] = df1['Wage'].apply(fix)
df1['Value'] = df1['Value'].apply(fix)
df1.head(6)
Here I have not added the conversion because this is not even working on its own. The head() command works and things are printed, but without the characters being replaced. However, if I write the same code with a lambda function, it works. Pandas is really annoying me
Now for what DOES work:
df1['Wage'] = df1['Wage'].replace('[€MK]', '', regex=True).astype(float)*1000
df1['Value'] = df1['Value'].replace('[€MK]', '', regex=True).astype(float)*1000000
I guess that is nicer to get it all done in two lines, and here we have some Pandas specific methods, which is nice to know, but I still don't understand what happened with the other variation.
If someone could explain why one of these works and the other does not it would be very helpful to my understanding and sanity
RE: Converting Dollar Amount Str to Int with Pandas - calvinsomething - Nov-19-2020
(Accidental post/duplicate.)
RE: Converting Dollar Amount Str to Int with Pandas - calvinsomething - Nov-19-2020
SOLVED!
The reason lambda was working and the full form function was not is because obviously the lambda function was in one line. The problem with the full function was that '.replace' is not an in place method and therefore needed to be on the return line or be preceded with 'x ='.
Hopefully this helps someone else one day :P