<jupyter_start><jupyter_text>Data ProcessingIn this part, we processed several datasets and calculated the maximum, minimum and mode of Workload<jupyter_code>episodes = pd.read_excel('data/Episodes.xlsx')
episodes['AdmitDate'] = pd.to_datetime(episodes['AdmitDate'], format='%m/%d/%Y')
episodes['DischargeDate'] = pd.to_datetime(episodes['DischargeDate'], format='%Y-%m-%d')

episodes
longdf = pd.DataFrame(columns=['Date', 'PID', 'EpisodeID', 'ContractType', 'WorkLoadMode','WorkLoadMin','WorkLoadMax'])

for index, row in episodes.iterrows():
    PID = row['PID']
    EpisodeID = row['EpisodeID']
    ContractType = row['ContractType']
    AdmitDate = row['AdmitDate']
    DischargeDate = row['DischargeDate']
    
    if pd.isnull(DischargeDate):
        today = datetime.date.today()
        DischargeDate = today.strftime('%Y-%m-%d')
        
    date_list = [datetime.datetime.strftime(x,'%Y-%m-%d') for x in list(pd.date_range(start=AdmitDate, end=DischargeDate))]
            
    if ContractType == 'CT1':
        workloadmin =  [6.33 for i in range(min(3,len(date_list)))] + [2.86 for i in range(len(date_list)-3)]
        workloadmode = [10.33 for i in range(min(3,len(date_list)))] + [6.57 for i in range(len(date_list)-3)]
        workloadmax =  [22 for i in range(min(3,len(date_list)))] + [18.35 for i in range(len(date_list)-3)]
    elif ContractType == 'CT2':
        workloadmin =  [10.33 for i in range(min(3,len(date_list)))] + [4.71 for i in range(len(date_list)-3)]
        workloadmode = [27.67 for i in range(min(3,len(date_list)))] + [14.29 for i in range(len(date_list)-3)]
        workloadmax =  [50.33 for i in range(min(3,len(date_list)))] + [33.93 for i in range(len(date_list)-3)]
    
    
    episode = pd.DataFrame(columns=['Date', 'PID', 'EpisodeID', 'ContractType', 'WorkLoadMode','WorkLoadMin','WorkLoadMax'])
    episode['Date'] = date_list
    episode['PID'] = PID
    episode['EpisodeID'] = EpisodeID
    episode['ContractType'] = ContractType
    episode['WorkLoadMode'] = workloadmode
    episode['WorkLoadMin'] = workloadmin
    episode['WorkLoadMax'] = workloadmax
    longdf = longdf.append(episode,ignore_index=True)
    
    print("progress: {0}%".format(round((index + 1) * 100 / len(episodes), 2)), end="\r")
longdf
longdf.to_csv('longdf.csv')
date_pid = pd.DataFrame()
date_pid['WorkLoadMode'] = longdf.groupby(['Date', 'PID'])['WorkLoadMode'].sum()
date_pid['WorkLoadMin'] = longdf.groupby(['Date', 'PID'])['WorkLoadMin'].sum()
date_pid['WorkLoadMax'] = longdf.groupby(['Date', 'PID'])['WorkLoadMax'].sum()
date_pid.reset_index(inplace=True)
date_pid
date_pid.to_csv('date_pid.csv')
SNFs = pd.read_excel('data/SNFs.xlsx')
lat_l, lon_l = [], []
for index, row in SNFs.iterrows():
    ProviderPracticeAddress1 = row['ProviderPracticeAddress1']
    ProviderPracticeCity = row['ProviderPracticeCity']
    ProviderPracticeStateCode = row['ProviderPracticeStateCode']
    ProviderPracticeZipCode = row['ProviderPracticeZipCode']
    
    address = ProviderPracticeAddress1+', ' +ProviderPracticeCity+', '+ProviderPracticeStateCode+' '+str(ProviderPracticeZipCode)
    try:
        locator = Nominatim(user_agent='myGeocoder')
        location = locator.geocode(address)
        lat = location.latitude
        lon = location.longitude
    except:
        lat = np.nan
        lon = np.nan
    lat_l.append(lat)
    lon_l.append(lon)

    print("progress: {0}%".format(round((index + 1) * 100 / len(SNFs), 2)), end="\r")
    
SNFs['latitude'] = lat_l
SNFs['longitude'] = lon_l
PID_latlon = SNFs[['PID','latitude','longitude']]
PID_latlon
PID_latlon[PID_latlon.isnull().T.any()]
result = date_pid.merge(PID_latlon, on='PID', how='left')
result
result[result.isnull().T.any()]
result.to_csv('case1.csv')
CCs = pd.read_excel('data/CCs.xlsx')
CCs
distance = np.array([(x,y) for x in SNFs['PID'] for y in CCs['CCName']])
distance = pd.DataFrame(distance, columns=['PID','CCName'])
distance[['PID']] = distance[['PID']].astype(int)
distance = distance.merge(CCs, on='CCName', how='left')
distance = distance.merge(PID_latlon, on='PID', how='left')
distance = distance.dropna(axis=0,how='any')
distance
from geopy.distance import great_circle

distance_list = []
for index, row in distance.iterrows():
    CC_location = (row['Lat'], row['Lon'])
    SNF_location = (row['latitude'], row['longitude'])
    distance_list.append(great_circle(CC_location, SNF_location).miles)
    
    print("progress: {0}%".format(round((index + 1) * 100 / len(distance), 2)), end="\r")
distance['distance'] = np.array(distance_list)
distance
distance.to_csv('distance.csv')
distance = pd.read_csv("distance.csv")
distance.describe()
distance.Lat.unique()<jupyter_output><empty_output><jupyter_text>Historical Data Estimation and Modeling<jupyter_code>main_df = pd.read_csv("case1.csv")
main_df
main_df.describe()
columns = []
count = 0
for i in ['mode','min','max']:
    main_df['cc_num_'+i] = np.ceil(main_df.iloc[:,count+3]/480)
    count += 1
    columns.append('cc_num_'+i)
main_df
main_df.drop(columns = 'Unnamed: 0', inplace=True)
main_df.to_csv('new_case.csv',index=False)
columns.append('PID')
df1 = main_df[columns].groupby('PID').max()
df1.to_csv('history_cc_num.csv',index=False)
demo_snf = main_df.copy()
demo_snf['Date'] = pd.to_datetime(demo_snf['Date'])
demo_snf['Year'] = demo_snf['Date'].dt.year
demo_snf['Month'] = demo_snf['Date'].dt.month
demo_snf['Day'] = demo_snf['Date'].dt.day
# demo_snf.reset_index(drop=True, inplace=True)
demo_snf = demo_snf[demo_snf['Date'] < '2020-01-01']
predictors = ['PID','latitude','longitude','Year','Month','Day']
data_X = demo_snf.loc[:,predictors]
data_y = demo_snf.loc[:,'WorkLoadMode']
import xgboost as xgb
from sklearn.model_selection import train_test_split
from sklearn.ensemble import RandomForestRegressor
model = xgb.XGBRegressor()
model.fit(data_X,data_y)
df1=demo_snf[['PID','latitude','longitude']].drop_duplicates().reset_index(drop=True)
rownum = len(df1)
date = input('Enter date like 2020-10-01: ')
y = int(date[:4])
m = int(date[5:7])
d = int(date[8:10])
Year = [y]*rownum
Month = [m]*rownum
Day = [d]*rownum
df1['Year'] = Year
df1['Month'] = Month
df1['Day'] = Day
test_x = df1.copy()
preds = model.predict(test_x)
preds = preds.tolist()
for i in range(len(preds)):
    if preds[i] <= 0:
        preds[i] = 0
number = [np.ceil(i/480) for i in preds]
output = pd.DataFrame({'PID':test_x['PID'].tolist(),'CC_preds':number,'WorkLoad':np.round(preds,2),'Date':date})
pd.DataFrame({'Date':date,'Lower Bound':np.ceil(sum(preds)/480)}, index=[0])
sum(preds)/480
sum(output['CC_preds'])<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.18199727231398696, 'fraction_numerical': 0.01969995453856645, 'mean_word_length': 5.464250734573947, 'pattern_counts': {'":': 0, '<': 11, '<?xml version=': 0, '>': 9, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 7, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '2', 'dir': 'jupyter-structured-clean-dedup', 'id': '199977', 'license': 'MIT', 'n_tokens_mistral': 2559, 'n_tokens_neox': 2432, 'n_words': 417, 'path': 'data-process-modeling.ipynb', 'repo_name': 'waittim/SNF-workload-tracker', 'size': '6599'}