# Recode multiple columns in DataFrame as integer
**URL:** https://discourse.julialang.org/t/recode-multiple-columns-in-dataframe-as-integer/76699
**Category:** General Usage
**Tags:** dataframes
**Created:** [February 18, 2022, 2:50pm UTC](https://discourse.julialang.org/t/recode-multiple-columns-in-dataframe-as-integer/76699 "2022-02-18T14:50:04Z")
**Posts on this page:** 9
**Page:** 1
### Author:  [@Christopher\_Fisher](https://discourse.julialang.org/u/Christopher_Fisher)
#### Post date: [February 18, 2022, 2:50pm UTC](https://discourse.julialang.org/t/recode-multiple-columns-in-dataframe-as-integer/76699/1 "2022-02-18T14:50:04Z")
Hi all,
I want to recode symbols in a DataFrame as integers so I can use `confusmat` to create a confusion matrix. Here is what my `DataFrame` looks like:
```julia
using DataFrames
df = DataFrame(x = [:a,:a,:b,:c,:c], y = [:b,:b,:c,:a,:c])
```
**output**
```julia
5×2 DataFrame
Row │ x y
│ Symbol Symbol
─────┼────────────────
1 │ a b
2 │ a b
3 │ b c
4 │ c a
5 │ c c
```
I want to recode the symbols so that:
```julia
5×4 DataFrame
Row │ x y new_x new_y
│ Symbol Symbol Int64 Int64
─────┼──────────────────────────────
1 │ a b 1 2
2 │ a b 1 2
3 │ b c 2 3
4 │ c a 3 1
5 │ c c 3 3
```
Notice that the mapping must be the same in `new_x` and `new_y`. How can I do that with DataFrames?
Thanks!
---
### Author:  [@pdeffebach](https://discourse.julialang.org/u/pdeffebach)
#### Post date: [February 18, 2022, 2:52pm UTC](https://discourse.julialang.org/t/recode-multiple-columns-in-dataframe-as-integer/76699/2 "2022-02-18T14:52:54Z")
I don’t have a great solution. Something like
```julia
julia> using CategoricalArrays
julia> levelcode.(categorical(string.(df.x)))
5-element Vector{Int64}:
1
1
2
3
3
```
But this might be an X-Y problem. Unlike Stata, columns don’t need to be encoded as integers in order to use fixed effects.
---
### Author:  [@Christopher\_Fisher](https://discourse.julialang.org/u/Christopher_Fisher)
#### Post date: [February 18, 2022, 3:00pm UTC](https://discourse.julialang.org/t/recode-multiple-columns-in-dataframe-as-integer/76699/3 "2022-02-18T15:00:46Z")
> [@pdeffebach](#):
>
> `levelcode.(categorical(string.(df.x)))`
Thanks for your response. Unfortunately, I have identified a case in which the mapping is not identical between the columns. If a value is missing in one of the columns, the mapping will be different:
```julia
df = DataFrame(x = [:a,:a,:b,:c,:c], y = [:b,:b,:c,:b,:c])
using CategoricalArrays
df.new_x = levelcode.(categorical(string.(df.x)))
df.new_y = levelcode.(categorical(string.(df.y)))
```
**output**
```julia
5×4 DataFrame
Row │ x y new_x new_y
│ Symbol Symbol Int64 Int64
─────┼──────────────────────────────
1 │ a b 1 1
2 │ a b 1 1
3 │ b c 2 2
4 │ c b 3 1
5 │ c c 3 2
```
In column new\_x, c is mapped to 3, but it is mapped to 2 in column new\_y.
---
### Author:  [@tbeason](https://discourse.julialang.org/u/tbeason)
#### Post date: [February 18, 2022, 3:05pm UTC](https://discourse.julialang.org/t/recode-multiple-columns-in-dataframe-as-integer/76699/4 "2022-02-18T15:05:24Z")
In that case you probably need to extract all unique values from the original columns, create a mapping from these unique values, and then use something like `replace` to map the columns in the DataFrame to their corresponding new values.
EDIT: for example, if `u` is the set of all unique values in your initial DataFrame, then
```julia
mapping = Dict(zip(u,levelcode.(CategoricalArray(string.(u)))))
Dict{Symbol, Int64} with 3 entries:
:a => 1
:b => 2
:c => 3
```
will create the mapping for you.
---
### Author:  [@nilshg](https://discourse.julialang.org/u/nilshg)
#### Post date: [February 18, 2022, 3:22pm UTC](https://discourse.julialang.org/t/recode-multiple-columns-in-dataframe-as-integer/76699/5 "2022-02-18T15:22:45Z")
A fun little two liner without any additional packages:
```julia
julia> uvals = sort(unique([df.x; df.y]))
3-element Vector{Symbol}:
:a
:b
:c
ulia> leftjoin(leftjoin(df, DataFrame(x = uvals, new_x = 1:length(uvals)), on = :x), DataFrame(y = uvals, new_y = 1:length(uvals)), on = :y)
5×4 DataFrame
Row │ x y new_x new_y
│ Symbol Symbol Int64? Int64?
─────┼────────────────────────────────
1 │ a b 1 2
2 │ a b 1 2
3 │ b c 2 3
4 │ c a 3 1
5 │ c c 3 3
```
---
### Author:  [@Christopher\_Fisher](https://discourse.julialang.org/u/Christopher_Fisher)
#### Post date: [February 18, 2022, 4:09pm UTC](https://discourse.julialang.org/t/recode-multiple-columns-in-dataframe-as-integer/76699/6 "2022-02-18T16:09:52Z")
> [@nilshg](#):
>
> `leftjoin(leftjoin(df, DataFrame(x = uvals, new_x = 1:length(uvals)), on = :x), DataFrame(y = uvals, new_y = 1:length(uvals)), on = :y)`
Thank you all for your help! I will mark this as the solution because it does not require an additional package. For future reference, others may consider tbeason’s solution too.
---
### Author:  [@tbeason](https://discourse.julialang.org/u/tbeason)
#### Post date: [February 18, 2022, 4:11pm UTC](https://discourse.julialang.org/t/recode-multiple-columns-in-dataframe-as-integer/76699/7 "2022-02-18T16:11:13Z")
I was just copying from @pdeffebach lol
---
### Author:  [@nalimilan](https://discourse.julialang.org/u/nalimilan)
#### Post date: [February 18, 2022, 10:38pm UTC](https://discourse.julialang.org/t/recode-multiple-columns-in-dataframe-as-integer/76699/8 "2022-02-18T22:38:13Z")
> [@Christopher\_Fisher](#):
>
> Thanks for your response. Unfortunately, I have identified a case in which the mapping is not identical between the columns. If a value is missing in one of the columns, the mapping will be different:
You can do this to ensure that both columns use the same levels:
```julia
df = DataFrame(x = [:a,:a,:b,:c,:c], y = [:b,:b,:c,:b,:c])
using CategoricalArrays
levs = string.(union(unique(df.x), unique(df.y)))
df.new_x = levelcode.(categorical(string.(df.x), levels=levs))
df.new_y = levelcode.(categorical(string.(df.y), levels=levs))
julia> df
5×4 DataFrame
Row │ x y new_x new_y
│ Symbol Symbol Int64 Int64
─────┼──────────────────────────────
1 │ a b 1 2
2 │ a b 1 2
3 │ b c 2 3
4 │ c b 3 2
5 │ c c 3 3
```
---
### Author:  [@rafael.guerra](https://discourse.julialang.org/u/rafael.guerra)
#### Post date: [February 19, 2022, 10:06am UTC](https://discourse.julialang.org/t/recode-multiple-columns-in-dataframe-as-integer/76699/9 "2022-02-19T10:06:02Z")
Another possibility:
```julia
hcat(df, Int.(getindex.(string.(df),1)) .- 96, makeunique=true)
```