Plot a timetable for charging electric vehicles

6 views (last 30 days)
Can the following excel table be displayed graphically as I have drawn in the jpg file?

Accepted Answer

Peter Perkins
Peter Perkins on 9 Aug 2021
Sure. It looks like you want to overlay not only all stands, but also all days on one (24 hour?) time of day axis. That makes it a little trickier. I may be overcomplicating things, but here's my take:
First read the spreadsheet. There are two timestamps for each row, so read it as a table.
>> tt = readtable("Charging Timetable.xlsx")
Warning: Column headers from the file were modified to make them valid MATLAB identifiers before creating variable names for the table. The original column headers are
saved in the VariableDescriptions property.
Set 'VariableNamingRule' to 'preserve' to use the original column headers as table variable names.
tt =
4×5 table
ArrivalDate_Time DepartureDate_Time Registration Stand ChargingRate_kW_
____________________ ____________________ ____________ _____ ________________
02-Jan-2020 08:00:00 02-Jan-2020 09:00:00 {'GGF1'} {'A'} 200
02-Jan-2020 10:00:00 02-Jan-2020 12:00:00 {'GGF2'} {'C'} 300
03-Jan-2020 11:00:00 03-Jan-2020 13:00:00 {'GGF3'} {'B'} 400
03-Jan-2020 14:00:00 03-Jan-2020 15:00:00 {'GGF4'} {'A'} 300
Clean up a few things.
>> tt.Properties.VariableNames(1:2) = ["Arrive" "Depart"];
tt.Registration = categorical(tt.Registration);
tt.Stand = categorical(tt.Stand)
tt =
4×5 table
Arrive Depart Registration Stand ChargingRate_kW_
____________________ ____________________ ____________ _____ ________________
02-Jan-2020 08:00:00 02-Jan-2020 09:00:00 GGF1 A 200
02-Jan-2020 10:00:00 02-Jan-2020 12:00:00 GGF2 C 300
03-Jan-2020 11:00:00 03-Jan-2020 13:00:00 GGF3 B 400
03-Jan-2020 14:00:00 03-Jan-2020 15:00:00 GGF4 A 300
Using the stairs function might be one way to make this plot, maybe even simpler than what I've done. But I'm gonna use plot. First I'll stack up those pairs of times, giving each row only one timestamp, and then turn that into a timetable.
>> tt = stack(tt,["Arrive" "Depart"]);
tt.Properties.VariableNames(end) = "Time"
tt =
8×5 table
Registration Stand ChargingRate_kW_ Arrive_Depart_Indicator Time
____________ _____ ________________ _______________________ ____________________
GGF1 A 200 Arrive 02-Jan-2020 08:00:00
GGF1 A 200 Depart 02-Jan-2020 09:00:00
GGF2 C 300 Arrive 02-Jan-2020 10:00:00
GGF2 C 300 Depart 02-Jan-2020 12:00:00
GGF3 B 400 Arrive 03-Jan-2020 11:00:00
GGF3 B 400 Depart 03-Jan-2020 13:00:00
GGF4 A 300 Arrive 03-Jan-2020 14:00:00
GGF4 A 300 Depart 03-Jan-2020 15:00:00
>> tt = table2timetable(tt)
tt =
8×4 timetable
Time Registration Stand ChargingRate_kW_ Arrive_Depart_Indicator
____________________ ____________ _____ ________________ _______________________
02-Jan-2020 08:00:00 GGF1 A 200 Arrive
02-Jan-2020 09:00:00 GGF1 A 200 Depart
02-Jan-2020 10:00:00 GGF2 C 300 Arrive
02-Jan-2020 12:00:00 GGF2 C 300 Depart
03-Jan-2020 11:00:00 GGF3 B 400 Arrive
03-Jan-2020 13:00:00 GGF3 B 400 Depart
03-Jan-2020 14:00:00 GGF4 A 300 Arrive
03-Jan-2020 15:00:00 GGF4 A 300 Depart
Using plot, the data will need to include all four "corners" of each session. Also, overlaying all the days means that you'd have a lot of back and forth lines at zero. That might be fine, but instead I'm going to add a "break" after each session.
>> n = height(tt);
tt = tt(repelem(1:n,repmat([2 3],1,n/2)),:);
tt.ChargingRate_kW_(1:5:end) = 0;
tt.ChargingRate_kW_(4:5:end) = 0;
tt.ChargingRate_kW_(5:5:end) = NaN % line break
tt =
20×4 timetable
Time Registration Stand ChargingRate_kW_ Arrive_Depart_Indicator
____________________ ____________ _____ ________________ _______________________
02-Jan-2020 08:00:00 GGF1 A 0 Arrive
02-Jan-2020 08:00:00 GGF1 A 200 Arrive
02-Jan-2020 09:00:00 GGF1 A 200 Depart
02-Jan-2020 09:00:00 GGF1 A 0 Depart
02-Jan-2020 09:00:00 GGF1 A NaN Depart
02-Jan-2020 10:00:00 GGF2 C 0 Arrive
02-Jan-2020 10:00:00 GGF2 C 300 Arrive
02-Jan-2020 12:00:00 GGF2 C 300 Depart
02-Jan-2020 12:00:00 GGF2 C 0 Depart
02-Jan-2020 12:00:00 GGF2 C NaN Depart
03-Jan-2020 11:00:00 GGF3 B 0 Arrive
03-Jan-2020 11:00:00 GGF3 B 400 Arrive
03-Jan-2020 13:00:00 GGF3 B 400 Depart
03-Jan-2020 13:00:00 GGF3 B 0 Depart
03-Jan-2020 13:00:00 GGF3 B NaN Depart
03-Jan-2020 14:00:00 GGF4 A 0 Arrive
03-Jan-2020 14:00:00 GGF4 A 300 Arrive
03-Jan-2020 15:00:00 GGF4 A 300 Depart
03-Jan-2020 15:00:00 GGF4 A 0 Depart
03-Jan-2020 15:00:00 GGF4 A NaN Depart
Now we just need to make al the plots, using time of day, not absolute day.
>> for stand = categories(tt.Stand)'
idx = (tt.Stand == stand);
plot(timeofday(tt.Time(idx)),tt.ChargingRate_kW_(idx),"LineWidth",2);
hold on
end
hold off
legend(["A" "B" "C"]);
  2 Comments
Mark Maders
Mark Maders on 12 Aug 2021
Can the legend be produced automatically, so that you do not type
legend(["A" "B" "C"]);
say the legend could be anything within the range of A-Z, is there a way you could make it show whatever happens to appear in the stand column for the day in question?

Sign in to comment.

More Answers (0)

Categories

Find more on Line Plots in Help Center and File Exchange

Products


Release

R2021a

Community Treasure Hunt

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

Start Hunting!