# DataFrames: Turning a column of ints into an array of ints per row dependent upon grouping of matching inputs

**URL:** <https://discourse.julialang.org/t/dataframes-turning-a-column-of-ints-into-an-array-of-ints-per-row-dependent-upon-grouping-of-matching-inputs/105978>\
**Category:** General Usage\
**Tags:** dataframes\
**Created:** [November 9, 2023, 4:31am UTC](https://discourse.julialang.org/t/dataframes-turning-a-column-of-ints-into-an-array-of-ints-per-row-dependent-upon-grouping-of-matching-inputs/105978 "2023-11-09T04:31:20Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![Name1](https://avatars.discourse-cdn.com/v4/letter/n/ed655f/32.png) [@Name1](https://discourse.julialang.org/u/Name1)\
**Post date:** [November 9, 2023, 4:31am UTC](https://discourse.julialang.org/t/dataframes-turning-a-column-of-ints-into-an-array-of-ints-per-row-dependent-upon-grouping-of-matching-inputs/105978/1 "2023-11-09T04:31:20Z")

</div>

Hi all,

I want to turn this DataFrame (see first image)

 ![image](https://global.discourse-cdn.com/julialang/original/3X/f/8/f8e5bcbfebda8d5a12472d887300a49fd6107e1b.png)

```julia
df = DataFrame(Year = [1,2,3,1,2,3], Name=["Jack","Jack","Jack","Jill","Jill","Jill"], Gender=["M","M","M","F","F","F"], Cash=[10.0, 14.0, 12.0, 20.5, 21.5, 22.5], Grade=[1, 2, 3, 2, 4, 3])

```

into this DataFrame (see second image)

 ![image](https://global.discourse-cdn.com/julialang/original/3X/d/0/d0df159fa66ef4f7151e119298ca9b397155dbf1.png)

```julia
intended_df = DataFrame(Name = ["Jack", "Jill"], Gender=["M", "F"], Cash=[[10.0, 14.0, 12.0], [20.5, 21.5, 22.5]], Grade=[[1,2,3],[2,4,3]])

```

Basically, I’m taking the observed output values for the “Cash” and “Grade” column across years 1-3 on the same input columns (“Name” and “Gender”), and turning them into an array of values in order from years 1-3 such that I can drop the “Year” column from the DataFrame. Does anybody have a suggested command for me to do this please? In my actual DataFrame there are going to be more inputs columns and more output columns than the 2 (Name, Gender) and 2 (Cash, Grade) that I have there, but I imagine the principle will be the same still.

Thanks for your time, I hope I’ve explained the question in a sensible manner (should hopefully be a straightforward scenario). If you need any more information from me please feel free to comment and let me know.

---

<div class="post-metadata">

**Author:** ![sijo](https://avatars.discourse-cdn.com/v4/letter/s/da6949/32.png) [@sijo](https://discourse.julialang.org/u/sijo)\
**Post date:** [November 9, 2023, 8:34am UTC](https://discourse.julialang.org/t/dataframes-turning-a-column-of-ints-into-an-array-of-ints-per-row-dependent-upon-grouping-of-matching-inputs/105978/2 "2023-11-09T08:34:30Z")

</div>

What about this:

```julia
using DataFrames
df = DataFrame(Year=[1,2,3,1,2,3],
               Name=["Jack","Jack","Jack","Jill","Jill","Jill"],
               Gender=["M","M","M","F","F","F"],
               Cash=[10.0, 14.0, 12.0, 20.5, 21.5, 22.5],
               Grade=[1, 2, 3, 2, 4, 3])

combine(groupby(df, [:Name, :Gender]),
        [:Cash, :Grade] .=> Ref, renamecols=false)

2×4 DataFrame
 Row │ Name Gender Cash Grade     
     │ String String SubArray… SubArray… 
─────┼───────────────────────────────────────────────
   1 │ Jack M [10.0, 14.0, 12.0] [1, 2, 3]
   2 │ Jill F [20.5, 21.5, 22.5] [2, 4, 3]

```

(replace `Ref` with `Ref∘copy` if you want to have copies of the data in the new data frame, so that further changes won’t change `df`)

Explanation: `groupby` makes the groups that you want (one group for each row of result), and `combine` transforms each group into a single row: `[:Cash, :Grade] .=> Ref` means that each of the `Cash` and `Grade` column gets transformed by the `Ref` function. The `Ref` adds a small wrapper around the column so that it gets treated as a single value (to have the whole vector as a row value).

---

<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:** [November 9, 2023, 11:03am UTC](https://discourse.julialang.org/t/dataframes-turning-a-column-of-ints-into-an-array-of-ints-per-row-dependent-upon-grouping-of-matching-inputs/105978/3 "2023-11-09T11:03:25Z")

</div>

`copy` isn’t needed here because you can’t actually `copy` a single number, right? I.e.

```julia
julia> df1 = combine(groupby(df, [:Name, :Gender]),
               [:Cash, :Grade] .=> Ref, renamecols=false)
2×4 DataFrame
 Row │ Name Gender Cash Grade
     │ String String SubArray… SubArray…
─────┼───────────────────────────────────────────────
   1 │ Jack M [10.0, 14.0, 12.0] [1, 2, 3]
   2 │ Jill F [20.5, 21.5, 22.5] [2, 4, 3]

julia> df.Cash .= 1;

julia> df1
2×4 DataFrame
 Row │ Name Gender Cash Grade
     │ String String SubArray… SubArray…
─────┼───────────────────────────────────────────────
   1 │ Jack M [10.0, 14.0, 12.0] [1, 2, 3]
   2 │ Jill F [20.5, 21.5, 22.5] [2, 4, 3]

julia> df
6×5 DataFrame
 Row │ Year Name Gender Cash Grade
     │ Int64 String String Int64 Int64
─────┼─────────────────────────────────────
   1 │ 1 Jack M 1 1
   2 │ 2 Jack M 1 2
   3 │ 3 Jack M 1 3
   4 │ 1 Jill F 1 2
   5 │ 2 Jill F 1 4
   6 │ 3 Jill F 1 3

```

or do you mean in the more general case where `Cash` could be some mutable container?

---

<div class="post-metadata">

**Author:** ![sijo](https://avatars.discourse-cdn.com/v4/letter/s/da6949/32.png) [@sijo](https://discourse.julialang.org/u/sijo)\
**Post date:** [November 9, 2023, 11:51am UTC](https://discourse.julialang.org/t/dataframes-turning-a-column-of-ints-into-an-array-of-ints-per-row-dependent-upon-grouping-of-matching-inputs/105978/4 "2023-11-09T11:51:08Z")

</div>

No it is required (`combine` will call `Ref` or `copy` with the whole column as argument):

```julia
julia> df1 = combine(groupby(df, [:Name, :Gender]),
               [:Cash, :Grade] .=> Ref, renamecols=false)
2×4 DataFrame
 Row │ Name Gender Cash Grade     
     │ String String SubArray… SubArray… 
─────┼───────────────────────────────────────────────
   1 │ Jack M [10.0, 14.0, 12.0] [1, 2, 3]
   2 │ Jill F [20.5, 21.5, 22.5] [2, 4, 3]

julia> df.Cash[1] = 0
0

julia> df1
2×4 DataFrame
 Row │ Name Gender Cash Grade     
     │ String String SubArray… SubArray… 
─────┼───────────────────────────────────────────────
   1 │ Jack M [0.0, 14.0, 12.0] [1, 2, 3]
   2 │ Jill F [20.5, 21.5, 22.5] [2, 4, 3]

```

I think you got bitten by the special behavior of `.=` in DataFrames: it’s not in-place, it creates a new vector for the result (I also find it confusing).

---

<div class="post-metadata">

**Author:** ![Name1](https://avatars.discourse-cdn.com/v4/letter/n/ed655f/32.png) [@Name1](https://discourse.julialang.org/u/Name1)\
**Post date:** [November 14, 2023, 5:22am UTC](https://discourse.julialang.org/t/dataframes-turning-a-column-of-ints-into-an-array-of-ints-per-row-dependent-upon-grouping-of-matching-inputs/105978/5 "2023-11-14T05:22:22Z")

</div>

Hi,

Thank you both for your help. The solution with .=\> Ref worked. I knew it’d be some combination of the groupby() and combine() function with something, so appreciate finding out that Ref was what I needed.

Thanks again.

---

<div class="post-metadata">

**Author:** ![Name1](https://avatars.discourse-cdn.com/v4/letter/n/ed655f/32.png) [@Name1](https://discourse.julialang.org/u/Name1)\
**Post date:** [November 17, 2023, 6:18am UTC](https://discourse.julialang.org/t/dataframes-turning-a-column-of-ints-into-an-array-of-ints-per-row-dependent-upon-grouping-of-matching-inputs/105978/6 "2023-11-17T06:18:04Z")

</div>

Hi there,

Back on this again. If I wanted to revert the operation such that I get `df` from `df1`, what command could I use?

---

<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:** [November 17, 2023, 8:48am UTC](https://discourse.julialang.org/t/dataframes-turning-a-column-of-ints-into-an-array-of-ints-per-row-dependent-upon-grouping-of-matching-inputs/105978/7 "2023-11-17T08:48:33Z")

</div>

```julia
julia> flatten(df1, [:Cash, :Grade])
6×4 DataFrame
 Row │ Name Gender Cash Grade
     │ String String Float64 Int64
─────┼────────────────────────────────
   1 │ Jack M 10.0 1
   2 │ Jack M 14.0 2
   3 │ Jack M 12.0 3
   4 │ Jill F 20.5 2
   5 │ Jill F 21.5 4
   6 │ Jill F 22.5 3

```