import pandas as pd
import numpy as np

wrangling_df = pd.read_csv("patients.csv")
wrangling_df.shape

wrangling_df.head()

(1*(wrangling_df['given_name'] == "Jake")).sum()

wrangling_df.keys()[-3:].tolist()

[x for x,y in list(wrangling_df['height'].value_counts().iteritems()) if y > 30]
# the result will express the heights, that occured more than thirty times in the dataframe

[row for row in wrangling_df['given_name'] if row.startswith('David')]

[row[1][2] for row in wrangling_df.iterrows() if row[1][2].startswith('David')]

wrangling_df[((wrangling_df.address.duplicated()) & wrangling_df.address.notnull())].head(1)

clean_df = wrangling_df[~((wrangling_df.address.duplicated()) & wrangling_df.address.notnull())].head(1)

wrangling_df.dropna().shape

wrangling_df.dropna(subset = ['surname']).shape

wrangling_df.drop(columns=['contact','birthdate']).shape

wrangling_df['illness'].index

wrangling_df['illness'] = pd.Series(pd.Categorical(values=["none"]*len(wrangling_df),categories=["none","a bit","intermediate","very ill"]))

wrangling_df['illness'][0] = "bad"

wrangling_df['illness'][0] = "intermediate"

wrangling_df_2 = wrangling_df.copy()

wrangling_df_2 = wrangling_df_2.add_suffix('X')
wrangling_df_2 = wrangling_df_2.add_prefix('X')
wrangling_df_2.head(1)

wrangling_df_2.rename(columns='{}Y'.format, inplace=True)
wrangling_df_2.rename(columns = lambda x: x+'Z', inplace=True)
wrangling_df_2.head(1)

wrangling_df_2 = wrangling_df.copy()

wrangling_df_2.columns = [column + 'X' for column in wrangling_df_2.columns]
wrangling_df_2.columns = list(map(lambda s: s+'Y', wrangling_df_2.columns))
wrangling_df_2.head(1)

wrangling_df['height_result'] = wrangling_df['height'].apply(lambda x: "extrem height" if (x > 67) or (x < 60) else "normal height")
wrangling_df.head(5)

# Mapping from full state name to abbreviation
state_abbrev = {'California': 'CA',
                'New York': 'NY',
                'Illinois': 'IL',
                'Florida': 'FL',
                'Nebraska': 'NE'}

# Function to apply
def abbreviate_state(patient):
    if patient['state'] in state_abbrev.keys():
        abbrev = state_abbrev[patient['state']]
        return abbrev
    else:
        return patient['state']
    
wrangling_df['state'] = wrangling_df.apply(abbreviate_state, axis=1)
wrangling_df.head(5)

df = pd.DataFrame([[1,2,3],[3,4,5]])
df.div(df.sum(axis=1), axis=0)

from scipy.interpolate import interp1d

m = interp1d([1,10],[1,100])
int(m(10))

ser = pd.Series([1,2,3,4])
100*(ser - ser.min()) / (ser.max() - ser.min())

m = interp1d([1,100],[1,10],fill_value="extrapolate")
int(m(95))

app_dict = {'patient_id':wrangling_df['patient_id'].max()+1,
            'given_name':'testuser',
            'surname':'blahblah'}

new_row = pd.DataFrame([app_dict],columns=app_dict.keys())

pd.concat([wrangling_df,new_row], sort=True).reset_index().tail(3)

height_mean = wrangling_df.height.mean()
mask = wrangling_df.patient_id == 4509
column_name = 'height'
wrangling_df.loc[mask, column_name] = height_mean * 1.5

df_cut = wrangling_df.iloc[np.r_[:5,10:15], np.r_[1, 3]]
df_cut.shape

categories = ["C006_01","C006_02","C006_04","C006_06","C006_07","C006_10"]
all_muts = list(itertools.combinations(categories, 4))

f_s = 'is correct'
result_df = pd.DataFrame()

for m in all_muts:
    temp_df = umfrage_df[(umfrage_df[m[0]] == f_s) & (umfrage_df[m[1]] == f_s) & (umfrage_df[m[2]] == f_s) & (umfrage_df[m[3]] == f_s)]
    result_df = result_df.append(temp_df)

def merge_categorical(df,col_str,rating_dict):
    
    filter_col = [col for col in df if col.startswith(col_str)]
    subset_df = df[filter_col].fillna(0)
    norm_value = len(filter_col)
    subset_df['sum'] = subset_df.apply(lambda x: (sum([rating_dict[y] for y in x[filter_col]]))/norm_value , axis=1)
    
    return subset_df

rating_dict_freq_lesson = {"Every Lesson":4,"Most Lessons":3,"Some Lessons":2,"Never or Hardly Ever":1,0:0}

teacher_practice = merge_categorical(pisa_df,"ST79Q",rating_dict_freq_lesson)

wrangling_df.shape

treatment_df = pd.read_csv("treatments.csv")
wrangling_df.merge(treatment_df,how='outer',left_on=['given_name','surname'],right_on=['given_name','surname']).shape

melt_df = wrangling_df.melt(id_vars = ['patient_id','surname'], 
                  value_vars = ['height', 'weight'], 
                  var_name = 'body_indicator', 
                  value_name = 'body')

melt_df[melt_df.patient_id == 1]

patients_country = wrangling_df.groupby(by=['country'])['state'].value_counts().sort_index()
patients_country.unstack(1)

wrangling_df['phone_number'] = wrangling_df.contact.str.extract('((?:\+\d{1,2}\s)?\(?\d{3}\)?[\s.-]?\d{3}[\s.-]?\d{4})', expand=True)
wrangling_df['email'] = wrangling_df.contact.str.extract('([a-zA-Z][a-zA-Z0-9_.+-]+@[a-zA-Z0-9-]+\.[a-zA-Z0-9-.][a-zA-Z]+)', expand=True)

repl = lambda m: m.group(0)[::-1]
pd.Series(['foo 123', 'bar baz', np.nan]).str.replace(r'[a-z]+', repl)

pat = r"(?P<one>\w+) (?P<two>\w+) (?P<three>\w+)"
repl = lambda m: m.group('one').upper()
pd.Series(['one two three', 'foo bar baz']).str.replace(pat, repl)

import re
reg_df = pd.DataFrame(["blah","blubb","blach","blhab","asdblaheh"], columns=['test'])

def regex_filter(val):
    if val:
        mo = re.search("blah",val)
        if mo:
            return True
        else:
            return False
    else:
        return False

reg_df[reg_df['test'].apply(regex_filter)]

wrangling_df.phone_number[:5]

wrangling_df.phone_number.str.replace(r'\D+', '').str.pad(11, fillchar='1')[:5]

wrangling_df.zip_code[:5]

wrangling_df.zip_code.astype(str).\
str[:-2].\
str.pad(5, fillchar='0').\
replace('0000n', np.nan)[:5]