# Aggregate data to month level from csv file

**URL:** <https://discuss.elastic.co/t/aggregate-data-to-month-level-from-csv-file/141956>\
**Category:** Logstash\
**Created:** [July 27, 2018, 2:33pm UTC](https://discuss.elastic.co/t/aggregate-data-to-month-level-from-csv-file/141956 "2018-07-27T14:33:41Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![rkhapre](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/rkhapre/32/48333_2.png) [@rkhapre](https://discuss.elastic.co/u/rkhapre)\
**Post date:** [July 27, 2018, 2:33pm UTC](https://discuss.elastic.co/t/aggregate-data-to-month-level-from-csv-file/141956/1 "2018-07-27T14:33:41Z")

</div>

Hi All

I have a dataset in below form in the csv format

> | Order | Date | Amount |
> | --- | --- | --- |
> | Order001 | 10-Jan-2018 | 10 |
> | Order001 | 11-Jan-2018 | 20 |
> | Order001 | 12-Jan-2018 | 30 |
> | Order001 | 13-Jan-2018 | 40 |
> | Order002 | 14-Jan-2018 | 50 |
> | Order002 | 15-Jan-2018 | 60 |
> | Order002 | 16-Jan-2018 | 70 |
> | Order003 | 2-Feb-2018 | 80 |
> | Order003 | 3-Feb-2018 | 90 |
> | Order003 | 4-Feb-2018 | 100 |
> | Order004 | 5-Feb-2018 | 20 |
> | Order004 | 6-Feb-2018 | 10 |
> | Order005 | 7-Feb-2018 | 10 |

I want expected output in the below form, which is data summed up at month level

> | Month | Amount |
> | --- | --- |
> | Jan-18 | 280 |
> | Feb-18 | 310 |

Since my dataset is in csv format, i am using following conf file, what filter plugin i should use to aggregate data at month level

```
input{
	file {
	      path => "/xyz.csv"
		  sincedb_path => "/xyz.txt"
          type => "data"
          start_position => "beginning"
      }
	} 
filter {
			
			csv {
			columns => ["Order","Date","Amount"]
			separator => ","
		}
		mutate {  
convert => { "Amount" => "integer"}
 }  
date { match => ["Date", "dd-MMM-yyyy"] 
 target => "Date"} 

		
	} 
output {  
stdout { codec => rubydebug }  
		.......
		{  
			action => "index"   
			hosts => "https://localhost:xxxx"
			........
		}
}

```

Thanks!

---

<div class="post-metadata">

**Author:** ![Badger](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/badger/32/25190_2.png) [@Badger](https://discuss.elastic.co/u/Badger)\
**Post date:** [July 27, 2018, 2:54pm UTC](https://discuss.elastic.co/t/aggregate-data-to-month-level-from-csv-file/141956/2 "2018-07-27T14:54:14Z")

</div>

It looks a lot like [example 4](https://www.elastic.co/guide/en/logstash/current/plugins-filters-aggregate.html#plugins-filters-aggregate-example4) in the documentation for the aggregate filter.

```
    mutate {
        convert => { "Amount" => "integer"}
        gsub => ["Date", ".*-([a-zA-Z]+)-20([0-9]+)", "\1-\2" ]
    }
    aggregate {
        task_id => "%{Date}"
        code => "
            map['Date'] = event.get('Date')
            map['sum'] ||= 0
            map['sum'] += event.get('Amount')
            event.cancel()
        "
        push_previous_map_as_event => true
        timeout => 3
    }
```

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [August 24, 2018, 2:54pm UTC](https://discuss.elastic.co/t/aggregate-data-to-month-level-from-csv-file/141956/3 "2018-08-24T14:54:21Z")

</div>

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.