Borrar filtros
Borrar filtros

How do I pull data between specific dates like Vlookup formula in Excel?

2 visualizaciones (últimos 30 días)
I have flight data and ultimately what I want to find out is how many flights there are in which minute. Since I will be analyzing yearly data, my reference interval will start from 01/01/2019 00:00 and continue until 31/12/2022 23:59. What I am trying to do is to print the numbers in the "Total_Min_ATM" I created against the datetimes in the Reference_Table. For 2 days I have tried almost every formula for this but with no results.Also another problem is that when I use groupsummary in Seat_Capacity or Direct_Pax tables, it does counting, not summing. Thank you in advance for your help. I hope there is a solution! Best Regards,
[nmb,txt,allof] = xlsread('sampledata_uyyk.csv'); Maindata=allof; NumberData=nmb; [~, idx] = sort(datetime(Maindata(:,3),'InputFormat', 'MM/dd/uuuu hh:mm:ss aa'), 1, 'ascend'); SortedLastData = Maindata(idx,:); t1 = datetime ('01/01/2019 00:00:00','Format','MM/dd/uuuu hh:mm:ss aa'); t2 = datetime ('31/12/2022 23:59:00','Format','MM/dd/uuuu hh:mm:ss aa'); Reference_Table = transpose(t1:minutes(1):t2); Arr_Dep =SortedLastData(:,1); Scheduled_Time =SortedLastData(:,2); Actual_Time =SortedLastData(:,3); Flight_Number =SortedLastData(:,4); Dom_Int =SortedLastData(:,5); Airline_Code =SortedLastData(:,6); ATM_Number = SortedLastData(:,7); Fleet_Type = SortedLastData(:,8); Seat_Capacity = SortedLastData(:,9); Direct_Pax = SortedLastData(:,10); Transfer_Pax = SortedLastData(:,11); Total_Pax = SortedLastData(:,12); Destination = SortedLastData(:,13); Country = SortedLastData(:,14); Region = SortedLastData(:,15); NB_WB = SortedLastData(:,16); Real_Time = SortedLastData(:,17); Schedule_Ver = SortedLastData(:,18); ATM_Table = table(Actual_Time,ATM_Number); Total_Min_ATM = groupsummary(ATM_Table,"Actual_Time");

Respuestas (2)

Walter Roberson
Walter Roberson el 15 de Dic. de 2022
Create a timetable and index it with a timerange
Once you have the subset, you can retime with a function handle that returns the row count of what it has been passed
This approach might require some modification if what you have is a start time and minutes in the air rather than an entry for every minute. In such a case you would take the start times and add minutes () of the time in the air .... and you would need further steps after that
  2 comentarios
Ugur Yilmaz
Ugur Yilmaz el 16 de Dic. de 2022
I understand what you said, but isn't the "Reference_Table" variable already my timetable?
I just want to print the values in the "Total_Min_ATM" variable against the dates in the "Reference_Table" in a new variable. If there is no value, I want to write "0". Then I will add these values for every 60 minutes, for example, 13:21-14:20, 13:22-14:22.
Walter Roberson
Walter Roberson el 16 de Dic. de 2022
Your code is difficult to read; you should format it so that we can understand it.
Reference_Table = transpose(t1:minutes(1):t2)
The transpose() operator is never valid on a table() or timetable() object, so Reference_Table is not a timetable() object. The : operator is also not valid on table() or timetable() objects, the part inside the transpose() is not a table or timetable object either.
timetable is a specialized type of table; see timetable

Iniciar sesión para comentar.

Peter Perkins
Peter Perkins el 19 de Dic. de 2022
As Walter says, uses a timetable.
>> tt = readtimetable("sampledata_uyyk.csv")
[some warnings removed]
>> tt = convertvars(tt,["A_D" "FlightNo" "DOM_INT" "AirlineCode" "FleetType" "Destination" "Country" "Region" "FleetTypeGroup" "ScheduleVer"],"categorical");
>> tt.RealTime = datetime(tt.RealTime,Format="hh:mm a");
>> tt.RealTime = timeofday(tt.RealTime);
>> tt.RealTime.Format = "hh:mm";
>> tt
tt =
1409×17 timetable
ScheduledTime A_D ActualTime FlightNo DOM_INT AirlineCode Frq FleetType SeatCapacity DirectPax_P2P_ TransferPax TotalPax Destination Country Region FleetTypeGroup RealTime ScheduleVer
________________ ___ ________________ ________ _______ ___________ ___ _________ ____________ ______________ ___________ ________ ___________ ________________________ _______ ______________ ________ ___________
09/03/0022 23:55 A 09/04/0022 00:03 3O0433 INT 3O 1 A320 180 170 0 170 FEZ MOROCCO AFRICA Narrow Body 00:03 IST0922
09/04/0022 00:50 D 09/04/0022 00:58 3O0434 INT 3O 1 A320 180 150 0 150 FEZ MOROCCO AFRICA Narrow Body 00:58 IST0922
09/04/0022 10:00 A 09/04/0022 09:22 3U3827 INT 3U 1 A333 305 0 0 0 CTU China ASIA Wide Body 09:22 IST0922
: : : : : : : : : : : : : : : : : :
09/04/0022 13:00 D 09/04/0022 13:44 ZZ0018 INT ZZ 1 CRJ2 15 10 0 10 FAB ENGLAND EUROPE Narrow Body 13:44 IST0922
09/04/0022 16:00 A 09/04/0022 15:21 ZZ0022 DOM ZZ 1 FA8X 14 1 0 1 ADB TURKEY TURKEY Narrow Body 15:21 IST0922
09/04/0022 15:15 D 09/04/0022 16:20 ZZ0025 INT ZZ 1 E135 13 8 0 8 HER GREECE EUROPE Narrow Body 16:20 IST0922
Display all 1409 rows.
I've chopped out some fairly self-explanatory warnings that can be dealt with using additional inputs to readtimetable, and the reformatting can also be handled as part of reading in data, using detectimportoptions (as can the warnings).
At this point, counting flights is just a call to retime.


Más información sobre Tables en Help Center y File Exchange.




Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!

Translated by