import pandas as pd
data = pd.read_csv('http://files.gramener.com/data/singapore-property-data.csv')

data.irow(0)

import json
import requests

root = 'http://localhost:9200'
database = root + '/singapore/property'
requests.delete(database).json()

# Loop through the first 5 row and add the document. The ID is the index
for index, row in data.head().iterrows():
    print requests.put(database + '/%d' % index, data=row.to_json()).json()

bulk_command = []
action = json.dumps({
    "index": {
        "_index": "singapore",
        "_type": "property",
        "_id": "%d"
    }
})
for index, row in data.iterrows():
    bulk_command.append(action % index + '\n')
    bulk_command.append(row.to_json() + '\n')

result = requests.post(root + '/_bulk', data=''.join(bulk_command)).json()
print 'Errors:', result['errors']

requests.get(database + '/_count').json()

len(data)

requests.post(database + '/_count').json()['count']

data.groupby('year')['Project Name'].count()

requests.post(database + '/_search?search_type=count', data=json.dumps({
    "size": 0,
    "aggs": {
        "yearwise": {
            "terms": {
                "field": "year"
            }
        }
    }
})).json()['aggregations']['yearwise']['buckets']

data.groupby('year')['Total Number of Units in Project'].sum()

requests.post(database + '/_search?search_type=count', data=json.dumps({
    "size": 0,
    "aggs": {
        "yearwise": {
            "terms": {
                "field": "year"
            },
            "aggs": {
                "total_units": {
                    "sum": {
                        "field": "Total Number of Units in Project"
                    }
                }
            }
        }
    }
})).json()['aggregations']['yearwise']['buckets']

# Shutdown
requests.post(root + '/_shutdown').json()