<commit_before>'''
Copyright (c) 2014-2016 <NAME>, <NAME> (<EMAIL>)
Copyright (c) 2016 <NAME>, Delft University of Technology (<EMAIL>)
Distributed under the MIT License.
See accompanying file LICENSE.md or copy at http://opensource.org/licenses/MIT
'''

# Set up modules and packages
# Plotting
# import matplotlib
# matplotlib.use('Agg')
# import matplotlib.pyplot as plt
# from matplotlib import gridspec
# from matplotlib import cm
# from mpl_toolkits.mplot3d import Axes3D
# from mpl_toolkits.mplot3d import axes3d
# from nlcmap import nlcmap

# I/O
import commentjson
import json
from pprint import pprint
import sqlite3

# Numerical
import numpy as np
import pandas as pd

# System
import sys
import time

print ""
print "------------------------------------------------------------------"
print "                               D2D                                "
print "                              0.0.2                               "
print "      Copyright (c) 2015-2016, <NAME> (<EMAIL>)      "
print "      Copyright (c) 2016, <NAME> (<EMAIL>)   "
print "------------------------------------------------------------------"
print ""

# Start timer.
start_time = time.time( )

print ""
print "******************************************************************"
print "                          Input parameters                        "
print "******************************************************************"
print ""

# Parse JSON configuration file
# Raise exception if wrong number of inputs are provided to script
if len(sys.argv) != 2:
    raise Exception("Only provide a JSON config file as input!")

json_data = open(sys.argv[1])
config = commentjson.load(json_data)
json_data.close()
pprint(config)

print ""
print "******************************************************************"
print "                            Operations                            "
print "******************************************************************"
print ""

print "Fetching scan data from database ..."

# for i in xrange(0,len(config['database'])):
for i in xrange(0,1):
    print "Computations being done on database:", config['database_tag'][i]
    print config['database'][i]
    try:
        database = sqlite3.connect(config['database'][i])

    except sqlite3.Error, e:

        print "Error %s:" % e.args[0]
        sys.exit(1)

    toplist = pd.read_sql("SELECT * FROM atom_scanner_results LIMIT 10", database)
    # toplist.colums = list(toplist)
    # toplist.columns = ['atom_transfer_id', 'lambert_transfer_id', 'atom_departure_delta_v_x', 'atom_departure_delta_v_y', 'atom_departure_delta_v_z', 'atom_arrival_delta_v_x', 'atom_arrival_delta_v_y', 'atom_arrival_delta_v_z', 'atom_transfer_delta_v']
    # print toplist['atom_transfer_id']
    # print toplist.columns


    toplist = pd.read_sql("SELECT *                                                               \
                           FROM lambert_scanner_results                                           \
                           INNER JOIN    atom_scanner_results                                     \
                           ON lambert_scanner_results.transfer_id                                 \
                              = atom_scanner_results.lambert_transfer_id                          \
                           GROUP BY lambert_scanner_results.departure_object_id,                  \
                                    lambert_scanner_results.arrival_object_id                     \
                           ORDER BY atom_scanner_results.atom_transfer_delta_v                    \
                                    ASC                                                           \
                           LIMIT 10000000                                                         \
                           ;",                                                                    \
                           database)
    test = pd.concat([toplist['transfer_id'],
                      # toplist['atom_transfer_id'],
                      toplist['departure_object_id'],
                      toplist['arrival_object_id'],
                      toplist['departure_epoch'],
                      toplist['time_of_flight'],
                      toplist['revolutions'],
                      # toplist['time_of_flight'],
                      toplist['transfer_delta_v'],
                      toplist['atom_transfer_delta_v'] ], axis=1)
    test['lambert_dv_ranking'] = test['transfer_delta_v'].rank('min')
    test['atom_dv_ranking'] = test['atom_transfer_delta_v'].rank('min')

    test.to_csv(str(config['output_directory'] + config['database_tag'][i] + ".csv"), index=False )

# SELECT * FROM atom_scanner_results INNER JOIN lambert_scanner_results ON lambert_scanner_results.transfer_id = atom_scanner_results.lambert_transfer_id WHERE lambert_scanner_results.transfer_id=14441079;
# Connect to SQLite database.
# try:
#     database = sqlite3.connect(config['database'])

# except sqlite3.Error, e:

#     print "Error %s:" % e.args[0]
#     sys.exit(1)


# departure_epochs = pd.read_sql("SELECT DISTINCT departure_epoch                                   \
#                                     FROM lambert_scanner_results;",                               \
#                                 database)
# for i in xrange(0,departure_epochs.size):
#     c = departure_epochs['departure_epoch'][i]
#     print "Plotting scan map with departure epoch: ",c,"Julian Date"

#     # Fetch scan data.
#     map_order = "departure_" + config['map_order']
#     scan_data = pd.read_sql("SELECT departure_object_id, arrival_object_id,                       \
#                                     min(transfer_delta_v), "+ map_order + "                       \
#                                 FROM lambert_scanner_results                                      \
#                                 WHERE departure_epoch BETWEEN " + str(c-0.00001) + "              \
#                                                       AND "+str(c+0.00001) +"                     \
#                                 GROUP BY departure_object_id, arrival_object_id;",                \
#                             database)
#     scan_data.columns = ['departure_object_id','arrival_object_id',                               \
#                          'transfer_delta_v',str(map_order)]<commit_msg>Improve sql extraction<commit_after>'''
Copyright (c) 2014-2016 <NAME>, <NAME> (<EMAIL>)
Copyright (c) 2016 <NAME>, Delft University of Technology (<EMAIL>)
Distributed under the MIT License.
See accompanying file LICENSE.md or copy at http://opensource.org/licenses/MIT
'''

# Set up modules and packages
# Plotting
# import matplotlib
# matplotlib.use('Agg')
# import matplotlib.pyplot as plt
# from matplotlib import gridspec
# from matplotlib import cm
# from mpl_toolkits.mplot3d import Axes3D
# from mpl_toolkits.mplot3d import axes3d
# from nlcmap import nlcmap

# I/O
import commentjson
import json
from pprint import pprint
import sqlite3

# Numerical
import numpy as np
import pandas as pd

# System
import sys
import time

print ""
print "------------------------------------------------------------------"
print "                               D2D                                "
print "                              0.0.2                               "
print "      Copyright (c) 2015-2016, <NAME> (<EMAIL>)      "
print "      Copyright (c) 2016, <NAME> (<EMAIL>)   "
print "------------------------------------------------------------------"
print ""

# Start timer.
start_time = time.time( )

print ""
print "******************************************************************"
print "                          Input parameters                        "
print "******************************************************************"
print ""

# Parse JSON configuration file
# Raise exception if wrong number of inputs are provided to script
if len(sys.argv) != 2:
    raise Exception("Only provide a JSON config file as input!")

json_data = open(sys.argv[1])
config = commentjson.load(json_data)
json_data.close()
pprint(config)

print ""
print "******************************************************************"
print "                            Operations                            "
print "******************************************************************"
print ""

print "Fetching scan data from database ..."

# for i in xrange(0,len(config['database'])):
for i in xrange(0,1):
    print "Computations being done on database:", config['database_tag'][i]
    print config['database'][i]
    try:
        database = sqlite3.connect(config['database'][i])

    except sqlite3.Error, e:

        print "Error %s:" % e.args[0]
        sys.exit(1)

    toplist = pd.read_sql("SELECT * FROM atom_scanner_results LIMIT 10", database)
    # toplist.colums = list(toplist)
    # toplist.columns = ['atom_transfer_id', 'lambert_transfer_id', 'atom_departure_delta_v_x', 'atom_departure_delta_v_y', 'atom_departure_delta_v_z', 'atom_arrival_delta_v_x', 'atom_arrival_delta_v_y', 'atom_arrival_delta_v_z', 'atom_transfer_delta_v']
    # print toplist['atom_transfer_id']
    # print toplist.columns


    toplist_atom = pd.read_sql("SELECT *, min(atom_scanner_results.atom_transfer_delta_v)                                                          \
                           FROM lambert_scanner_results                                           \
                           INNER JOIN    atom_scanner_results                                     \
                           ON lambert_scanner_results.transfer_id                                 \
                              = atom_scanner_results.lambert_transfer_id                          \
                           GROUP BY lambert_scanner_results.departure_object_id,                  \
                                    lambert_scanner_results.arrival_object_id                     \
                           ORDER BY atom_scanner_results.atom_transfer_delta_v                    \
                                    ASC                                                           \
                           LIMIT 10000000                                                         \
                           ;",                                                                    \
                           database)
    toplist_atom2 = pd.concat([ toplist_atom['transfer_id'],
                                # toplist_atom['lambert_transfer_id'],
                                toplist_atom['departure_object_id'],
                                toplist_atom['arrival_object_id'],
                                toplist_atom['departure_epoch'],
                                toplist_atom['time_of_flight'],
                                toplist_atom['revolutions'],
                                # toplist_atom['time_of_flight'],
                                toplist_atom['transfer_delta_v'],
                                toplist_atom['atom_transfer_delta_v'] ], axis=1)

                           # ORDER BY lambert_scanner_results.transfer_delta_v                      \
    # toplist_lambert = pd.read_sql("SELECT *, min(lambert_scanner_results.transfer_delta_v)        \
    toplist_lambert = pd.read_sql("SELECT *, min(lambert_scanner_results.arrival_delta_v_x)       \
                           FROM lambert_scanner_results                                           \
                           INNER JOIN    atom_scanner_results                                     \
                           ON lambert_scanner_results.transfer_id                                 \
                              = atom_scanner_results.lambert_transfer_id                          \
                           GROUP BY lambert_scanner_results.departure_object_id,                  \
                                    lambert_scanner_results.arrival_object_id                     \
                           ORDER BY lambert_scanner_results.arrival_delta_v_x                     \
                                    ASC                                                           \
                           LIMIT 10000000                                                         \
                           ;",                                                                    \
                           database)
    toplist_lambert2 = pd.concat([ toplist_lambert['transfer_id'],
                                   # toplist_lambert['atom_transfer_id'],
                                   toplist_lambert['departure_object_id'],
                                   toplist_lambert['arrival_object_id'],
                                   toplist_lambert['departure_epoch'],
                                   toplist_lambert['time_of_flight'],
                                   toplist_lambert['revolutions'],
                                   # toplist_lambert['time_of_flight'],
                                   toplist_lambert['transfer_delta_v'],
                                   toplist_lambert['atom_transfer_delta_v'] ], axis=1)

    toplist_atom2['atom_dv_ranking'] = toplist_atom2['atom_transfer_delta_v'].rank('min')
    toplist_lambert2['lambert_dv_ranking'] = toplist_lambert2['transfer_delta_v'].rank('min')
    toplist_atom2['combo'] = toplist_atom2['departure_object_id'].map(str) + '-' + toplist_atom2['arrival_object_id'].map(str)
    toplist_lambert2['combo'] = toplist_lambert2['departure_object_id'].map(str) + '-' + toplist_lambert2['arrival_object_id'].map(str)

    toplist_lambert2.join(toplist_atom2, on='combo', how='left', lsuffix="_atom")
    test =  pd.merge(toplist_atom2,toplist_lambert2, on='combo', how='outer')
    # test['lambert_dv_ranking'] = test['transfer_delta_v'].rank('min')
    # test['atom_dv_ranking'] = test['atom_transfer_delta_v'].rank('min')
    print 'toplist_atom2'
    print toplist_atom2
    print 'toplist_lambert2'
    print toplist_lambert2
    print 'test'
    print test
    test.to_csv(str(config['output_directory'] + config['database_tag'][i] + ".csv"), index=False )

# SELECT * FROM atom_scanner_results INNER JOIN lambert_scanner_results ON lambert_scanner_results.transfer_id = atom_scanner_results.lambert_transfer_id WHERE lambert_scanner_results.transfer_id=14441079;
# Connect to SQLite database.
# try:
#     database = sqlite3.connect(config['database'])

# except sqlite3.Error, e:

#     print "Error %s:" % e.args[0]
#     sys.exit(1)


# departure_epochs = pd.read_sql("SELECT DISTINCT departure_epoch                                   \
#                                     FROM lambert_scanner_results;",                               \
#                                 database)
# for i in xrange(0,departure_epochs.size):
#     c = departure_epochs['departure_epoch'][i]
#     print "Plotting scan map with departure epoch: ",c,"<NAME>"

#     # Fetch scan data.
#     map_order = "departure_" + config['map_order']
#     scan_data = pd.read_sql("SELECT departure_object_id, arrival_object_id,                       \
#                                     min(transfer_delta_v), "+ map_order + "                       \
#                                 FROM lambert_scanner_results                                      \
#                                 WHERE departure_epoch BETWEEN " + str(c-0.00001) + "              \
#                                                       AND "+str(c+0.00001) +"                     \
#                                 GROUP BY departure_object_id, arrival_object_id;",                \
#                             database)
#     scan_data.columns = ['departure_object_id','arrival_object_id',                               \
#                          'transfer_delta_v',str(map_order)]
{'fraction_non_alphanumeric': 0.13363705391040243, 'fraction_numerical': 0.011705897241204759, 'mean_word_length': 1.4503875968992248, 'pattern_counts': {'":': 0, '<': 22, '<?xml version=': 0, '>': 22, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 3, 'word_list_counts': {'cursed_substrings.json': 80, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '2332271', 'n_tokens_mistral': 4257, 'n_tokens_neox': 3920, 'n_words': 735}