# Pivot the field using csv data in LS

**URL:** <https://discuss.elastic.co/t/pivot-the-field-using-csv-data-in-ls/192539>\
**Category:** Logstash\
**Created:** [July 27, 2019, 9:28pm UTC](https://discuss.elastic.co/t/pivot-the-field-using-csv-data-in-ls/192539 "2019-07-27T21:28:42Z")\
**Posts on this page:** 12\
**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, 2019, 9:28pm UTC](https://discuss.elastic.co/t/pivot-the-field-using-csv-data-in-ls/192539/1 "2019-07-27T21:28:43Z")

</div>

Hi All

I have a csv data like this

Object#,Type,Count  
A001,P0,5  
A002,P1,3  
A003,P0,4  
A005,P1,2  
A006,P2,2

I need to output in this below format, i tried with ruby filter, but not sure which gem to use exactly

Please let me know if someone has done something similar, using ruby filter

![image](https://us1.discourse-cdn.com/elastic/original/3X/d/1/d1bb9f616190b57d719d25b2e8b7bd6d95dabea9.png)

I have input as .csv file and need output as .csv file

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, 2019, 11:43pm UTC](https://discuss.elastic.co/t/pivot-the-field-using-csv-data-in-ls/192539/2 "2019-07-27T23:43:48Z")

</div>

So you want the number from the second column to determine which output column the third input column appears in?

---

<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 28, 2019, 5:03am UTC](https://discuss.elastic.co/t/pivot-the-field-using-csv-data-in-ls/192539/3 "2019-07-28T05:03:43Z")

</div>

Yes, the values from second column, will become the column header and the value of third column will be segregated accordingly.

Any idea how we can do this?

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 28, 2019, 11:18am UTC](https://discuss.elastic.co/t/pivot-the-field-using-csv-data-in-ls/192539/4 "2019-07-28T11:18:57Z")

</div>

```
    csv { autodetect_column_names => true remove_field => ["message"] }
    if [Type] == "P0" {
        mutate { add_field => { "P0" => "%{Count}" } }
    } else {
        mutate { add_field => { "P0" => "" } }
    }
    if [Type] == "P1" {
        mutate { add_field => { "P1" => "%{Count}" } }
    } else {
        mutate { add_field => { "P1" => "" } }
    }
    if [Type] == "P2" {
        mutate { add_field => { "P2" => "%{Count}" } }
    } else {
        mutate { add_field => { "P2" => "" } }
    }
```

---

<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 28, 2019, 11:24am UTC](https://discuss.elastic.co/t/pivot-the-field-using-csv-data-in-ls/192539/5 "2019-07-28T11:24:55Z")

</div>

Hi Thanks for you reply.

I am looking for some dynamic solution. With the above i have given a example wherein i have 3 values as P0, P1, P2 to ask question in a simple manner

In my real dataset the value of Type goes to between 50 -70 approx.  
This keep changing on monthly basis. So i am looking for some dynamic solution  
With every pipeline run the values from Type , will become the column

i have tried this [gem](https://github.com/edjames/pivot_table) , but not sure how to really construct with ruby filter

The above solution that you provided is more a hardcoded data with value as P0,P1 and P2

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 28, 2019, 5:25pm UTC](https://discuss.elastic.co/t/pivot-the-field-using-csv-data-in-ls/192539/6 "2019-07-28T17:25:53Z")

</div>

The csv output will output empty fields is the event is missing them, so all you really need is

```
    csv { autodetect_column_names => true remove_field => ["message"] }
    ruby { code => 'event.set(event.get("Type"), event.get("Count"))' }

```

The fields option on the filter is not optional, but you could use a technique similar to [this](https://discuss.elastic.co/t/export-to-csv-with-all-fields-logstash-7-0-1/187420/2) to find the set of fields required.

---

<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 30, 2019, 10:18am UTC](https://discuss.elastic.co/t/pivot-the-field-using-csv-data-in-ls/192539/7 "2019-07-30T10:18:43Z")

</div>

Hi @Badger

I tried this but it seems i am getting correct output in stdout but not in csv. This below i tried

1. I am not able to generate the csv fields dynamically ( the mandatory one fields =\> [" ", " ".....]
2. I am not able to give headers to the csv output.

Any insight, really appreciate  
Filter

> ruby {  
> code =\> '  
> csvFields =   
> event.to\_hash.each { |k, v|  
> csvFields \<\< k  
> }  
> event.set("[csvFields]", csvFields.to\_s)  
> '  
> }  
> }

Output

> output {  
> csv {  
> path =\> "....................\output.csv"  
> fields =\> ["%{csvFields}"]
> 
> }  
> }

---

<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 30, 2019, 1:43pm UTC](https://discuss.elastic.co/t/pivot-the-field-using-csv-data-in-ls/192539/8 "2019-07-30T13:43:05Z")

</div>

As I noted in the post I linked to, you cannot use either %{[fieldname]} or ${envVar} in the fields option for a csv output. The idea is to run the complete data set through logstash to generate the value required for that option, then you can copy and paste it.

```
filter {
    csv { autodetect_column_names => true }
    ruby {
        init => '@csvFields = []'
        code => '
            t = event.get("Type")
            unless @csvFields.include? t
                @csvFields << t
            end
            event.set("csvFields", @csvFields.to_s)
        '
    }
}
output { stdout { codec => plain { format => "%{csvFields}
" } } }

```

will produce

```auto
["P0"]
["P0", "P1"]
["P0", "P1"]
["P0", "P1"]
["P0", "P1", "P2"]

```

Just take the last line and paste it into your config

---

<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 30, 2019, 6:23pm UTC](https://discuss.elastic.co/t/pivot-the-field-using-csv-data-in-ls/192539/9 "2019-07-30T18:23:36Z")

</div>

Hi @Badger , thanks for replying back. I tried again multiple times, looks like i am missing something.  
Please checkout this my complete file. Please suggest whats wrong here, i am not a big expert of ruby.  
Are you saying i need 2 outputs one on csv and other stdout, i am not clear on it

> input{  
> file {  
> path =\> "......./cumsum.csv"  
> sincedb\_path =\> "......../cumsum.txt"  
> start\_position =\> "beginning"  
> type =\> "csv"  
> }  
> }
> 
> filter {  
> csv {  
> autodetect\_column\_names =\> true  
> remove\_field =\> ["message"]  
> }  
> ruby {  
> code =\> '  
> csvFields =   
> event.to\_hash.each { |k, v|  
> csvFields \<\< k  
> }  
> event.set("[csvFields]", csvFields.to\_s)  
> '  
> }  
> ruby {  
> code =\> 'event.set(event.get("Type"), event.get("Count"))'  
> }  
> }
> 
> output {  
> file {  
> path =\> ".........\output.csv"  
> codec =\> plain { format =\> "%{csvFields}" }  
> }  
> }

I am getting this response in the output.csv file

`

["A001", "type", "@timestamp", "5", "P0", "@version", "path", "host"]["A001", "type", "@timestamp", "5", "P0", "@version", "path", "host"]["A001", "type", "@timestamp", "5", "P0", "@version", "path", "host"]["A001", "type", "@timestamp", "5", "P0", "@version", "path", "host"]

`

---

<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 30, 2019, 8:15pm UTC](https://discuss.elastic.co/t/pivot-the-field-using-csv-data-in-ls/192539/10 "2019-07-30T20:15:32Z")

</div>

You have input data that contains an unknown set of values in the [Type] field. The idea is that we run that data through logstash in order to find what that set of values is. For that you should use the stdout output.

The last line of output will be a string like

```
["P0", "P1", "P2"]

```

which you can copy and paste into the fields option of a csv output.

---

<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 31, 2019, 2:26am UTC](https://discuss.elastic.co/t/pivot-the-field-using-csv-data-in-ls/192539/11 "2019-07-31T02:26:58Z")

</div>

okay, i was looking for full automation through pipeline, here there will be manual intervention on copying the fields for csv output.

Also, i was looking for headers in csv.

Anyways thanks for your help, i will look for some other solution

---

<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 28, 2019, 2:27am UTC](https://discuss.elastic.co/t/pivot-the-field-using-csv-data-in-ls/192539/12 "2019-08-28T02:27:06Z")

</div>

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