# Use joins to change country names
**URL:** https://discourse.julialang.org/t/use-joins-to-change-country-names/97507
**Category:** General Usage
**Tags:** question, package, inmemorydatasets
**Created:** [April 15, 2023, 6:54am UTC](https://discourse.julialang.org/t/use-joins-to-change-country-names/97507 "2023-04-15T06:54:13Z")
**Posts on this page:** 8
**Page:** 1
### Author:  [@akshdfyehd](https://discourse.julialang.org/u/akshdfyehd)
#### Post date: [April 15, 2023, 6:54am UTC](https://discourse.julialang.org/t/use-joins-to-change-country-names/97507/1 "2023-04-15T06:54:14Z")
Hi, I have got a dataset with a city and country column. However, there are some names in country column are actually the same place but different names, for example:
row 7 and 123, I want both country name be Australia, the way I prefer is to use package named Countries [List of country codes by alpha-2, alpha-3 code (ISO 3166)](https://www.iban.com/country-codes) and joins function(leftjoins, semijoin…) from inmemorydataset package.
I have tried to use joins but it just didn’t work:
Here is the code to get the dataset which named ds and countries named count:
```julia
using InMemoryDatasets,DLMReader, Countries
import Downloads
data=Downloads.download("https://raw.githubusercontent.com/akshdfyehd/travel/main/Travel%20details%20dataset.csv")
data=filereader(data, quotechar='"', dtformat=Dict(3:4 .=> dateformat"m/d/y"))
data=data[completecases(data),:]
modify!(data, 11 => x -> parse.(Int, replace.(x, "\$" =>"","USD" =>"",","=>"")))
modify!(data, 13 => x -> parse.(Int, replace.(x, "\$" =>"","USD" =>"",","=>"")))
split_comma_pair(str) = collect(match(r"([^,]+),?(.*)", str))
modify!(data, :Destination => byrow(Tuple∘split_comma_pair),
:Destination => splitter => [:city, :country])
ds=select(data, 14:15)
count=Dataset(all_countries())
```
really appreciate any advices.
---
### Author:  [@rocco\_sprmnt21](https://discourse.julialang.org/u/rocco_sprmnt21)
#### Post date: [April 15, 2023, 10:07am UTC](https://discourse.julialang.org/t/use-joins-to-change-country-names/97507/2 "2023-04-15T10:07:08Z")
I think there are two kinds of problems:
1. the space after the comma
2. uppercase characters
```julia
split_comma_pair(str) = let m=match(r"([^,]+)(, )?(.*)", str); (first(m.captures),last(m.captures)) end
modify!(data, :Destination => byrow(split_comma_pair),
:Destination => splitter => [:city, :country])
ds=select(data, 14:15)
modify!(ds, :country=>byrow(uppercase))
count=Dataset(all_countries())
leftjoin(ds, count, on=:country=>:alpha3)
```
a more general form of the split\_comma… function
```julia
split_comma_pair(str)=Tuple(match(r"([^,]+),? *(.*)", str))
```
---
### Author:  [@akshdfyehd](https://discourse.julialang.org/u/akshdfyehd)
#### Post date: [April 19, 2023, 5:31am UTC](https://discourse.julialang.org/t/use-joins-to-change-country-names/97507/3 "2023-04-19T05:31:11Z")
Thanks for the reply!!!
---
### Author:  [@akshdfyehd](https://discourse.julialang.org/u/akshdfyehd)
#### Post date: [April 19, 2023, 6:21am UTC](https://discourse.julialang.org/t/use-joins-to-change-country-names/97507/4 "2023-04-19T06:21:48Z")
Hi,
I got the dataset which is correct, but my problem is to replace to the name, for example replace USA to united states in column 17, rest are stay untouched:
Do you know an easy way to replace all these?
really appreciate.
---
### Author:  [@rocco\_sprmnt21](https://discourse.julialang.org/u/rocco_sprmnt21)
#### Post date: [April 19, 2023, 7:00am UTC](https://discourse.julialang.org/t/use-joins-to-change-country-names/97507/5 "2023-04-19T07:00:11Z")
this is one way
```julia
acount=Dataset(all_countries())
ccn=leftjoin(ds, acount, on=:country=>:alpha3)
coalesce.(ccn[:,:common_name],ccn[:,:country])
coalesce.(ccn[:,:common_name],data[:,:country]) # to get ProperCase
```
---
### Author:  [@akshdfyehd](https://discourse.julialang.org/u/akshdfyehd)
#### Post date: [April 23, 2023, 4:21am UTC](https://discourse.julialang.org/t/use-joins-to-change-country-names/97507/7 "2023-04-23T04:21:48Z")
Hi, I am so sorry to bother you again, my tutor want me to use modify to do this(from inmemorydatasets package) , do you know a function that can perform the same in inmemorydataset package as coalesce? I have tried replace but seems that function can’t do the missing value. Thanks for any advices, really appreciate
---
### Author:  [@rocco\_sprmnt21](https://discourse.julialang.org/u/rocco_sprmnt21)
#### Post date: [April 23, 2023, 6:18am UTC](https://discourse.julialang.org/t/use-joins-to-change-country-names/97507/8 "2023-04-23T06:18:52Z")
try something like this
```julia
julia> ds
5×2 Dataset
Row │ x1 x2
│ identity identity
│ Int64? Int64?
─────┼────────────────────
1 │ -1 1
2 │ 10 missing
3 │ 4 missing
4 │ 5 -8
5 │ 1 missing
julia> modify(ds,(:x1,:x2)=>byrow((x1,x2)->ismissing(x2) ? x1 : x2)=>:new_x)
5×3 Dataset
Row │ x1 x2 new_x
│ identity identity identity
│ Int64? Int64? Int64?
─────┼──────────────────────────────
1 │ -1 1 1
2 │ 10 missing 10
3 │ 4 missing 4
4 │ 5 -8 -8
5 │ 1 missing 1
```
---
### Author:  [@akshdfyehd](https://discourse.julialang.org/u/akshdfyehd)
#### Post date: [April 23, 2023, 6:37am UTC](https://discourse.julialang.org/t/use-joins-to-change-country-names/97507/9 "2023-04-23T06:37:47Z")
Thank you!!!