Convert Numbers to Binary

This time this problem will appear on both Sat and Sun with variations. Today’s is easier of the 2. Calculate the total fly time and rest time for a pilot in hours. Fly Time = Flight End – Flight Start Rest Time = Flight Start – Flight End of Previous record

📌 Challenge Details and Links
ExcelBI Power Query Challenge Number: 153
Challenge Difficulty: ⭐️⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn

Solving the challenge of Pilot Flight Rest Hours with Power Query

Power Query solution 1 for Pilot Flight Rest Hours, proposed by Zoran Milokanović:

let
  Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content], 
  C = Table.ToColumns(Source), 
  T = Duration.TotalHours, 
  P = Table.FromColumns(
    C
      & List.Zip(
        List.Transform(
          List.Positions(C{0}), 
          each {
            T(C{2}{_} - C{1}{_}), 
            T(C{1}{_} - (if _ <> 0 and C{0}{_ - 1} = C{0}{_} then ({null} & C{2}){_} else C{1}{_}))
          }
        )
      ), 
    Table.ColumnNames(Source) & {"A", "B"}
  ), 
  S = Table.Group(
    P, 
    {"Pilot"}, 
    {
      {"Fly Time", each List.Sum([A])}, 
      {
        "Rest Time", 
        each 
          let
            s = List.Sum([B])
          in
            if s = 0 then null else s
      }
    }
  )
in
  S

Power Query solution 2 for Pilot Flight Rest Hours, proposed by Zoran Milokanović:

let
  Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content], 
  S = Table.FromRows(
    List.Transform(
      List.Accumulate(
        Table.ToRows(Source), 
        {}, 
        (s, c) =>
          let
            l = List.Last(s, c & {0, 0}), 
            T = Duration.TotalHours, 
            f = Number.From(l{0} = c{0} and l{1} <> c{1})
          in
            List.RemoveLastN(s, f) & {c & {f * l{3} + T(c{2} - c{1}), f * (l{4} + T(c{1} - l{2}))}}
      ), 
      each {_{0}, _{3}, if _{4} = 0 then null else _{4}}
    ), 
    {"Pilot", "Fly Time", "Rest Time"}
  )
in
  S

Power Query solution 3 for Pilot Flight Rest Hours, proposed by Kris Jaganah:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Index = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type), 
  Idx1 = Table.AddColumn(Index, "Custom", each [Index] + 1), 
  Merge = Table.NestedJoin(
    Idx1, 
    {"Pilot", "Custom"}, 
    Idx1, 
    {"Pilot", "Index"}, 
    "Added Custom", 
    JoinKind.LeftOuter
  ), 
  Xpand = Table.ExpandTableColumn(Merge, "Added Custom", {"Flight Start"}, {"Start"}), 
  FlyTime = Table.AddColumn(Xpand, "Fly", each Number.From([Flight End] - [Flight Start]) * 24), 
  Restime = Table.AddColumn(FlyTime, "Rest", each Number.From([Start] - [Flight End]) * 24), 
  Sort = Table.Sort(Restime, {{"Index", Order.Ascending}}), 
  Group = Table.Group(
    Sort, 
    {"Pilot"}, 
    {
      {"Fly Time", each Number.Round(List.Sum([Fly]), 2)}, 
      {"Rest Time", each Number.Round(List.Sum([Rest]), 2)}
    }
  )
in
  Group

Power Query solution 4 for Pilot Flight Rest Hours, proposed by Alejandro Simón 🇵🇦 🇪🇸:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  A = Table.AddColumn(
    Source, 
    "A", 
    each Number.Round(Duration.TotalHours([Flight End] - [Flight Start]), 2)
  ), 
  Group = Table.Group(
    A, 
    {"Pilot"}, 
    {
      {
        "All", 
        each 
          let
            a = List.RemoveLastN([Flight End]), 
            b = List.RemoveFirstN([Flight Start]), 
            c = List.Sum(
              List.Transform({0 .. List.Count(a) - 1}, each Duration.TotalHours(b{_} - a{_}))
            ), 
            d = List.Sum([A])
          in
            Table.FromColumns({{d}, {c}}, {"Fly Time", "Rest Time"})
      }
    }
  ), 
  Sol = Table.ExpandTableColumn(Group, "All", Table.ColumnNames(Group[All]{0}))
in
  Sol

Power Query solution 5 for Pilot Flight Rest Hours, proposed by Alejandro Simón 🇵🇦 🇪🇸:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  A = Table.AddColumn(
    Source, 
    "A", 
    each Number.Round(Duration.TotalHours([Flight End] - [Flight Start]), 2)
  ), 
  Group = Table.Group(
    A, 
    {"Pilot"}, 
    {
      {
        "All", 
        each 
          let
            a = {0} & List.RemoveLastN([Flight End]), 
            b = Table.AddIndexColumn(_, "Idx"), 
            c = Table.AddColumn(
              b, 
              "B", 
              each try
                Duration.TotalHours(b[Flight Start]{[Idx] + 1} - b[Flight End]{[Idx]})
              otherwise
                null
            ), 
            d = Table.FromColumns({{List.Sum(c[A])}, {List.Sum(c[B])}}, {"Fly Time", "Rest Time"})
          in
            d
      }
    }
  ), 
  Sol = Table.ExpandTableColumn(Group, "All", Table.ColumnNames(Group[All]{0}))
in
  Sol

Power Query solution 6 for Pilot Flight Rest Hours, proposed by Hussein SATOUR:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  AddIndex = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type), 
  ColFly = Table.AddColumn(
    AddIndex, 
    "Custom", 
    each Duration.TotalHours([Flight End] - [Flight Start])
  ), 
  ColRest = Table.AddColumn(
    ColFly, 
    "Custom.1", 
    each 
      if [Pilot] = ColFly[Pilot]{[Index] - 1} then
        Duration.TotalHours([Flight Start] - ColFly[Flight End]{[Index] - 1})
      else
        0
  ), 
  ReplaceErrors = Table.ReplaceErrorValues(ColRest, {{"Custom.1", 0}}), 
  Grouping = Table.Group(
    ReplaceErrors, 
    {"Pilot"}, 
    {{"Fly time", each List.Sum([Custom])}, {"Rest Time", each List.Sum([Custom.1])}}
  )
in
  Grouping

Power Query solution 7 for Pilot Flight Rest Hours, proposed by Bhavya Gupta:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  TH = Table.AddColumn(
    Source, 
    "TotalHours", 
    each Duration.TotalHours([Flight End] - [Flight Start])
  ), 
  Grouped = Table.Group(
    TH, 
    {"Pilot"}, 
    {
      {
        "Record", 
        each [
          Fly Time = List.Sum([TotalHours]), 
          Rest Time = Duration.TotalHours(List.Max([Flight End]) - List.Min([Flight Start]))
            - #"Fly Time"
        ]
      }
    }
  ), 
  Output = Table.ExpandRecordColumn(Grouped, "Record", {"Fly Time", "Rest Time"})
in
  Output

Power Query solution 8 for Pilot Flight Rest Hours, proposed by An Nguyen:

let
 Dataset = Excel.CurrentWorkbook () { [ Name = "MainData" ] } [Content] ,
 GroupbyPilot = Table.Group ( Dataset , "Pilot",{ "Zip" , (t) =>
 let
 FlyTime = List.Sum( Table.AddColumn ( t , "Fly Time" , each Duration.TotalHours( [Flight End] - [Flight Start ] ) ) [Fly Time] ) ,
 FlightStart = List.Buffer( t [Flight Start] ) ,
 FlightEnd = List.Buffer ( t [Flight End] ) ,
 Length = List.Count ( FlightStart ) ,
 RestTime = List.Sum( List.Generate ( 
 () => [ Result = 0 , Counter = 0 ] ,
 each [Counter] < Length , 
 each [Counter = [Counter] + 1 , Result = Duration.TotalHours( FlightStart { Counter } - FlightEnd { Counter - 1 } ) ] ,
 each [Result] 
 ) ) ,
 ResultTbl = hashtag#table({"Fly Time", "Rest Time"} , {{FlyTime , RestTime}}  )
 in
 ResultTbl } ) ,
 ExpandTable = Table.ExpandTableColumn ( GroupbyPilot ,"Zip", {"Fly Time","Rest Time"}) ,
 ColumnsName = Table.ColumnNames (ExpandTable) ,
 RoundNumbers = Table.TransformColumns ( ExpandTable , List.Transform ( {"Fly Time", "Rest Time"} , 
 each { _ , each Number.Round ( _ , 2 ) , type number } ) )
in
 RoundNumbers

Power Query solution 9 for Pilot Flight Rest Hours, proposed by Ramiro Ayala Chávez:

let
  Origen = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  a = Table.AddColumn(Origen, "A", each [Flight End] - [Flight Start]), 
  b = Table.TransformColumns(a, {{"A", Duration.TotalHours}}), 
  c = Table.Group(
    b, 
    {"Pilot"}, 
    {
      {"Fly Time", each Number.Round(List.Sum([A]), 2)}, 
      {"B", each List.Max([Flight End]) - List.Min([Flight Start])}
    }
  ), 
  d = Table.TransformColumns(c, {{"B", Duration.TotalHours}}), 
  e = Table.AddColumn(d, "Rest Time", each [B] - [Fly Time])[[Pilot], [Fly Time], [Rest Time]], 
  Sol = Table.TransformColumns(e, {{"Rest Time", each Number.Round(_, 2)}})
in
  Sol

Power Query solution 10 for Pilot Flight Rest Hours, proposed by Eric Laforce:

let
  Source = Table.TransformColumnTypes(
    Excel.CurrentWorkbook(){[Name = "tData153"]}[Content], 
    {{"Pilot", type text}, {"Flight Start", type datetime}, {"Flight End", type datetime}}
  ), 
  Group = Table.Group(
    Source, 
    "Pilot", 
    {
      "All", 
      each 
        let
          _FT = List.Accumulate(Table.ToRows(_), 0, (s, c) => s + Duration.TotalHours(c{2} - c{1})), 
          _RT = Duration.TotalHours(List.Max([Flight End]) - List.Min([Flight Start])) - _FT
        in
          [FT = _FT, RT = if _RT = 0 then null else _RT]
    }
  ), 
  Expand = Table.ExpandRecordColumn(Group, "All", {"FT", "RT"}, {"Fly Time", "Rest Time"})
in
  Expand

Power Query solution 11 for Pilot Flight Rest Hours, proposed by 🇮🇷 Navid Esmaeilzadeh اسماعیل زاده:

let
  S = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  C = Table.TransformColumnTypes(
    S, 
    {{"Pilot", type text}, {"Flight Start", type datetime}, {"Flight End", type datetime}}
  ), 
  A = Table.AddIndexColumn(C, "I", 1, 1, Int64.Type), 
  G = Table.Group(
    A, 
    {"Pilot"}, 
    {
      {
        "T", 
        each _, 
        type table [
          Pilot = nullable text, 
          Flight Start = nullable datetime, 
          Flight End = nullable datetime, 
          Index = number
        ]
      }
    }
  ), 
  B = Table.AddColumn(G, "T2", each Table.AddIndexColumn([T], "I.", 0, 1)), 
  R = Table.SelectColumns(B, {"T2"}), 
  M = (TBL) =>
    let
      A = Table.AddColumn(TBL, "F", each [Flight End] - [Flight Start]), 
      CA = Table.TransformColumns(A, {{"F", Duration.TotalHours, type number}}), 
      A2 = Table.AddColumn(
        CA, 
        "R", 
        each try CA[Flight Start]{[#"I."] + 1} - [Flight End] otherwise null
      ), 
      C = Table.TransformColumns(A2, {{"R", Duration.TotalHours, type number}}), 
      G = Table.Group(
        C, 
        {"Pilot"}, 
        {
          {"FlyTime", each List.Sum([F]), type number}, 
          {"RestTime", each List.Sum([R]), type number}
        }
      )
    in
      G, 
  I = Table.AddColumn(R, "MF", each M([T2])), 
  X = Table.SelectColumns(I, {"MF"}), 
  E = Table.ExpandTableColumn(
    X, 
    "MF", 
    {"Pilot", "FlyTime", "RestTime"}, 
    {"Pilot", "FlyTime", "RestTime"}
  )
in
  E

Power Query solution 12 for Pilot Flight Rest Hours, proposed by Sandeep Marwal:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  S1 = Table.TransformColumnTypes(
    Source, 
    {{"Pilot", type text}, {"Flight Start", type datetime}, {"Flight End", type datetime}}
  ), 
  S2 = Table.Group(
    S1, 
    {"Pilot"}, 
    {{"Count", each List.Combine(Table.ToRows(Table.RemoveColumns(_, {"Pilot"})))}}
  ), 
  S3 = Table.TransformColumns(
    S2, 
    {"Count", each List.Split(List.RemoveItems(_, {List.First(_), List.Last(_)}), 2)}
  ), 
  S4 = Table.ExpandListColumn(S3, "Count"), 
  S5 = Table.TransformColumns(
    S4, 
    {"Count", each Record.FromList(_ ?? {null, null}, {"first", "second"})}
  ), 
  S6 = Table.ExpandRecordColumn(S5, "Count", {"first", "second"}, {"Count.first", "Count.second"}), 
  S7 = Table.AddColumn(S6, "Subtraction", each [Count.second] - [Count.first], type duration), 
  S8 = Table.AddColumn(S7, "Total Hours", each Duration.TotalHours([Subtraction]), type number), 
  S9 = Table.Group(S8, {"Pilot"}, {{"resst", each List.Sum([Total Hours]), type number}}), 
  S10 = Table.TransformColumns(S9, {{"resst", each Number.Round(_, 2), type number}})
in
  S10

Power Query solution 13 for Pilot Flight Rest Hours, proposed by Sandeep Marwal:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  #"Changed Type" = Table.TransformColumnTypes(
    Source, 
    {{"Pilot", type text}, {"Flight Start", type datetime}, {"Flight End", type datetime}}
  ), 
  Subtraction = Table.AddColumn(
    #"Changed Type", 
    "Subtraction", 
    each [Flight End] - [Flight Start], 
    type duration
  ), 
  #"Total Hours" = Table.AddColumn(
    Subtraction, 
    "Total Hours", 
    each Duration.TotalHours([Subtraction]), 
    type number
  ), 
  Grouped = Table.Group(
    #"Total Hours", 
    {"Pilot"}, 
    {{"Fly time", each List.Sum([Total Hours]), type number}}
  ), 
  #"Rounded Off" = Table.TransformColumns(
    Grouped, 
    {{"Fly time", each Number.Round(_, 2), type number}}
  )
in
  #"Rounded Off"

Power Query solution 14 for Pilot Flight Rest Hours, proposed by Glyn Willis:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  #"Changed Type" = Table.TransformColumnTypes(
    Source, 
    {{"Pilot", type text}, {"Flight Start", type datetime}, {"Flight End", type datetime}}
  ), 
  #"Grouped Rows" = Table.Group(
    #"Changed Type", 
    {"Pilot"}, 
    {
      {
        "Count", 
        each [
          t = Table.FromColumns(
            Table.ToColumns(_) & {{null} & List.RemoveLastN([Flight End], 1)}, 
            Table.ColumnNames(_) & {"prvfe"}
          ), 
          ft = Table.AddColumn(
            t, 
            "ft", 
            (x) => Number.From(x[Flight End] - x[Flight Start]) / (1 / 24)
          ), 
          rt = Table.AddColumn(ft, "rt", (x) => Number.From(x[Flight Start] - x[prvfe]) / (1 / 24)), 
          a = [
            f = Number.Round(List.Sum(rt[ft]), 2, RoundingMode.AwayFromZero), 
            r = Number.Round(List.Sum(rt[rt]), 2, RoundingMode.AwayFromZero)
          ]
        ][a]
      }
    }
  ), 
  #"Expanded Count" = Table.ExpandRecordColumn(
    #"Grouped Rows", 
    "Count", 
    {"f", "r"}, 
    {"Flight Time", "Rest Time"}
  )
in
  #"Expanded Count"

Power Query solution 15 for Pilot Flight Rest Hours, proposed by Arden Nguyen, CPA:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  a = Table.TransformColumnTypes(
    Source, 
    {{"Pilot", type text}, {"Flight Start", type datetime}, {"Flight End", type datetime}}
  ), 
  b = Table.Group(
    a, 
    {"Pilot"}, 
    {
      {
        "Rows", 
        each 
          let
            _a = List.Sum(
              List.Transform(
                List.Zip({[Flight End], [Flight Start]}), 
                each Duration.TotalHours(_{0} - _{1})
              )
            ), 
            _b = List.Sum(
              List.Transform(
                List.Zip({List.Skip([Flight Start]), [Flight End]}), 
                each Duration.TotalHours(_{0} - _{1})
              )
            )
          in
            [Fly Time = _a, Rest Time = _b]
      }
    }, 
    GroupKind.Global
  ), 
  c = Table.ExpandRecordColumn(b, "Rows", {"Fly Time", "Rest Time"})
in
  c

Solving the challenge of Pilot Flight Rest Hours with Excel

Excel solution 1 for Pilot Flight Rest Hours, proposed by محمد حلمي:

=LET(a,A2:A13,b,B2:B13,c,C2:C13,u,UNIQUE(a),
v,SUMIFS(c,a,u)-SUMIFS(b,a,u),
HSTACK(u,v*24,(MAXIFS(c,a,u)-MINIFS(b,a,u)-v)*24))

Excel solution 2 for Pilot Flight Rest Hours, proposed by محمد حلمي:

=LET(i,A2:A13,c,C2:C13,b,B2:B13,u,UNIQUE(i),
v,MAP(u,LAMBDA(a,SUM((i=&a)*(c-b)*24))),
HSTACK(u,v,(MAXIFS(c,i,u)-MINIFS(b,i,u))*24-v))

Excel solution 3 for Pilot Flight Rest Hours, proposed by Kris Jaganah:

=LET(a,A2:A13,b,B2:B13,c,C2:C13,d,UNIQUE(a),HSTACK(VSTACK("Pilot",d),REDUCE({"Fly","Rest"}&" Time",d,LAMBDA(x,y,VSTACK(x,ROUND(HSTACK(SUM((a=y)*(c-b)),SUM((a=y)*(VSTACK(DROP(a,1),"")=a)*(VSTACK(DROP(b,1),0)-c)))*24,2))))))

Excel solution 4 for Pilot Flight Rest Hours, proposed by Duy Tùng:

=LET(a,A2:A13,REDUCE(E1:G1,UNIQUE(a),LAMBDA(x,y,LET(u,FILTER(B2:B13,a=y),k,FILTER(C2:C13,a=y),VSTACK(x,HSTACK(y,SUM(k-u)*24,IFERROR(SUM(DROP(u,1)-DROP(k,-1))*24,"")))))))

=LET(a,A2:A13,b,B2:B13,c,C2:C13,DROP(GROUPBY(HSTACK(XMATCH(a,a),a),HSTACK(c-b,IFERROR(b-C1:C12,0)),HSTACK(LAMBDA(v,SUM(v)*24),LAMBDA(x,IFERROR(SUM(DROP(x,1))*24,""))),,0),1,1))

Excel solution 5 for Pilot Flight Rest Hours, proposed by Sunny Baggu:

=LET(
 _u, UNIQUE(A2:A13),
 _a, MAP(_u, LAMBDA(a, SUM((C2:C13 - B2:B13) * (A2:A13 = a)) * 24)),
 _b, MAP(_u, LAMBDA(a, 24 * (MAX(FILTER(C2:C13, A2:A13 = a)) - MIN(FILTER(B2:B13, A2:A13 = a))))) - _a,
 HSTACK(_u, _a, _b)
)

Excel solution 6 for Pilot Flight Rest Hours, proposed by LEONARD OCHEA 🇷🇴:

=LET(p,A2:A13,s,B2:B13,e,C2:C13,D,LAMBDA(x,VSTACK(0,DROP(x,-1))),f,e-s,r,(p=D(p))*(s-D(e)),VSTACK({"Pilot","Fly Time","Rest Time"}, GROUPBY(p,24*HSTACK(f,r),SUM,,0)))

Excel solution 7 for Pilot Flight Rest Hours, proposed by 🇵🇪 Ned Navarrete C.:

=LET(p,A2:A13,u,UNIQUE(p),REDUCE({"Pilot","Fly time","Rest time"},u,LAMBDA(a,v, LET(s,FILTER(B2:B13,p=v),e,FILTER(C2:C13,p=v),n,DROP(s,1),p,DROP(e,-1), VSTACK(a,HSTACK(v,SUM((e-s)*24),IFERROR(SUM((n-p)*24),"")))))))

Excel solution 8 for Pilot Flight Rest Hours, proposed by An Nguyen:

=LET(
 p, UNIQUE(A2:A13),
 f, (C2:C13 - B2:B13) * 24,
 calc, LAMBDA(x,y, MAP(x, LAMBDA(a, ROUND(SUM(FILTER(y, A2:A13 = a)), 2)))),
 r, IF(A2:A13 <> A3:A14, 0, B3:B14 - C2:C13) * 24,
 VSTACK({"Pilot","Fly Time","Rest time"}, HSTACK(p, calc(p, f), calc(p, r)))
)

Excel solution 9 for Pilot Flight Rest Hours, proposed by Surendra Reddy:

=LET(x,A2:A13,y,B2:B13,z,C2:C13,u,UNIQUE(x),f,(SUMIFS(z,x,u)-SUMIFS(y,x,u))*24,r,(MAXIFS(z,x,u)-MINIFS(y,x,u))*24,VSTACK(A1:C1,HSTACK(u,f,r-f)))

Thank you محمد حلمي

Sometimes, we miss using good old functions :)

Excel solution 10 for Pilot Flight Rest Hours, proposed by Surendra Reddy:

=LET(u,UNIQUE(A2:A13),VSTACK(A1:C1,HSTACK(u,IFERROR(DROP(REDUCE("",u,LAMBDA(x,y,VSTACK(x,LET(a,TOCOL(FILTER(B2:C13,(A2:A13=y))),BYCOL(WRAPROWS(DROP((DROP(a,1)-a)*24,-1),2,0),LAMBDA(x,SUM(x))))))),1),""))))

Solving the challenge of Pilot Flight Rest Hours with Python

Python solution 1 for Pilot Flight Rest Hours, proposed by Jan Willem Van Holst:

import pandas as pd
from itertools import chain
import numpy as np
df = pd.read_csv(r"C:JWLENOVOPYTHONPQ challengesPQ_Challenge_153.csv", sep=',', usecols=[0,1,2],
 dayfirst=True, parse_dates=[1,2])
df_group = df.groupby(['Pilot'], sort=False)
shifted = [item.iloc[0:1]['Flight Start'].to_list() + item['Flight End'].to_list()[:-1] for key, item in df_group]
 
df['shifted'] = list(chain.from_iterable(shifted))
df['Fly Time']=(df['Flight End']-df['Flight Start']) / pd.Timedelta(hours=1)
df['Rest Time']=(df['Flight Start']-df['shifted']) / pd.Timedelta(hours=1)
df_group_extended = df.groupby(['Pilot'], sort=False).agg(Fly_Time=('Fly Time', np.sum), Rest_Time=('Rest Time', np.sum))
print(df_group_extended)

Solving the challenge of Pilot Flight Rest Hours with Python in Excel

Python in Excel solution 1 for Pilot Flight Rest Hours, proposed by Alejandro Campos:

df = xl("A1:C13", headers=True)
df['Flight Start'] = pd.to_datetime(df['Flight Start'], format='%d/%m/%Y %H:%M')
df['Flight End'] = pd.to_datetime(df['Flight End'], format='%d/%m/%Y %H:%M')
df['Flight Duration'] = (df['Flight End'] - df['Flight Start']).dt.total_seconds() / 3600
df['Rest Duration'] = df.groupby('Pilot')['Flight Start'].shift(-1) - df['Flight End']
df['Rest Duration'] = df['Rest Duration'].dt.total_seconds() / 3600
flight_time = df.groupby('Pilot')['Flight Duration'].sum().reset_index()
rest_time = df.groupby('Pilot')['Rest Duration'].sum().reset_index()
results = pd.merge(flight_time, rest_time, on='Pilot', how='left')
results.columns = ['Pilot', 'Total Flight Time (hours)', 'Total Rest Time (hours)']
results

Solving the challenge of Pilot Flight Rest Hours with R

R solution 1 for Pilot Flight Rest Hours, proposed by Konrad Gryczan, PhD:

library(tidyverse)
library(readxl)
input = read_excel("Power Query/PQ_Challenge_153.xlsx", range = "A1:C13") %>% 
 janitor::clean_names()
test = read_excel("Power Query/PQ_Challenge_153.xlsx", range = "E1:G5") %>%
 janitor::clean_names()
input$pilot = factor(input$pilot, levels = unique(input$pilot), ordered = TRUE)
test$pilot = factor(test$pilot, levels = unique(test$pilot), ordered = TRUE)
result = input %>%
 group_by(pilot) %>%
 mutate(prev_landing = lag(flight_end, default = NA_POSIXct_),
 flight_time = flight_end - flight_start,
 rest_time = flight_start - prev_landing) %>%
 summarise(fly_time = sum(flight_time, na.rm = TRUE),
 rest_time = sum(rest_time, na.rm = TRUE)) %>%
 mutate(fly_time = as.numeric(fly_time, units = "hours") %>% round(2),
 rest_time = as.numeric(rest_time, units = "hours") %>% round(2)) %>%
 arrange(pilot) %>%
 ungroup() %>%
 mutate(fly_time = ifelse(fly_time == 0, NA, fly_time),
 rest_time = ifelse(rest_time == 0, NA, rest_time))

Solving the challenge of Pilot Flight Rest Hours with Power Query

Solving the challenge of Pilot Flight Rest Hours with Excel

Solving the challenge of Pilot Flight Rest Hours with Python

Solving the challenge of Pilot Flight Rest Hours with Python in Excel

Solving the challenge of Pilot Flight Rest Hours with R

Leave a Reply Cancel reply