Repository: onesuper/pandasticsearch

Issue:
KeyError: properties in DataFrame.print_schema running against ES 7.x
Hi team, 
I installed pandasticsearch version 0.5.1 using 
```
pip install pandasticsearch[pandas]
```

and I tried to run this code


```
df = DataFrame.from_es(url='http://localhost:9200', index='my_index')
df.print_schema()
```

I get the following stacktrace

```
<ipython-input-9-cee54abe7265> in <module>
      1 from pandasticsearch import DataFrame
----> 2 df = DataFrame.from_es(url='http://localhost:9200', index='my_index')
      3 df.print_schema()

~/<redacted>/env/lib/python3.7/site-packages/pandasticsearch/dataframe.py in from_es(**kwargs)
    122             endpoint = index + '/' + doc_type + '/_search'
    123         return DataFrame(client=RestClient(url, endpoint, username, password, verify_ssl),
--> 124                          mapping=mapping, index=index, doc_type=doc_type, compat=compat)
    125 
    126     def __getattr__(self, name):

~/<redacted>/env/lib/python3.7/site-packages/pandasticsearch/dataframe.py in __init__(self, **kwargs)
     40         self._index = list(self._mapping.keys())[0] if self._mapping else None
     41         self._doc_type = DataFrame._get_doc_type(self._mapping) if self._mapping else None
---> 42         self._columns = sorted(DataFrame._get_cols(self._mapping)) if self._mapping else None
     43         self._filter = kwargs.get('filter', None)
     44         self._groupby = kwargs.get('groupby', None)

~/<redacted>env/lib/python3.7/site-packages/pandasticsearch/dataframe.py in _get_cols(cls, mapping)
    492         index = list(mapping.values())[0]  # {'index': {}}
    493         for _, properties in six.iteritems(index['mappings']):
--> 494             for k, _ in six.iteritems(properties['properties']):
    495                 cols.append(k)
    496 

KeyError: 'properties'

```

It looks like this might have something to do with removing types, which is a breaking change between ES 6.x and 7.x. I can try to dig into this a bit later.

Discussion:

Patch:
diff --git a/pandasticsearch/dataframe.py b/pandasticsearch/dataframe.py
index 1f372db..1159dd6 100644
--- a/pandasticsearch/dataframe.py
+++ b/pandasticsearch/dataframe.py
@@ -138,7 +138,7 @@ class DataFrame(object):
                 raise TypeError('Column does not exist: [{0}]'.format(item))
             return Column(item)
         elif isinstance(item, BooleanFilter):
-            self._filter = item.build()
+            self._filter = item
             return self
         else:
             raise TypeError('Unsupported expr: [{0}]'.format(item))
@@ -434,10 +434,18 @@ class DataFrame(object):
 
         sys.stdout.write('{0}\n'.format(self._index))
         index = list(self._mapping.values())[0]  # {'index': {}}
-        for typ, properties in six.iteritems(index['mappings']):
-            sys.stdout.write('|--{0}\n'.format(typ))
-            for k, v in six.iteritems(properties['properties']):
-                sys.stdout.write('  |--{0}: {1}\n'.format(k, v))
+        schema = self.resolve_schema(index['mappings']['properties'])
+        sys.stdout.write(schema)
+
+    def resolve_schema(self, json_prop, res_schema="", depth=1):
+        for field in json_prop:
+            if "properties" in json_prop[field]:
+                res_schema += f"{'  '*depth}|--{field}:\n"
+                res_schema = self.resolve_schema(json_prop[field]["properties"],
+                                                 res_schema, depth=depth+1)
+            else:
+                res_schema += f"{'  ' * depth}|--{field}: {json_prop[field]}\n"
+        return res_schema
 
     def _build_query(self):
         query = dict()
@@ -488,16 +496,31 @@ class DataFrame(object):
 
     @classmethod
     def _get_cols(cls, mapping):
-        cols = []
-        index = list(mapping.values())[0]  # {'index': {}}
-        for _, properties in six.iteritems(index['mappings']):
-            for k, _ in six.iteritems(properties['properties']):
-                cols.append(k)
+        index = list(mapping.keys())[0]
+        cols = cls.get_mappings(mapping, index)
 
         if len(cols) == 0:
             raise Exception('0 columns found in mapping')
         return cols
 
+    @classmethod
+    def resolve_mappings(cls, json_map):
+        prop = []
+        for field in json_map:
+            nested_props = []
+            if "properties" in json_map[field]:
+                nested_props = cls.resolve_mappings(json_map[field]["properties"])
+            if len(nested_props) == 0:
+                prop.append(field)
+            else:
+                for nested_prop in nested_props:
+                    prop.append(f"{field}.{nested_prop}")
+        return prop
+
+    @classmethod
+    def get_mappings(cls, json_map, index_name):
+        return cls.resolve_mappings(json_map[index_name]["mappings"]["properties"])
+
     @classmethod
     def _get_doc_type(cls, mapping):
         index = list(mapping.values())[0]  # {'index': {}}
diff --git a/pandasticsearch/queries.py b/pandasticsearch/queries.py
index 15e5fc1..19b5c78 100644
--- a/pandasticsearch/queries.py
+++ b/pandasticsearch/queries.py
@@ -74,6 +74,18 @@ class Select(Query):
     def __init__(self):
         super(Select, self).__init__()
 
+    def resolve_fields(self, row):
+        fields = {}
+        for field in row:
+            nested_fields = {}
+            if isinstance(row[field], dict):
+                nested_fields = self.resolve_fields(row[field])
+                for n_field, val in nested_fields.items():
+                    fields[f"{field}.{n_field}"] = val
+            else:
+                fields[field] = row[field]
+        return fields
+
     def explain_result(self, result=None):
         super(Select, self).explain_result(result)
         rows = []
@@ -81,7 +93,8 @@ class Select(Query):
             row = {}
             for k in hit.keys():
                 if k == '_source':
-                    row.update(hit['_source'])
+                    solved_fields = self.resolve_fields(hit['_source'])
+                    row.update(solved_fields)
                 elif k.startswith('_'):
                     row[k] = hit[k]
             rows.append(row)


Test patch:
diff --git a/tests/test_dataframe.py b/tests/test_dataframe.py
index f139d54..cc2cc8e 100644
--- a/tests/test_dataframe.py
+++ b/tests/test_dataframe.py
@@ -9,8 +9,30 @@ from pandasticsearch.operators import *
 @patch('pandasticsearch.client.urllib.request.urlopen')
 def create_df_from_es(mock_urlopen):
     response = Mock()
-    dic = {"index": {"mappings": {"doc_type": {"properties": {"a": {"type": "integer"},
-                                                              "b": {"type": "integer"}}}}}}
+    dic = {
+      "metricbeat-7.0.0-qa": {
+        "mappings": {
+          "_meta": {
+            "beat": "metricbeat",
+            "version": "7.0.0"
+          },
+          "date_detection": False,
+          "properties": {
+            "a": {
+              "type": "integer"
+            },
+            "b": {
+              "type": "integer"
+            },
+            "c": {"properties": {
+                "d": {"type": "keyword", "ignore_above": 1024},
+                "e": {"type": "keyword", "ignore_above": 1024}
+              }
+            }
+          }
+        }
+      }
+    }
     response.read.return_value = json.dumps(dic).encode("utf-8")
     mock_urlopen.return_value = response
     return DataFrame.from_es(url="http://localhost:9200", index='xxx')
@@ -25,7 +47,7 @@ class TestDataFrame(unittest.TestCase):
         expr = df['a'] > 2
         self.assertTrue(isinstance(expr, BooleanFilter))
         self.assertTrue(isinstance(df[expr], DataFrame))
-        self.assertEqual(df[expr]._filter, {'range': {'a': {'gt': 2}}})
+        self.assertEqual(df[expr]._filter.build(), {'range': {'a': {'gt': 2}}})
 
     def test_getattr(self):
         df = create_df_from_es()
@@ -34,7 +56,7 @@ class TestDataFrame(unittest.TestCase):
 
     def test_columns(self):
         df = create_df_from_es()
-        self.assertEqual(df.columns, ['a', 'b'])
+        self.assertEqual(df.columns, ['a', 'b', 'c.d', 'c.e'])
 
     def test_init(self):
         df = create_df_from_es()