def df_heartrate(query):
    """
    # Input: querystring to retrieve the full signal
    # Output: PD dataframe with 5 minute discretised intervals with all the features 
    """
    time1 = time.time()
    HRrsh = pd.DataFrame()
    HRframe = pd.read_gbq(query, globe.LRSid,private_key=globe.LRSkey) # Populating the dataframe
    if len(HRframe)>0:
        HRdf = HRframe[['timestamp','resultResponse','actorId']]
        HRrsh = core.emailToId(HRdf,'actorId')
        HRrsh.set_index(['timestamp','actorId'], inplace=True)
        HRrsh.resultResponse = HRrsh.resultResponse.astype(int)  
        HRrsh = HRrsh.groupby([pd.TimeGrouper('5Min',level=0), 
                           HRrsh.index.get_level_values('actorId')]).agg({'resultResponse': {
        'hr_mean':  np.mean,        # Mean of the signal
        'hr_max':   np.max,         # Maximum
        'hr_min':   np.min,         # Minimum
        'hr_std':   np.std,
        'hr_avc':   avg_change }})['resultResponse']
    
        time2 = time.time()
        print '3 ----- Heartrate feature generation took %0.1f s' % ((time2-time1))
    else:
        print '3 ----- No Heartrate values found in this time-window'
    
    return HRrsh
def df_heartrate(query):
    """
    # Input: querystring to retrieve the full signal
    # Output: PD dataframe with 5 minute discretised intervals with all the features 
    """
    time1 = time.time()
    HRrsh = pd.DataFrame()
    HRframe = pd.read_gbq(query, globe.LRSid,
                          private_key=globe.LRSkey)  # Populating the dataframe
    if len(HRframe) > 0:
        HRdf = HRframe[['timestamp', 'resultResponse', 'actorId']]
        HRrsh = core.emailToId(HRdf, 'actorId')
        HRrsh.set_index(['timestamp', 'actorId'], inplace=True)
        HRrsh.resultResponse = HRrsh.resultResponse.astype(int)
        HRrsh = HRrsh.groupby([
            pd.TimeGrouper('5Min', level=0),
            HRrsh.index.get_level_values('actorId')
        ]).agg({
            'resultResponse': {
                'hr_mean': np.mean,  # Mean of the signal
                'hr_max': np.max,  # Maximum
                'hr_min': np.min,  # Minimum
                'hr_std': np.std,
                'hr_avc': avg_change
            }
        })['resultResponse']

        time2 = time.time()
        print '3 ----- Heartrate feature generation took %0.1f s' % (
            (time2 - time1))
    else:
        print '3 ----- No Heartrate values found in this time-window'

    return HRrsh
def df_ratings(query): 
    time1 = time.time()
    # Populating the dataframe 
    RTframe = pd.read_gbq(query, globe.LRSid,private_key=globe.LRSkey)
    RTrsh = pd.DataFrame()
    if len(RTframe)>0:
        # Filtering the results 
        RTdf = RTframe[RTframe['objectId']!='dashboard']
        RTdf = RTdf[['timestamp','objectId','resultResponse','actorId']]
        
        #Rename the columns
        RTdf.rename(columns={'objectId':'Indicators'}, inplace=True)
        RTdf.rename(columns={'resultResponse':'value'}, inplace=True)
        
        #Drop the entries which are exactely identical
        RTdf = RTdf.drop_duplicates()
        
        #Drop the duplicate ratings (not indentical), take the last
        RTdf = RTdf.set_index(['timestamp','actorId','Indicators']).sort_index()
        RTdf = RTdf.groupby(level=RTdf.index.names).last().reset_index()
        
        RTrsh = RTdf.pivot_table(values='value',
                              index=['timestamp','actorId'],
                               columns=['Indicators'],aggfunc=lambda x: x.iloc[0])  

        RTrsh.reset_index(inplace=True)
        # Fix: the index will shift -1 hr. E.g. 9:00 -> 8:00 
        # Indicating the rating done at 9:00 for the 8:xx activities
        RTrsh.timestamp = RTrsh.timestamp - pd.offsets.Hour(1)
        # Retstrict to the arlearn value
        RTrsh = core.emailToId(RTrsh,'actorId')
        RTrsh['timeframe'] = RTrsh.timestamp.map(lambda x: x.strftime('%H')).astype(int)       
        RTrsh.set_index(['timestamp','actorId'], inplace=True)
        RTrsh = RTrsh.dropna()                
        
        RTrsh['MainActivity'] = activityToId(RTrsh['MainActivity'])
        
        # 1. First check for missing values and fill them backward
        # 2. Then check again and fill them forward (workaround for latest missing)
        # 3. Then cast to int
        RTrsh = RTrsh.fillna(method='bfill').fillna(method='pad').astype(int) 
            
        # Calculate the Flow-Score - see function flowPoints for explaination   
        RTrsh['Flow'] = RTrsh.apply(flowPoints, axis=1)
        
        #Create 5 minutes intervals
        RTrsh = RTrsh.unstack().fillna(-1).resample('5Min').fillna(method='pad').stack().replace(-1,np.NaN)
        
        # The correlation between Flow and Productivity 
        #flowProdCorr = RTrsh[['Productivity','Flow']].corr().iloc[0]['Flow']
        time2 = time.time()  
        print '1 ----- Ratings values read from BigQuery in %0.3f s' % ((time2-time1))
    else:
        print '1 ----- No ratings found in this time-window'
        
    return RTrsh
def df_weather(query,start_date,end_date):        
    # Populating the dataframe
    time1 = time.time()
    
    # Check the CSV file
    WTcsv = pd.DataFrame()
    if os.path.exists(globe.weatherFile):
        dateparse = lambda x: pd.datetime.strptime(x, '%Y-%m-%d %H:%M:%S.%f')
        WTdf = pd.read_csv(globe.weatherFile, parse_dates=['timestamp'], date_parser=dateparse)
        mask = (WTdf['timestamp'] > start_date) & (WTdf['timestamp'] <= end_date)
        WTdf = WTdf.loc[mask]
        if len(WTdf)>0:
            WTdf['weather'] = WTdf['weather'].replace({"u'":"'","'":'"'}, regex=True)
            WTdf['lat'], WTdf['lng'], WTdf['weatherId'], WTdf['pressure'],\
            WTdf['temp'], WTdf['humidity'] = zip(*WTdf['weather'].map(jsonToDF))
            WTcsv = WTdf.drop(['weather'], axis=1)
            WTcsv.set_index(['timestamp','actorId'], inplace=True)
            WTcsv = WTcsv.unstack().resample('5min').fillna(method='bfill').fillna(method='pad').stack()
            time2 = time.time()
            print '5.1 ----- Weather generation (from CSV) took %0.1f s' % ((time2-time1))
    
    # Check the Bigquery   
    WTframe = pd.read_gbq(query, globe.PRSid,private_key=globe.PRSkey) 
    WTgbq = pd.DataFrame()
    if len(WTframe)>0:
        WTdf = WTframe[['date','status','user']] 
        WTgbq = core.emailToId(WTdf,'user')
        WTgbq['status'] = WTgbq['status'].replace({"u'":"'","'":'"'}, regex=True)
        WTgbq['lat'], WTgbq['lng'], WTgbq['weatherId'], WTgbq['pressure'],\
        WTgbq['temp'],WTgbq['humidity'] = zip(*WTgbq['status'].map(jsonToDF))
        WTgbq.rename(columns={'date':'timestamp'}, inplace=True)
        WTgbq.rename(columns={'user':'******'}, inplace=True)  
        WTgbq = WTgbq.drop(['status'], axis=1)
        WTgbq.set_index(['timestamp','actorId'], inplace=True)
        WTgbq = WTgbq.unstack().resample('5min').stack()
        time2 = time.time()
        print '5.2 ----- Weather generation (from BigQuery) took %0.1f s' % ((time2-time1))
    
    WTrsh = pd.DataFrame()
    if len(WTcsv)>0 and len(WTgbq)>0:
        WTrsh = pd.concat([WTcsv,WTgbq])
        WTrsh = WTrsh[~WTrsh.index.duplicated(keep='last')]
    elif len(WTcsv)>0:
        WTrsh = WTcsv
    else:
        WTrsh = WTgbq
    return WTrsh
def df_steps(query,allUsers=False):
    # Populating the dataframe
    time1 = time.time()
    SCframe = pd.read_gbq(query, globe.LRSid,private_key=globe.LRSkey) 
    SCrsh = pd.DataFrame()
    if len(SCframe)>0:
         # Filtering the results 
        SCdf = SCframe[['timestamp','resultResponse','actorId']] 
        SCrsh = core.emailToId(SCdf,'actorId')
        #Rename columns
        SCrsh.rename(columns={'resultResponse':'Steps'}, inplace=True)
        SCrsh.Steps = SCrsh.Steps.astype(int)        
        SCrsh.set_index(['timestamp','actorId'], inplace=True)
        SCrsh = SCrsh.groupby([pd.TimeGrouper('5Min',level=0), SCrsh.index.get_level_values('actorId')])['Steps'].sum()
        time2 = time.time()  
        print '2 ----- Steps values read from BigQuery in %0.3f s' % ((time2-time1))
    return SCrsh
def df_activities(query):
    
    # Populating the dataframe
    time1 = time.time()
    ACframe = pd.read_gbq(query, globe.LRSid, private_key=globe.LRSkey)
    ACrsh = pd.DataFrame()
    CArsh = pd.DataFrame()
    if len(ACframe)>0:
        gdoc = requests.get(globe.googleDocCategories)
        data = gdoc.content
        GDdf = pd.read_csv(StringIO(data), error_bad_lines=False)
        n_app = len(GDdf)
        df = GDdf.stack()
        dict_apps = dict()
        for i in range(0,n_app):
            if len(df[i][df[i]=='x'])>0:
                dict_apps[df[i][0]] = df[i][df[i]=='x'].index.get_values()[0]
        ACframe['origin'] = ACframe['origin'].astype(str)
        ACdf = ACframe[['timestamp','objectId','resultDuration','actorId']]
        ACdf = core.emailToId(ACdf,'actorId')        
        #Rename columns
        ACdf.rename(columns={'objectId':'App'}, inplace=True)  
        ACdf['Cat'] = ACdf['App'].map(dict_apps)
    
        ACrsh = ACdf.groupby(['timestamp', 'actorId', 'App'])['resultDuration'].sum().unstack()
        CArsh = ACdf.groupby(['timestamp', 'actorId', 'Cat'])['resultDuration'].sum().unstack()
        ACrsh = ACrsh.fillna(0)
        CArsh = CArsh.fillna(0)
        #check distributon
        #df[df['objectId'].str.contains("MS Word")].ix[:,2:].notnull().stack().idxmax()[1]
        #CArsh.sum().plot(kind='bar')
    
        #dfAC,dfCA = activities.df_activities("SELECT *  FROM [xAPIStatements.xapiTableNew] WHERE origin = 'rescuetime' AND timestamp > PARSE_UTC_USEC('2015-11-23 07:00:00')  AND timestamp <  PARSE_UTC_USEC('2015-12-09 20:00:00') ORDER by timestamp")
        time2 = time.time()  
        print '4 ----- Activities processed in %0.1f s' % ((time2-time1))
    else:
        time2 = time.time()  
        print '4 ----- No activities found in this time window'
        
    return ACrsh,CArsh
def df_ratings(query):
    time1 = time.time()
    # Populating the dataframe
    RTframe = pd.read_gbq(query, globe.LRSid, private_key=globe.LRSkey)
    RTrsh = pd.DataFrame()
    if len(RTframe) > 0:
        # Filtering the results
        RTdf = RTframe[RTframe['objectId'] != 'dashboard']
        RTdf = RTdf[['timestamp', 'objectId', 'resultResponse', 'actorId']]

        #Rename the columns
        RTdf.rename(columns={'objectId': 'Indicators'}, inplace=True)
        RTdf.rename(columns={'resultResponse': 'value'}, inplace=True)

        #Drop the entries which are exactely identical
        RTdf = RTdf.drop_duplicates()

        #Drop the duplicate ratings (not indentical), take the last
        RTdf = RTdf.set_index(['timestamp', 'actorId',
                               'Indicators']).sort_index()
        RTdf = RTdf.groupby(level=RTdf.index.names).last().reset_index()

        RTrsh = RTdf.pivot_table(values='value',
                                 index=['timestamp', 'actorId'],
                                 columns=['Indicators'],
                                 aggfunc=lambda x: x.iloc[0])

        RTrsh.reset_index(inplace=True)
        # Fix: the index will shift -1 hr. E.g. 9:00 -> 8:00
        # Indicating the rating done at 9:00 for the 8:xx activities
        RTrsh.timestamp = RTrsh.timestamp - pd.offsets.Hour(1)
        # Retstrict to the arlearn value
        RTrsh = core.emailToId(RTrsh, 'actorId')
        RTrsh['timeframe'] = RTrsh.timestamp.map(
            lambda x: x.strftime('%H')).astype(int)
        RTrsh.set_index(['timestamp', 'actorId'], inplace=True)
        RTrsh = RTrsh.dropna()

        RTrsh['MainActivity'] = activityToId(RTrsh['MainActivity'])

        # 1. First check for missing values and fill them backward
        # 2. Then check again and fill them forward (workaround for latest missing)
        # 3. Then cast to int
        RTrsh = RTrsh.fillna(method='bfill').fillna(method='pad').astype(int)

        # Calculate the Flow-Score - see function flowPoints for explaination
        RTrsh['Flow'] = RTrsh.apply(flowPoints, axis=1)

        #Create 5 minutes intervals
        RTrsh = RTrsh.unstack().fillna(-1).resample('5Min').fillna(
            method='pad').stack().replace(-1, np.NaN)

        # The correlation between Flow and Productivity
        #flowProdCorr = RTrsh[['Productivity','Flow']].corr().iloc[0]['Flow']
        time2 = time.time()
        print '1 ----- Ratings values read from BigQuery in %0.3f s' % (
            (time2 - time1))
    else:
        print '1 ----- No ratings found in this time-window'

    return RTrsh