mysql - Use sql or pandas to complete data from one table to another.

Question

As shown in the figure, Figure 1 shows the number of people entering the station at each subway station at each time. Since it is a simulated real data, for example, no one entered the station at Qilizhuang at 5 o'clock, so there is no data record, while Figure 2 is the complete period. The schedule is from 5 o'clock to 23 o'clock, every 15 minutes. Now I want each subway station in Figure 1 to be completed according to the timetable in Figure 2. If there is no record at this subway station at this time, it proves that no one enters the station. , the number of people entering the station defaults to 0.

It would be best if you can use sql, but you can also use pandas.
But I don’t know how to do it, so I’m asking for help.

Answer 1

According to your current table structure, the main table in Figure 2, the left connection table in Figure 1, the condition start time = start time, end time = end time. Query the start and end time of Figure 2 and the number of people in Figure 1

Suppose there is a site table that records the basic information of all sites.

Seletc Site,Start,End,Cnt from
( select Site,Start,End,timeKey from SiteTbl,TimeZone) a
left join (.....) b on a.Site=b.Site a.timeKey = b.timeKey

Answer 2

select table2.起始时间, table2.结束时间, ifnull(table1.进站人数, 0) "进站人数"
from table2
left join table1 on table1.起始时间=table2.起始时间 and table1.结束时间=table2.结束时间

PS: If possible, please add a station name field to the second photo.