//// Python API

// Set up Python API

python setup.py install

python setup.py install --force


// Python API functionalities

import pandas as pd
import numpy as np
import dolphindb as ddb

conn=ddb.session()
conn.connect( 'localhost', 8848)

df = pd.DataFrame({'id': np.int32([1, 2, 3, 4, 3]), 'value':  np.double([7.8, 4.6, 5.1, 9.6, 0.1]), 'x': np.int32([5, 4, 3, 2, 1])})
conn.upload({'t1': df})

conn.run('wavg', [100, 60, 300], [1, 1.5, 2])

df = pd.DataFrame({'id': np.int32([1, 2, 3, 4, 3]), 'value':  np.double([7.8, 4.6, 5.1, 9.6, 0.1]), 'x': np.int32([5, 4, 3, 2, 1])})
conn.upload({'t1': df})
conn.run('t1.value.avg()')

conn.run('1 2 NULL')

z= conn.run("dict(1 2 3, `IBM`MSFT`GOOG)")
z
z[1]

conn.run('(1..6).reshape(3:2)')

table_str = "n=20000\n"
table_str += "t1=table(09:30:00+rand(18000, n) as timestamp, rand(100, n) as id, 100*(1+rand(100, n)) as qty, 5.0+rand(100.0,n) as price);\n"
table_str += "select * from t1 where price>9"                                                  
df = conn.run(table_str)
df

df.columns

dt = conn.table(data={'sym': ['A', 'B', 'B', 'A', 'A'], 'vol': [1, 3, 2, 5, 4], 'price': [16, 31, 28, 19, 22]})
dt.tableName()

dt.showSQL()

dt['price'].showSQL()

dt.select('price').showSQL()

dt.select("price").where(dt.price > 20).showSQL()

dt.select("price").where(dt.price > 20).sort('price').showSQL()

dt.select(['sym','vol']).where(dt.price > 20).sort(['sym','vol desc']).showSQL()

dt[dt.price > 20].showSQL()

dt.toDF()

dt['price'].toDF()

dt.select('price').toDF()

dt.select("price").where(dt.price > 20).toDF()

dt.select("price").where(dt.price > 20).sort('price').toDF()

dt.select(['sym','vol']).where(dt.price > 20).sort(['sym','vol desc']).toDF()

dt[dt.price > 20].toDF()

dt[dt.price > 20].groupby('sym').agg('sum').showSQL()

dt[dt.price > 20].groupby('sym').agg('sum').toDF()

dt.groupby('sym').agg({'vol':[ddb.sum2]}).showSQL()

dt.groupby('sym').agg({'vol':[ddb.sum2]}).toDF()

dt.groupby('sym').agg2([ddb.wsum, ddb.wavg], [("price", "vol")]).showSQL()

dt.groupby('sym').agg2([ddb.wsum, ddb.wavg], [("price", "vol")]).toDF()

dt.contextby('sym').agg({'price':[ddb.sum]}).showSQL()

dt.contextby('sym').agg({'price':[ddb.sum]}).toDF()

dt.contextby('sym').agg({'price':[ddb.cumsum]}).showSQL()

dt.contextby('sym').agg({'price':[ddb.cumsum]}).toDF()

dt.pivotby(index='vol', column='sym', value='price').showSQL()

dt.pivotby(index='vol', column='sym', value='price').toDF()

dt1 = conn.table(data={'sym': ['AAPL', 'AAPL', 'AAPL', 'AMZN', 'AMZN'], 'date': ['2017-01-02', '2017-01-03', '2017-01-04', '2017-01-05', '2017-01-06'], 'price': [122, 125, 123, 756, 767]})
dt2 = conn.table(data={'sym': ['AAPL', 'AAPL', 'AMZN', 'AMZN', 'AMZN'], 'date': ['2017-01-04', '2017-01-05', '2017-01-06', '2017-01-07', '2017-01-10'], 'vol': [1100, 1400, 1600, 2200, 1800]})
dt1.toDF();
dt2.toDF();
dt1.merge(dt2, left_on=['sym','date'], right_on=['sym','date']).showSQL()
dt1.merge(dt2, left_on=['sym','date'], right_on=['sym','date']).toDF()
dt1.merge(dt2, left_on=['sym','date'], right_on=['sym','date'], how='left').showSQL()
dt1.merge(dt2, left_on=['sym','date'], right_on=['sym','date'], how='left').toDF()

dt1 = conn.table(data={'id': ['A','A','A','B','B'], 'date': pd.to_datetime(['2017-02-06', '2017-02-08', '2017-02-10', '2017-02-07', '2017-02-09']), 'price': [22, 23, 20, 100, 102]})
dt2 = conn.table(data={'id': ['A','A','B','B','B'], 'date': pd.to_datetime(['2017-02-07', '2017-02-10', '2017-02-07', '2017-02-08', '2017-02-10'])})
dt1.toDF()
dt2.toDF()
dt2.merge_asof(dt1, on=['id','date']).showSQL()
dt2.merge_asof(dt1, on=['id','date']).toDF()