# Filtering and Grouping DataFrame

**URL:** <https://discourse.julialang.org/t/filtering-and-grouping-dataframe/29691>\
**Category:** New to Julia\
**Created:** [October 9, 2019, 7:05pm UTC](https://discourse.julialang.org/t/filtering-and-grouping-dataframe/29691 "2019-10-09T19:05:10Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![hasanOryx](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/hasanoryx/32/5373_2.png) [@hasanOryx](https://discourse.julialang.org/u/hasanOryx)\
**Post date:** [October 9, 2019, 7:05pm UTC](https://discourse.julialang.org/t/filtering-and-grouping-dataframe/29691/1 "2019-10-09T19:05:11Z")

</div>

I’ve a csv file of the following:  
1- Customer name  
2- Customer location  
3- Deliveries over last 60 months, so `n` in the table below is `60` sometimes more

I read the data as:

```nohighlight
using CSV, Query, DataFramesMeta, DataFrames
df = CSV.read("deliveries.csv"; header=true, delim=',')

```

And get it as:

```bash

locations×n DataFrame
│ Row │ Customer │ location │ M0 │ M1 │ M2 │ ... │ Mn
│ │ String │ String │ Int64 │ Int64 │ Int64 │ ... │ Int64 
├─────┼──────────┼──────────┼─────────┼──────────┼───────┼───────┼──────
│ 1 │ x │ X1 │ 5 │ 4 │ 5 │ ... │ 3    
│ 2 │ x │ X2 │ 3 │ 3 │ 4 │ ... │ 5     
│ 3 │ y │ X3 │ 6 │ 3 │ 4 │ ... │ 5      

```

How can I work it to do the following:  
1- Exclude location from the data frame  
2- Group by customer, so that ineach month, the number became the sum of deliveries for all locations with this customer under the given month, something as below:

```bash

customers×n DataFrame
│ Row │ Customer │ M0 │ M1 │ M2 │ ... │ Mn 
│ │ String │ Int64 │ Int64 │ Int64 │ ..... │ Int64 
├─────┼──────────┼─────────┼──────────┼───────┼───────┼───
│ 1 │ x │ 8 │ 7 │ 9 │ ... │ 8     
│ 2 │ y │ 6 │ 3 │ 4 │ ... │ 5     

```

In typical SQL, it can be done as:

```sql
Select Customer,SUM(M0),SUM(M1),SUM(M2),...,SUM(Mn) From Customers GROUP BY Customer

```

---

<div class="post-metadata">

**Author:** ![bkamins](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/bkamins/32/208538_2.png) [@bkamins](https://discourse.julialang.org/u/bkamins)\
**Post date:** [October 9, 2019, 7:34pm UTC](https://discourse.julialang.org/t/filtering-and-grouping-dataframe/29691/2 "2019-10-09T19:34:33Z")

</div>

```julia
aggregate(df[:, Not(:location)], :Customer, sum)

```

with a drawback that it will rename you the columns

or

```julia
by(sdf -> mapcols(sum, sdf[:, 3:end]), df, :Customer)

```

and now your columns will not get renamed.

---

<div class="post-metadata">

**Author:** ![hasanOryx](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/hasanoryx/32/5373_2.png) [@hasanOryx](https://discourse.julialang.org/u/hasanOryx)\
**Post date:** [October 9, 2019, 7:38pm UTC](https://discourse.julialang.org/t/filtering-and-grouping-dataframe/29691/3 "2019-10-09T19:38:33Z")

</div>

> [@bkamins](#):
>
> ```julia
> by(
> sdf -> mapcols(sum, sdf[:, 3:end]),
> df, 
> :Customer
> )
> 
> ```

This one workd perfectly, can you please explain how it works

---

<div class="post-metadata">

**Author:** ![bkamins](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/bkamins/32/208538_2.png) [@bkamins](https://discourse.julialang.org/u/bkamins)\
**Post date:** [October 9, 2019, 8:00pm UTC](https://discourse.julialang.org/t/filtering-and-grouping-dataframe/29691/4 "2019-10-09T20:00:16Z")

</div>

1. you group `df` by customer,
2. then in each subgroup you drop first two columns (as they are `:Customer` and `:location` so you do not want to aggregate them)
3. after dropping the columns you apply `sum` function to each column of such a data frame (which is done by `mapcols` function)

You can find more examples of similar transformations by reading the documentation of `by` and `mapcols` functions.

---

<div class="post-metadata">

**Author:** ![hasanOryx](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/hasanoryx/32/5373_2.png) [@hasanOryx](https://discourse.julialang.org/u/hasanOryx)\
**Post date:** [October 9, 2019, 8:03pm UTC](https://discourse.julialang.org/t/filtering-and-grouping-dataframe/29691/5 "2019-10-09T20:03:37Z")

</div>

Deeply appreciated, thanks a lot.