# Split country column into a column with city and a column with country

**URL:** <https://discourse.julialang.org/t/split-country-column-into-a-column-with-city-and-a-column-with-country/96586>\
**Category:** General Usage\
**Tags:** question, package, dataframes, inmemorydatasets\
**Created:** [March 25, 2023, 7:13am UTC](https://discourse.julialang.org/t/split-country-column-into-a-column-with-city-and-a-column-with-country/96586 "2023-03-25T07:13:28Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![akshdfyehd](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/akshdfyehd/32/39097_2.png) [@akshdfyehd](https://discourse.julialang.org/u/akshdfyehd)\
**Post date:** [March 25, 2023, 7:13am UTC](https://discourse.julialang.org/t/split-country-column-into-a-column-with-city-and-a-column-with-country/96586/1 "2023-03-25T07:13:28Z")

</div>

Hi, i have got a travel dataset and I need to split the destination column into two columns, one column is city and another column is country name, for example, “Sydney, Australia” will be split into sydney in city column and australia in country column.  
This is the original data:

 ![image](https://global.discourse-cdn.com/julialang/original/3X/3/4/3420e0a67644f97ce6428ff7ace34fa47d503dd5.png)

The problem is, some place only have city name and some place have both city name and country.  
Any advices will be really appreciated!!!

This is the code to read the data:

```julia
using InMemoryDatasets,DLMReader
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),:]

```

---

<div class="post-metadata">

**Author:** ![nilshg](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/nilshg/32/2283_2.png) [@nilshg](https://discourse.julialang.org/u/nilshg)\
**Post date:** [March 25, 2023, 8:50am UTC](https://discourse.julialang.org/t/split-country-column-into-a-column-with-city-and-a-column-with-country/96586/2 "2023-03-25T08:50:20Z")

</div>

Note that none of your questions are really about working with `InMemoryDatasets` (or `DataFrames` or similar), they are generally about how to work with simple vectors so it can often be edifying to reduce the MWE to just that.

In your case:

```julia
julia> x = ["London", "London, UK"]
2-element Vector{String}:
 "London"
 "London, UK"

julia> split.(x, ", ")
2-element Vector{Vector{SubString{String}}}:
 ["London"]
 ["London", "UK"]

```

You then need to think about how you deal with the fact that some country names are missing - one way would be to construct a tuple where the second element is just an empty string if the country is missing:

```julia
julia> (y -> length(y) == 1 ? (only(y), "") : (y...,)).(split.(x, ", "))
2-element Vector{Tuple{SubString{String}, AbstractString}}:
 ("London", "")
 ("London", "UK")

```

---

<div class="post-metadata">

**Author:** ![Jollywatt](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/jollywatt/32/202198_2.png) [@Jollywatt](https://discourse.julialang.org/u/Jollywatt)\
**Post date:** [March 25, 2023, 11:08am UTC](https://discourse.julialang.org/t/split-country-column-into-a-column-with-city-and-a-column-with-country/96586/3 "2023-03-25T11:08:14Z")

</div>

A regular expressions way:

```julia
julia> split_comma_pair(str) = collect(match(r"([^,]+),?(.*)", str))

```

Example usage with a data frame:

```julia
julia> using DataFrames

julia> df = DataFrame(destination = ["London", "London, UK"]);

julia> transform(df, :destination => ByRow(split_comma_pair) => [:city, :country])
2×3 DataFrame
 Row │ destination city country
     │ String SubStrin… SubStrin…
─────┼───────────────────────────────────
   1 │ London London
   2 │ London, UK London UK

```

* * *

Edit: Annoyingly, InMemoryDatasets.jl has its own slightly different way of doing column transformations:

```julia
julia> ds = Dataset(:destination => ["London", "London, UK"]);

julia> modify(ds, :destination => byrow(Tuple∘split_comma_pair),
                  :destination => splitter => [:city, :country])
2×3 Dataset
 Row │ destination city country
     │ identity identity identity
     │ Tuple…? SubStrin…? SubStrin…?
─────┼───────────────────────────────────────────
   1 │ ("London", "") London
   2 │ ("London", " UK") London UK

```

---

<div class="post-metadata">

**Author:** ![akshdfyehd](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/akshdfyehd/32/39097_2.png) [@akshdfyehd](https://discourse.julialang.org/u/akshdfyehd)\
**Post date:** [March 28, 2023, 8:10am UTC](https://discourse.julialang.org/t/split-country-column-into-a-column-with-city-and-a-column-with-country/96586/4 "2023-03-28T08:10:15Z")

</div>

Thanks!

---

<div class="post-metadata">

**Author:** ![akshdfyehd](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/akshdfyehd/32/39097_2.png) [@akshdfyehd](https://discourse.julialang.org/u/akshdfyehd)\
**Post date:** [March 28, 2023, 8:20am UTC](https://discourse.julialang.org/t/split-country-column-into-a-column-with-city-and-a-column-with-country/96586/6 "2023-03-28T08:20:34Z")

</div>

Hi, thank you so much for the reply, Can you please explain this part? I don’t quite understand this part, really appreciate!!

> [@Jollywatt](#):
>
> `r"([^,]+),?(.*)",`

---

<div class="post-metadata">

**Author:** ![Jollywatt](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/jollywatt/32/202198_2.png) [@Jollywatt](https://discourse.julialang.org/u/Jollywatt)\
**Post date:** [March 28, 2023, 10:57am UTC](https://discourse.julialang.org/t/split-country-column-into-a-column-with-city-and-a-column-with-country/96586/7 "2023-03-28T10:57:19Z")

</div>

Sure: `r"..."` is Julia’s syntax for a [regular expression literal](https://docs.julialang.org/en/v1/manual/strings/#man-regex-literals). The regular expression

> `r"([^,]+),?\s*(.*)"`

has two capture groups (enclosed in parens): the first capture group `([^,]+)` matches one or more (`+`) non-comma characters. After this group, it optionally matches a comma (`,?`) and zero or more spaces (`\s*`) before getting to the next capture group `(.*)` which matches everything remaining.

When using this regex with `match`, you can access the matched capture groups by indexing (or `collect`ing).

```julia
julia> match(r"([^,]+),?\s*(.*)", "Th!s, and, that")
RegexMatch("Th!s, and, that", 1="Th!s", 2="and, that")

julia> ans[1], ans[2]
("Th!s", "and, that")

```