Python Forum
(Solved) Converting Dollar Amount Str to Int with Pandas
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
(Solved) Converting Dollar Amount Str to Int with Pandas
#1
(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! Undecided
Reply
#2
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 Think

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 Smile
Reply
#3
(Accidental post/duplicate.)
Reply
#4
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
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  [solved] duplication in pandas BSDevo 0 523 Sep-06-2023, 10:47 PM
Last Post: BSDevo
  Pandas converting date to epoch randor 2 3,869 Jul-16-2019, 02:41 AM
Last Post: scidam
  Converting string the pandas dataframe chrismc 0 2,336 Jan-24-2019, 11:07 AM
Last Post: chrismc

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020