Change Number Column

Changing precision of numeric column in Oracle

Assuming that you didn't set a precision initially, it's assumed to be the maximum (38). You're reducing the precision because you're changing it from 38 to 14.

The easiest way to handle this is to rename the column, copy the data over, then drop the original column:

alter table EVAPP_FEES rename column AMOUNT to AMOUNT_OLD;

alter table EVAPP_FEES add AMOUNT NUMBER(14,2);

update EVAPP_FEES set AMOUNT = AMOUNT_OLD;

alter table EVAPP_FEES drop column AMOUNT_OLD;

If you really want to retain the column ordering, you can move the data twice instead:

alter table EVAPP_FEES add AMOUNT_TEMP NUMBER(14,2);

update EVAPP_FEES set AMOUNT_TEMP = AMOUNT;

update EVAPP_FEES set AMOUNT = null;

alter table EVAPP_FEES modify AMOUNT NUMBER(14,2);

update EVAPP_FEES set AMOUNT = AMOUNT_TEMP;

alter table EVAPP_FEES drop column AMOUNT_TEMP;

Oracle: How to change column data type from VARCHAR to NUMBER without losing data

Oracle does not allow modification of data type of the column if it is not empty so as a workaround, You need to follow the following steps

1. create another column with NUMBER data type let's say "NUMBER1".
2. add the data of "NUMBER" column into that newly created column("NUMBER1").
3. Remove the original "NUMBER" column
4. Rename the newly created column "NUMBER1" to "NUMBER"

as following:

Oracle setup:

SQL> CREATE TABLE YOUR_TABLE (
  2      ID         NUMBER(10, 0),
  3      "NUMBER"   VARCHAR(255)
  4  );

Table created.

SQL> DESC YOUR_TABLE;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER(10)
 NUMBER                                             VARCHAR2(255)

SQL> INSERT INTO YOUR_TABLE VALUES (1,'1');

1 row created.

SQL> COMMIT;

Commit complete.

Showing error if the column data type is changed directly:

SQL> ALTER TABLE YOUR_TABLE MODIFY "NUMBER" NUMBER;
ALTER TABLE YOUR_TABLE MODIFY "NUMBER" NUMBER
                              *
ERROR at line 1:
ORA-01439: column to be modified must be empty to change datatype

Solution:

SQL> ALTER TABLE YOUR_TABLE ADD "NUMBER1" NUMBER;

Table altered.

SQL>
SQL> UPDATE YOUR_TABLE
  2  SET "NUMBER1" = "NUMBER";

1 row updated.

SQL>
SQL> ALTER TABLE YOUR_TABLE DROP COLUMN "NUMBER";

Table altered.

SQL>
SQL> ALTER TABLE YOUR_TABLE RENAME COLUMN "NUMBER1" TO "NUMBER";

Table altered.

SQL> DESC YOUR_TABLE;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER(10)
 NUMBER                                             NUMBER

SQL>

Cheers!!

Oracle SQL to change column type from number to varchar2 while it contains data

create table temp_uda1 (test1 integer);
insert into temp_uda1 values (1);

alter table temp_uda1 add (test1_new varchar2(3));

update temp_uda1 
   set test1_new = to_char(test1);

alter table temp_uda1 drop column test1 cascade constraints;
alter table temp_uda1 rename column test1_new to test1;

If there was an index on the column you need to re-create it.

Note that the update will fail if you have numbers in the old column that are greater than 999. If you do, you need to adjust the maximum value for the varchar column

How to change number of columns in ol list depending on parent div width?

As of today, rule columns: number column does not allow automatic adaptation of columns to the width of the parent.

To adapt the columns, use the property function minmax() (grid), indicating the minimum and maximum values:

grid-template-columns: repeat(auto-fit, minmax(100px, 1fr));

With the indication of parameter auto-fit, which will allow the parent to occupy free space during resizing.

#opponentsOfCivList {
    display: grid;
    grid-template-columns: repeat(auto-fit, minmax(100px, 1fr));
    column-gap: 10px;
    font-size: 12px;
    list-style-type: disc;
}

li {
    padding-left: 2px;
}

<div style="background: grey; resize: horizontal; width: 450px; overflow: auto;">
    <ol id="opponentsOfCivList">
        <li value="Phoenicia">Phoenicia</li>
        <li value="Austria-Hungary">Austria-Hungary</li>
        <li value="Persia">Persia</li>
        <li value="Siam">Siam</li>
        <li value="Maya">Maya</li>
        <li value="Korea">Korea</li>
        <li value="Babylonia">Babylonia</li>
        <li value="Brazil">Brazil</li>
        <li value="Ethiopia">Ethiopia</li>
        <li value="America">America</li>
        <li value="Spain">Spain</li>
        <li value="Aztec">Aztec</li>
        <li value="Vikingland">Vikingland</li>
        <li value="Egypt">Egypt</li>
        <li value="Greece">Greece</li>
        <li value="China">China</li>
        <li value="Arabia">Arabia</li>
        <li value="Turkey">Turkey</li>
        <li value="Mongol">Mongol</li>
        <li value="Inca">Inca</li>
        <li value="Portugal">Portugal</li>
        <li value="Rome">Rome</li>
        <li value="Netherlands">Netherlands</li>
        <li value="Byzantium">Byzantium</li>
        <li value="France">France</li>
        <li value="Germany">Germany</li>
        <li value="India">India</li>
        <li value="Japan">Japan</li>
        <li value="Huns">Huns</li>
        <li value="Russia">Russia</li>
        <li value="England">England</li>
        <li value="Iroquois">Iroquois</li>
    </ol>
    <p>resize this div at the bottom right!</p>
</div>

How to change the value format for a specific column - R

your gsub solution is almost correct.Try the following:

traffic$Traffic_count <- as.numeric(gsub("\\.","",traffic$Traffic_count))

Explanation

. is a special sign in regex, meaning "any" character, therefore it will replace every letter with "". If you exlicitly want to replace a dot, you need to put a \ in front of it. But as R tries to interpret a single "" within a string we need to tell R itself not to preprocess the slash with another slash. therefore \\

Change column type in pandas

You have four main options for converting types in pandas:

1. to_numeric() - provides functionality to safely convert non-numeric types (e.g. strings) to a suitable numeric type. (See also to_datetime() and to_timedelta().)

2. astype() - convert (almost) any type to (almost) any other type (even if it's not necessarily sensible to do so). Also allows you to convert to categorial types (very useful).

3. infer_objects() - a utility method to convert object columns holding Python objects to a pandas type if possible.

4. convert_dtypes() - convert DataFrame columns to the "best possible" dtype that supports pd.NA (pandas' object to indicate a missing value).

Read on for more detailed explanations and usage of each of these methods.

---

1. to_numeric()

The best way to convert one or more columns of a DataFrame to numeric values is to use pandas.to_numeric().

This function will try to change non-numeric objects (such as strings) into integers or floating-point numbers as appropriate.

Basic usage

The input to to_numeric() is a Series or a single column of a DataFrame.

>>> s = pd.Series(["8", 6, "7.5", 3, "0.9"]) # mixed string and numeric values
>>> s
0      8
1      6
2    7.5
3      3
4    0.9
dtype: object

>>> pd.to_numeric(s) # convert everything to float values
0    8.0
1    6.0
2    7.5
3    3.0
4    0.9
dtype: float64

As you can see, a new Series is returned. Remember to assign this output to a variable or column name to continue using it:

# convert Series
my_series = pd.to_numeric(my_series)

# convert column "a" of a DataFrame
df["a"] = pd.to_numeric(df["a"])

You can also use it to convert multiple columns of a DataFrame via the apply() method:

# convert all columns of DataFrame
df = df.apply(pd.to_numeric) # convert all columns of DataFrame

# convert just columns "a" and "b"
df[["a", "b"]] = df[["a", "b"]].apply(pd.to_numeric)

As long as your values can all be converted, that's probably all you need.

Error handling

But what if some values can't be converted to a numeric type?

to_numeric() also takes an errors keyword argument that allows you to force non-numeric values to be NaN, or simply ignore columns containing these values.

Here's an example using a Series of strings s which has the object dtype:

>>> s = pd.Series(['1', '2', '4.7', 'pandas', '10'])
>>> s
0         1
1         2
2       4.7
3    pandas
4        10
dtype: object

The default behaviour is to raise if it can't convert a value. In this case, it can't cope with the string 'pandas':

>>> pd.to_numeric(s) # or pd.to_numeric(s, errors='raise')
ValueError: Unable to parse string

Rather than fail, we might want 'pandas' to be considered a missing/bad numeric value. We can coerce invalid values to NaN as follows using the errors keyword argument:

>>> pd.to_numeric(s, errors='coerce')
0     1.0
1     2.0
2     4.7
3     NaN
4    10.0
dtype: float64

The third option for errors is just to ignore the operation if an invalid value is encountered:

>>> pd.to_numeric(s, errors='ignore')
# the original Series is returned untouched

This last option is particularly useful for converting your entire DataFrame, but don't know which of our columns can be converted reliably to a numeric type. In that case, just write:

df.apply(pd.to_numeric, errors='ignore')

The function will be applied to each column of the DataFrame. Columns that can be converted to a numeric type will be converted, while columns that cannot (e.g. they contain non-digit strings or dates) will be left alone.

Downcasting

By default, conversion with to_numeric() will give you either an int64 or float64 dtype (or whatever integer width is native to your platform).

That's usually what you want, but what if you wanted to save some memory and use a more compact dtype, like float32, or int8?

to_numeric() gives you the option to downcast to either 'integer', 'signed', 'unsigned', 'float'. Here's an example for a simple series s of integer type:

>>> s = pd.Series([1, 2, -7])
>>> s
0    1
1    2
2   -7
dtype: int64

Downcasting to 'integer' uses the smallest possible integer that can hold the values:

>>> pd.to_numeric(s, downcast='integer')
0    1
1    2
2   -7
dtype: int8

Downcasting to 'float' similarly picks a smaller than normal floating type:

>>> pd.to_numeric(s, downcast='float')
0    1.0
1    2.0
2   -7.0
dtype: float32

---

2. astype()

The astype() method enables you to be explicit about the dtype you want your DataFrame or Series to have. It's very versatile in that you can try and go from one type to any other.

Basic usage

Just pick a type: you can use a NumPy dtype (e.g. np.int16), some Python types (e.g. bool), or pandas-specific types (like the categorical dtype).

Call the method on the object you want to convert and astype() will try and convert it for you:

# convert all DataFrame columns to the int64 dtype
df = df.astype(int)

# convert column "a" to int64 dtype and "b" to complex type
df = df.astype({"a": int, "b": complex})

# convert Series to float16 type
s = s.astype(np.float16)

# convert Series to Python strings
s = s.astype(str)

# convert Series to categorical type - see docs for more details
s = s.astype('category')

Notice I said "try" - if astype() does not know how to convert a value in the Series or DataFrame, it will raise an error. For example, if you have a NaN or inf value you'll get an error trying to convert it to an integer.

As of pandas 0.20.0, this error can be suppressed by passing errors='ignore'. Your original object will be returned untouched.

Be careful

astype() is powerful, but it will sometimes convert values "incorrectly". For example:

>>> s = pd.Series([1, 2, -7])
>>> s
0    1
1    2
2   -7
dtype: int64

These are small integers, so how about converting to an unsigned 8-bit type to save memory?

>>> s.astype(np.uint8)
0      1
1      2
2    249
dtype: uint8

The conversion worked, but the -7 was wrapped round to become 249 (i.e. 2⁸ - 7)!

Trying to downcast using pd.to_numeric(s, downcast='unsigned') instead could help prevent this error.

---

3. infer_objects()

Version 0.21.0 of pandas introduced the method infer_objects() for converting columns of a DataFrame that have an object datatype to a more specific type (soft conversions).

For example, here's a DataFrame with two columns of object type. One holds actual integers and the other holds strings representing integers:

>>> df = pd.DataFrame({'a': [7, 1, 5], 'b': ['3','2','1']}, dtype='object')
>>> df.dtypes
a    object
b    object
dtype: object

Using infer_objects(), you can change the type of column 'a' to int64:

>>> df = df.infer_objects()
>>> df.dtypes
a     int64
b    object
dtype: object

Column 'b' has been left alone since its values were strings, not integers. If you wanted to force both columns to an integer type, you could use df.astype(int) instead.

---

4. convert_dtypes()

Version 1.0 and above includes a method convert_dtypes() to convert Series and DataFrame columns to the best possible dtype that supports the pd.NA missing value.

Here "best possible" means the type most suited to hold the values. For example, this a pandas integer type, if all of the values are integers (or missing values): an object column of Python integer objects are converted to Int64, a column of NumPy int32 values, will become the pandas dtype Int32.

With our object DataFrame df, we get the following result:

>>> df.convert_dtypes().dtypes                                             
a     Int64
b    string
dtype: object

Since column 'a' held integer values, it was converted to the Int64 type (which is capable of holding missing values, unlike int64).

Column 'b' contained string objects, so was changed to pandas' string dtype.

By default, this method will infer the type from object values in each column. We can change this by passing infer_objects=False:

>>> df.convert_dtypes(infer_objects=False).dtypes                          
a    object
b    string
dtype: object

Now column 'a' remained an object column: pandas knows it can be described as an 'integer' column (internally it ran infer_dtype) but didn't infer exactly what dtype of integer it should have so did not convert it. Column 'b' was again converted to 'string' dtype as it was recognised as holding 'string' values.

Change column value from character to number or duplicate row in R

You can first remove the square brackets and then separate_rows:

library(dplyr)
library(tidyr)
df %>%
  mutate(Y = gsub("\\[|\\]", "", Y)) %>%
  separate_rows(Y, sep = ",")
# A tibble: 6 × 2
      X Y    
  <dbl> <chr>
1    11 1    
2    15 400  
3    17 1    
4    17 2    
5    21 13   
6    21 14 

Change the number of columns and rows in a grid as the number of items increase

Implicit columns creation can do this. You can consider nth-child()/nth-last-child() to create a new column when you reach a specific number of columns:

.container {
  display: inline-grid;
  width: 100px;
  vertical-align: top;
  border: 1px solid;
}
.container > :nth-child(2) {
  grid-column: 2;
}

.container > :nth-last-child(n + 5) ~ :nth-child(3) { 
 grid-column: 3;
}

.container > :nth-last-child(n + 10) ~ :nth-child(4) { 
 grid-column: 4;
}

.container > :nth-last-child(n + 17) ~ :nth-child(5) { 
 grid-column: 5;
}

.container > * {
  border: 1px solid red;
  aspect-ratio: 1;
}

<div class="container">
<div></div>
</div>

<div class="container">
<div></div><div></div>
</div>

<div class="container">
<div></div><div></div><div></div>
</div>

<div class="container">
<div></div><div></div><div></div><div></div>
</div>

<div class="container">
<div></div><div></div><div></div><div></div><div></div>
</div>

<div class="container">
<div></div><div></div><div></div><div></div><div></div><div></div>
</div>

<div class="container">
<div></div><div></div><div></div><div></div><div></div><div></div><div></div>
</div>

<div class="container">
<div></div><div></div><div></div><div></div><div></div><div></div><div></div><div></div>
</div>

<div class="container">
<div></div><div></div><div></div><div></div><div></div><div></div><div></div><div></div><div></div>
</div>

<div class="container">
<div></div><div></div><div></div><div></div><div></div><div></div><div></div><div></div><div></div><div></div>
</div>

<div class="container">
<div></div><div></div><div></div><div></div><div></div><div></div><div></div><div></div><div></div><div></div><div></div>
</div>

<div class="container">
<div></div><div></div><div></div><div></div><div></div><div></div><div></div><div></div><div></div><div></div><div></div><div></div>
</div>

<div class="container">
<div></div><div></div><div></div><div></div><div></div><div></div><div></div><div></div><div></div><div></div><div></div><div></div><div></div>
</div>

<div class="container">
<div></div><div></div><div></div><div></div><div></div><div></div><div></div><div></div><div></div><div></div><div></div><div></div><div></div><div></div>
</div>

<div class="container">
<div></div><div></div><div></div><div></div><div></div><div></div><div></div><div></div><div></div><div></div><div></div><div></div><div></div><div></div><div></div>
</div>

<div class="container">
<div></div><div></div><div></div><div></div><div></div><div></div><div></div><div></div><div></div><div></div><div></div><div></div><div></div><div></div><div></div><div></div>
</div>

<div class="container">
<div></div><div></div><div></div><div></div><div></div><div></div><div></div><div></div><div></div><div></div><div></div><div></div><div></div><div></div><div></div><div></div><div></div>
</div>

<div class="container">
<div></div><div></div><div></div><div></div><div></div><div></div><div></div><div></div><div></div><div></div><div></div><div></div><div></div><div></div><div></div><div></div><div></div><div></div>
</div>

---

Related Topics