How to export pandas data to elasticsearch?

Question

It is possible to export a pandas dataframe data to elasticsearch using elasticsearch-py. For example, here is some code:

https://www.analyticsvidhya.com/blog/2017/05/beginners-guide-to-data-exploration-using-elastic-search-and-kibana/

There are a lot of similar methods like to_excel, to_csv, to_sql.

Is there a to_elastic method? If no, where should I request it?

Answer 1

The following script works for localhost:

import numpy as np
import pandas as pd

df = pd.DataFrame(np.random.randint(0,100,size=(100, 4)), columns=list('ABCD'))

INDEX="dataframe"
TYPE= "record"

def rec_to_actions(df):
    import json
    for record in df.to_dict(orient="records"):
        yield ('{ "index" : { "_index" : "%s", "_type" : "%s" }}'% (INDEX, TYPE))
        yield (json.dumps(record, default=int))

from elasticsearch import Elasticsearch
e = Elasticsearch() # no args, connect to localhost:9200
if not e.indices.exists(INDEX):
    raise RuntimeError('index does not exists, use `curl -X PUT "localhost:9200/%s"` and try again'%INDEX)

r = e.bulk(rec_to_actions(df)) # return a dict

print(not r["errors"])

Verify using curl -g 'http://localhost:9200/dataframe/_search?q=A:[29%20TO%2039]'

There are many little things that can be added to suit different needs but main is there.

Answer 2

I'm not aware of any to_elastic method integrated in pandas. You can always raise an issue on the pandas github repo or create a pull request.

However, there is espandas which allows to import a pandas DataFrame to elasticsearch. The following example from the README has been tested with Elasticsearch 6.2.1.

import pandas as pd
import numpy as np
from espandas import Espandas

df = (100 * pd.DataFrame(np.round(np.random.rand(100, 5), 2))).astype(int)
df.columns = ['A', 'B', 'C', 'D', 'E']
df['indexId'] = (df.index + 100).astype(str)

INDEX = 'foo_index'
TYPE = 'bar_type'
esp = Espandas()
esp.es_write(df, INDEX, TYPE)

Retrieving the mappings with GET foo_index/_mappings:

{
  "foo_index": {
    "mappings": {
      "bar_type": {
        "properties": {
          "A": {
            "type": "long"
          },
          "B": {
            "type": "long"
          },
          "C": {
            "type": "long"
          },
          "D": {
            "type": "long"
          },
          "E": {
            "type": "long"
          },
          "indexId": {
            "type": "text",
            "fields": {
              "keyword": {
                "type": "keyword",
                "ignore_above": 256
              }
            }
          }
        }
      }
    }
  }
}