Untitled

mail@pastecode.io avatar
unknown
python
a year ago
10 kB
2
Indexable
Never
import os
import json
import pandas as pd
import psycopg2
import configparser
from pymongo import MongoClient
import boto3
from sqlalchemy import create_engine
import io
import re


config = configparser.RawConfigParser()
config.read(os.path.join(os.getcwd() , '../config/config.property'))

#Database Connection
MONGODB = dict(config.items('MONGODB'))
MONGODB_HOST = MONGODB['host']
MONGODB_PORT = int(MONGODB['port'])
MONGODB_DATABASE = MONGODB['dbname']
MONGODB_COLLECTION1 = MONGODB["hospital_structure_details"]

MONGO_CLIENT = MongoClient(MONGODB_HOST, MONGODB_PORT)
MONGODB_DB = MONGO_CLIENT[MONGODB_DATABASE]
HOSPITAL_STRUCTURE_DETAILS= MONGODB_DB[MONGODB_COLLECTION1]     #Evaluation Batch Detail


#S3 credentials
S3BUCKET = dict(config.items("S3BUCKET"))
ACCESS_KEY_ID=S3BUCKET["keyid"]
AWS_SECRET_ACCESS_KEY=S3BUCKET["accesskey"]
REGION=S3BUCKET["region"] 
BUCKET=S3BUCKET['bucket_name']
FOLDER = S3BUCKET['folder_name']


#POSTGRES credentials
POSTGRES = dict(config.items("POSTGRES"))
HOST=POSTGRES["host"]
PORT=POSTGRES["port"]
DATABASE=POSTGRES["database"] 
USER=POSTGRES['user']
PASSWORD = POSTGRES['password']



#HOSPITAL_ID
HOSPITAL = dict(config.items("HOSPITAL_ID"))
HOSPITAL_ID = HOSPITAL['hospital_id']



ref_dict = {
    'hospital_id':'',
    'page':'',
    'table':'',
    's3url':'',
    'service_code':'STRUCT011',
    'category':'STRUCT008',
    'subcategory':'STRUCT009',
    'service_description':'STRUCT003',
    'bounding_box_sd':'',
    'type_of_room':'STRUCT001',
    'price_per_unit':'STRUCT002',
    'remarks':'STRUCT004',
    'los':'STRUCT007',
    'inclusions':'STRUCT005',
    'exclusions':'STRUCT006',
    'start_date':'',
    'end_date':'',
    'tariff_type':'',
    'room_mapping':'',
    'confidence_score_sd':'',
    'standard_term':''
}

def create_postgres_data(json_file):
    json_response = json_file['response']
    col_dict = column_dict(json_response)
    print('coldict : ', col_dict)
    row_dict = row_dictionary(json_response)    
    print('rowdict : ', row_dict)
    df = populate_df(json_file, row_dict, col_dict) 
    # df.to_excel('/home/rishabh/Desktop/SOC_UI/downloadHospital/data/out3.xlsx', index=False)
    return df


def row_dictionary(json_response):
    row_dict = {}
    des_list=[]
    for i in json_response:
        if i['label']=='STRUCT003':
            des_list.append(i['value'])
    description_list = list(set(des_list))
    # print(description_list)
    for description in description_list:
        row=[]
        for res in json_response:
            if res['value']==description:
                row_number = res['fieldType'].split('R')[-1]
                row = row+[row_number]
        row_dict[description]=row
    row_dict = dict(sorted(row_dict.items(), key=lambda x: int(x[1][0])))
    # print(row_dict)
    return row_dict


def column_dict(json_response):
    col_dict = {}
    
    for ref in ref_dict:
        val = ref_dict[ref]
        if val !='':
            for i in range(0,len(json_response)):
                if val==json_response[i]['label']:
                    column = (json_response[i]['fieldType'].split('R')[0]).split('C')[-1]
                    col_dict[val]=str(column)
    return col_dict


def populate_df(json_file, row_dict, col_dict):
    json_response = json_file['response']
    data=[]
    for service_desc in row_dict:  
        for row_num in row_dict[service_desc]:
            post_dict = {}
            post_dict['hospital_id']=json_file['hospitalId']
            post_dict['page']=json_file['page']
            post_dict['table']=json_file['table']
            post_dict['s3url']=json_file['pageViewURL']
            try:
                post_dict['service_code']=str(cell_value(json_response, row_num, col_dict[ref_dict['service_code']]))
            except:
                post_dict['service_code']=''
            try:
                post_dict['category']=str(cell_value(json_response, row_num, col_dict[ref_dict['category']]))
            except:
                post_dict['category']=''
            try:
                post_dict['subcategory']=str(cell_value(json_response, row_num, col_dict[ref_dict['subcategory']]))
            except:
                post_dict['subcategory']=''
            try:
                post_dict['type_of_room']=str(cell_value(json_response, row_num, col_dict[ref_dict['type_of_room']]))
            except:
                post_dict['type_of_room']=''
            try:
                post_dict['price_per_unit']=str(cell_value(json_response, row_num, col_dict[ref_dict['price_per_unit']]))
                post_dict['price_per_unit'] = post_dict['price_per_unit'].replace(",", "")
                post_dict['price_per_unit'] = int(post_dict['price_per_unit'])
            except:
                post_dict['price_per_unit']=0
            try:
                post_dict['inclusions']=str(cell_value(json_response, row_num, col_dict[ref_dict['inclusions']]))
            except:
                post_dict['inclusions']=''
            try:
                post_dict['exclusions']=str(cell_value(json_response, row_num, col_dict[ref_dict['exclusions']]))
            except:
                post_dict['exclusions']=''
            try:
                post_dict['remarks']=str(cell_value(json_response, row_num, col_dict[ref_dict['remarks']]))
            except:
                post_dict['remarks']=''
            try:
                 post_dict['los']=str(cell_value(json_response, row_num, col_dict[ref_dict['los']]))
            except:
                 post_dict['los']=''
            post_dict['service_description']=str(service_desc)
            post_dict['bounding_box_sd']=''
            post_dict['start_date']=get_date_and_tariff('start_date', json_file['pageViewURL'])
            post_dict['end_date']='2091/12/31'
            post_dict['tariff_type']=get_date_and_tariff('tariff_type', json_file['pageViewURL'])
            post_dict['room_mapping']=''            
            post_dict['confidence_score_sd']=''
            post_dict['standard_term']=''
            data= data + [post_dict]
    df = pd.DataFrame(data)
    return df

def cell_value(json_response, row_number, column_number):
    field_type = 'C'+column_number+'R'+row_number
    for i in json_response:
        if i['fieldType'] == field_type:
            # print(i['value'])
            return i['value']

def get_date_and_tariff(pattern_type, pageViewURL):
    if pattern_type=='start_date':
        date_pattern = r'(?<!\d)(?:\s*)(\d{4}[-/]\d{2}[-/]\d{2}|\d{2}[-/]\d{2}[-/]\d{4}|\d{4}[/]\d{2}[/]\d{2}|\d{2}[/]\d{2}[/]\d{4})(?:\s*|\b)'
        date_matches = re.findall(date_pattern, pageViewURL)
        start_date=''
        if date_matches:
            start_date = date_matches[0]
            return start_date
    
    if pattern_type=='tariff_type':
        url_lower = pageViewURL.lower()
        if 'soc' in url_lower or 'open' in url_lower:
            return 'OPEN'
        elif 'agreed' in url_lower:
            return 'AGREED'
        else:
            return 'OPEN'

        
def move_columns(df, columns_to_move, target_column):
    columns_not_to_move = [col for col in df.columns if col not in columns_to_move]
    target_index = columns_not_to_move.index(target_column)
    new_order = columns_not_to_move[:target_index + 1] + columns_to_move + columns_not_to_move[target_index + 1:]
    df = df.reindex(columns=new_order)
    return df


def wide_format(df_long):
    print(df_long)
    df2 = df_long.pivot_table(index = ['hospital_id','page','table','s3url', 'service_code','category','subcategory','service_description', 'bounding_box_sd','remarks','los','inclusions','exclusions','start_date','end_date','tariff_type','room_mapping','confidence_score_sd','standard_term'],columns='type_of_room', values='price_per_unit').reset_index()
    columns_to_drop = ['bounding_box_sd', 'standard_term']
    df2.drop(columns_to_drop, axis=1, inplace=True)
    column_index_last = df2.columns.get_loc('confidence_score_sd')
    selected_columns = df2.iloc[:, column_index_last+1:]
    col_names = list(selected_columns)
    target_column = 'service_description'
    df2 = move_columns(df2, col_names, target_column)
    df_wide = df2.dropna(axis=1, how="all")
    # df_wide = df_wide.drop(['confidence_score_sd'], axis=1)
    return df_wide

    
def upload_to_S3(df2, hospital_id):
    s3_client = boto3.client("s3", aws_access_key_id=ACCESS_KEY_ID, aws_secret_access_key=AWS_SECRET_ACCESS_KEY)
    name = hospital_id+'.xlsx'
    with io.BytesIO() as excel_buffer:
        df2.to_excel(excel_buffer, index=False)
        response = s3_client.put_object(
        Bucket='drishti-soc-data', Key=FOLDER+'/'+name, Body=excel_buffer.getvalue()
    )
        
    status = response.get("ResponseMetadata", {}).get("HTTPStatusCode")
    
    if status == 200:
        print(f"Successful S3 put_object response. Status - {status}")
    else:
        print(f"Unsuccessful S3 put_object response. Status - {status}")


def upload_to_postgres(df_long):
    conn_string = 'postgresql://' +USER+':'+PASSWORD+'@'+HOST+'/'+DATABASE
    db = create_engine(conn_string)
    conn = db.connect()
    df_long.to_sql(HOSPITAL_ID, con=conn, if_exists='replace', index=False)
    print('Pushed to postgres')


if __name__ == '__main__':

    hospital_id = HOSPITAL_ID
    records = HOSPITAL_STRUCTURE_DETAILS.find({'hospitalId':hospital_id},{'_id':0})
    records_list = list(records)
    
    column_headers = list(ref_dict.keys())
    df_final = pd.DataFrame(columns=column_headers)
    list_df = []

    for i in range(0, len(records_list)):
        a = json.dumps(records_list[i])
        json_file = json.loads(a)
        df = create_postgres_data(json_file)
        # print('yes')
        print(df.head())
        df_final = pd.concat([df_final, df], ignore_index=True)
    
    df_long = df_final[df_final['price_per_unit'] != '0']
    df_long = df_long[df_long['service_description'] != '']
    df_long.reset_index(drop=True, inplace=True)
    upload_to_postgres(df_long)
    
    # df_long.to_excel('/home/rishabh/Desktop/SOC_UI/downloadHospital/data/data6/final_long.xlsx')
    
    df_wide = wide_format(df_long)
    # df_wide.to_excel('/home/rishabh/Desktop/SOC_UI/downloadHospital/data/data6/final_wide.xlsx')
    upload_to_S3(df_wide, hospital_id)