How to convert column values from string to decimal?

spark cast column to string
scala column to string
cast decimal to string pyspark
pyspark transform column
spark convert column to float
convert col to string scala
change column type in scala
spark data type decimal

I'm having a dataframe which contains a really big integer value, example:

42306810747081022358

When I've tried to convert it to long it was working in the Java but not under the spark envrironment, I was getting

   NumberFormatException: For input string("42306810747081022358")

Then I tried to convert it too Decimal (BigDecimal) value. Again, easily can do it in Java, but in Spark: dframe.withColumn("c_number",col("c_a").cast(new DecimalType()));

This way I don't get any exceptions, however I can see that all result values are null.

I also tried to use UDF for this purpose but get the same results:

UDF1 cTransformer = new UDF1<String, BigDecimal>() {
        @Override
        public BigDecimal call(String aString) throws Exception {
            return new BigDecimal(aString);
        }
    };
sqlContext.udf().register("cTransformer", cTransformer, new DecimalType());
dframe = dframe.withColumn("c_number", callUDF("cTransformer", dframe.col("c_a"))); 

And here again all I'm getting is a column with all zeroes.

How should I proceed?

Try:

dframe.withColumn("c_number", dframe.col("c_a").cast("decimal(38,0)"))

Convert numbers stored as text to numbers, If so, in this tutorial, I'll review 2 scenarios to demonstrate how to convert strings to floats: (1) For a column that contains numeric values stored as strings; and The point he is making is that an integer(1) and a decimal(1) are not the same thing - integer(1) can hold up to 255 different values where decimal(1) can hold only 10 - transferring from either into a string means that the integer has to be converted where the decimal only has to be copied

A Decimal has a precision and scale value, by default the precision is 10 and scale is 0. The precision is the maximum number of digit in your number. In your case you have more than 10 digits so the number can't be cast to a 10 digits Decimal and you have null values.

To avoid that you need to specify a precision large enough to represent your numbers :

dframe.withColumn("c_number", dframe.col("c_a").cast(new DecimalType(38,0)))

Note that the precision can be up to 38

How to Convert Strings to Floats in Pandas DataFrame, In this guide, I'll show you two methods to convert a string into an integer in pandas DataFrame: (1) The astype(int) method: df['DataFrame Column']  Of course you can convert the result to a decimal afterwards: decimal d = (decimal)Int64.Parse(s, System.Globalization.NumberStyles.HexNumber); Or you can directly convert the original string with decimal coded hex groups and save you the conversion to the intermediate representation as a hex string.

This is after you have data in data frame with column that needs to be converted is ready Try: dframe.select($"column_name".cast("decimal(9,2)"))

How to Convert String to Integer in Pandas DataFrame, Frequently, a program ends up with numeric data in a string object—a value entered The Number subclasses that wrap primitive numeric types ( Byte , Integer  Specifically, we'll convert a few time values into a more meaningful format. For example, it's common to see time values that represent elapsed time - 1.5, 1.25, 3.75, 1, 2.3, and so on - in a

In scala :

df=df.withColumn("col", $"col".cast(DecimalType(9,2)))

Converting Between Numbers and Strings (The Java™ Tutorials , This tutorial will teach you how to convert strings to "true" numbers. To convert a column of text values, you enter the formula in the first cell,  I.e. one additional check you may want to use is that a decimal cannot be the first or last character in the string. That is easy enough to do by adding those additional checks into the first CASE for the SingleDec column.

Excel: convert text to number with formula and other ways, SQL conversion functions allow you to do things like change a number in text and of value such as numbers, dates, or text in table columns and to use in functions, One is an SMALLINT value, which has no decimal places, and the other is 1 Conversion failed when converting date and/or time from character string. If you add a new column, update it using your old values and try_convert, then you will be able to identify the problem values, where your decimal column is null and your varchar column is not. – GarethD Feb 18 '15 at 8:09

Converting Between Data Types with SQL Server Functions , DAX calculated columns must be of a single data type. Since MEDIAN and MEDIANX functions over an integer column return mixed data types,  Now how do you convert those strings values into integers? You may use the first method of astype(int) to perform the conversion: df['DataFrame Column'] = df['DataFrame Column'].astype(int) Since in our example the ‘DataFrame Column’ is the Price column (which contains the strings values), you’ll then need to add the following syntax:

CONVERT function (DAX), Numerical data saved as a string in a PostGIS table column needs to be Select the 'Decimal Number (real)' option under 'Output field type' to  Round Off Decimal Values Using the ROUND Function. The ROUND function rounds numbers to a certain number of decimal places that you configure. If the next digit to the right is between zero and four, it rounds down. So, for example, if you were rounding down to two decimal places, 8.532 would become 8.53.

Comments
  • Code only answers are rarely helpful. Please elaborate on your code and explain why and how this solves the question.