# Basic pivoting/widening of a table

**URL:** <https://discourse.julialang.org/t/basic-pivoting-widening-of-a-table/53812>\
**Category:** General Usage\
**Created:** [January 23, 2021, 12:02am UTC](https://discourse.julialang.org/t/basic-pivoting-widening-of-a-table/53812 "2021-01-23T00:02:49Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![affans](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/affans/32/11911_2.png) [@affans](https://discourse.julialang.org/u/affans)\
**Post date:** [January 23, 2021, 12:02am UTC](https://discourse.julialang.org/t/basic-pivoting-widening-of-a-table/53812/1 "2021-01-23T00:02:49Z")

</div>

Consider the following MWE:

```julia
using DataFrames 

df_str = """
mdate	type	yesno	workerid
1/1/2020	pcr	no	1
1/1/2020	molecular	yes	1
1/1/2020	pcr	yes	2
1/1/2020	molecular	yes	2
1/1/2020	antigen	yes	2
1/1/2020	molecular	no	3
1/2/2020	pcr	no	1
1/2/2020	molecular	yes	1
1/2/2020	antigen	yes	1
1/2/2020	molecular	yes	2
1/2/2020	antigen	yes	3
1/2/2020	molecular	no	3
1/2/2020	pcr	yes	3    
"""
df = CSV.File(IOBuffer(df_str)) |> DataFrame!

```

I would like to widen this table, i.e.

```julia
# pivot 
widedf = unstack(df, :type, :yesno)
# pivot 
widedf = unstack(df, :type, :yesno)
pretty_table(widedf, crop=:none)
┌──────────┬──────────┬────────────────────────┬────────────────────────┬────────────────────────┐
│ mdate │ workerid │ antigen │ molecular │ pcr │
│ String │ Int64 │ Union{Missing, String} │ Union{Missing, String} │ Union{Missing, String} │
├──────────┼──────────┼────────────────────────┼────────────────────────┼────────────────────────┤
│ 1/1/2020 │ 1 │ missing │ yes │ no │
│ 1/1/2020 │ 2 │ yes │ yes │ yes │
│ 1/1/2020 │ 3 │ missing │ no │ missing │
│ 1/2/2020 │ 1 │ yes │ yes │ no │
│ 1/2/2020 │ 2 │ missing │ yes │ missing │
│ 1/2/2020 │ 3 │ yes │ no │ yes │

```

but I would like to fix a few things here:

1. I would like to change the column types to purely `String`.
2. I would like to replace the missing data with `no`.

---

<div class="post-metadata">

**Author:** ![pdeffebach](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/pdeffebach/32/10320_2.png) [@pdeffebach](https://discourse.julialang.org/u/pdeffebach)\
**Post date:** [January 23, 2021, 12:10am UTC](https://discourse.julialang.org/t/basic-pivoting-widening-of-a-table/53812/2 "2021-01-23T00:10:40Z")

</div>

A better `read` command is the following

```julia
julia> df = CSV.read(IOBuffer(df_str), DataFrame; delim = " ", ignorerepeated = true)

```

You can’t do that `replace` part with just `unstack`. It’s best to just perform the replacements, or use `coalesce` after the unstack command.

EDIT: The best way to get an MWE is the following:

```julia
julia> io = IOBuffer()
IOBuffer(data=UInt8[...], readable=true, writable=true, seekable=true, append=false, size=0, maxsize=Inf, ptr=1, mark=-1)

julia> CSV.write(io, df);

julia> df_str = String(take!(io))
"mdate,type,yesno,workerid\n1/1/2020,pcr,no,1\n1/1/2020,molecular,yes,1\n1/1/2020,pcr,yes,2\n1/1/2020,molecular,yes,2\n1/1/2020,antigen,yes,2\n1/1/2020,molecular,no,3\n1/2/2020,pcr,no,1\n1/2/2020,molecular,yes,1\n1/2/2020,antigen,yes,1\n1/2/2020,molecular,yes,2\n1/2/2020,antigen,yes,3\n1/2/2020,molecular,no,3\n1/2/2020,pcr,yes,3\n"

julia> println(df_str)
mdate,type,yesno,workerid
1/1/2020,pcr,no,1
1/1/2020,molecular,yes,1
1/1/2020,pcr,yes,2
1/1/2020,molecular,yes,2
1/1/2020,antigen,yes,2
1/1/2020,molecular,no,3
1/2/2020,pcr,no,1
1/2/2020,molecular,yes,1
1/2/2020,antigen,yes,1
1/2/2020,molecular,yes,2
1/2/2020,antigen,yes,3
1/2/2020,molecular,no,3
1/2/2020,pcr,yes,3

```

Then you can copy and paste that and do the `CSV.read` trick. It would be great is someone made this little process a package.