-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathweb_database.py
More file actions
47 lines (34 loc) · 1.46 KB
/
web_database.py
File metadata and controls
47 lines (34 loc) · 1.46 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
from datetime import datetime, timedelta
from pymongo import MongoClient
import numpy as np
import os
import pandas as pd
mongourl = os.environ['MONGOURL']
client = MongoClient(mongourl)
ASSET_CLASSES = ['COMMODITIES', 'CREDITS', 'FOREX', 'RATES']
for asset in ASSET_CLASSES:
client.drop_database(asset)
file_date = (datetime.today() - timedelta(days=1)).strftime('%Y_%m_%d')
file_name = f"CFTC_CUMULATIVE_{asset}_{file_date}.csv"
df = pd.read_csv(file_name, low_memory=False)
df['Event timestamp'] = pd.to_datetime(df['Event timestamp'])
df['Execution Timestamp'] = pd.to_datetime(df['Execution Timestamp'])
df['Expiration Date'] = pd.to_datetime(df['Expiration Date'])
df['Effective Date'] = pd.to_datetime(df['Effective Date'])
df.rename(columns={
'Dissemination Identifier': '_id',
'Product name': 'Trade Structure'
}, inplace=True)
df_dict = df.to_dict('records')
db = client[asset]
for item in df_dict:
if str(item['Effective Date']) == 'NaT':
item['Effective Date'] = np.nan
if str(item['Event timestamp']) == 'NaT':
item['Event timestamp'] = np.nan
if str(item['Execution Timestamp']) == 'NaT':
item['Execution Timestamp'] = np.nan
if str(item['Expiration Date']) == 'NaT':
item['Expiration Date'] = np.nan
db['all_records'].insert_many(df_dict)
db['all_records'].create_index({'Event timestamp': 1})