Convert multiple string values (Python 3)

Question

Good Morning All!

This is my first time posting on this site after scouring for advice since I tried my hand at Python.

I'm working on pulling data from a sqlite database and inputting it into an Excel spreadsheet. I've learned how to pull the data, and get it written to a spreadsheet. The issue I'm having though, is one of the values in a table I'm pulling data from presents a dollar amount as a string. For example, $1000.00 is represented by 100000. At first glance it would appear to be $100000, but the application, for whatever reason, stores $1000.00 as 100000.

So my question: Is there a way I can convert these string values into a float, OR into another string to input the dollar sign, and decimal point to show the proper figure? This isn't a conversion for just one value either. It needs to be a blanket conversion, so that whenever I run the script it converts the data no matter what dollar amounts are presented.

So if the table contains values of 50000, 20000, 30000, 5000, 500, I need to be able to convert them into $500.00, $200.00, $300.00, $50.00, $5.00.

I hope that all makes sense and I appreciate any help anyone can give me!

forpas · Accepted Answer · 2020-08-14 15:42:35Z

1

You must divide the value by 100 to strip of the last 2 0s and then use the function printf() to format:

printf("$%.2f", columnname / 100)

Replace columnname with the name of your column.

So the result of:

select printf("$%.2f", 500 / 100)

is:

$5.00

See the demo.

answered Aug 14, 2020 at 15:42

forpas

165k10 gold badges51 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

SnakeDoc65 Over a year ago

This is great, thank you. And thanks for the demo as well! That showed exactly how I needed this done. Also, does the "%" sign represent a wild card value for whatever value is in the cell for that particular column?

SnakeDoc65 Over a year ago

So one problem I'm having is when there is "cents" involved. So let's say there's a value of 8888, which should be $88.88 I used the statement you provided, and it prints 8888 as $88.00. Any thoughts why it doesn't pick up the 88 cents value?

forpas Over a year ago

In your question you don't mention any cents.Instead of 100 use 100.0: db-fiddle.com/f/ozkGqbnhnc3DMCUWajjV88/1

Splendor · Accepted Answer · 2020-08-14 21:11:37Z

0

If you're trying to convert the values in Python you could do something like this:

new_value = float(old_value) / 100

That will attempt to cast the string as a float value and then divide it by 100 to correct the decimal place. If precision is important you may want to use the decimal type instead of float though.

answered Aug 14, 2020 at 21:11

Splendor

1,4166 gold badges30 silver badges63 bronze badges

Collectives™ on Stack Overflow

Convert multiple string values (Python 3)

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related