2

I have two different dataframes:
The first dataframe stores some possible train connections (like a timetable):

index route start stop
0     1     a     b
1     1     b     c
2     1     c     d
3     1     d     e
4     2     g     h
5     2     h     i
6     2     i     j

The second dataframe is a measurement of actual train stops:

index start stop passengers
0     a     b    2
1     b     d    4
2     a     c    1
3     c     d    2
4     g     j    5

Sometimes the train does not stop at a station. What I try to achieve is to fill the missing stops and still keep track of the passenger measurement:

index route start stop passengers
0     1     a     b    2
1     1     b     c    4
2     1     c     d    4
3     1     a     b    1
4     1     b     c    1
5     1     c     d    2
6     2     g     h    5
7     2     h     i    5
8     2     i     j    5

As a result I would just like to fill up all stops that have been skipped.

Fran.yanno
  • 105
  • 11

1 Answers1

0

As Wen pointed out, Pandas is probably not optimal to represent such data. If you want to work with Pandas, I would recommend to switch from "connecting stations" via their proximity in the df (next row = next station unless it's a different route / using letters to define order) to numeric identifiers and keeping routes, names, etc. in different columns. If you use numeric identifiers, here's a possible implementation of adding up the passengers. Different route are differentiate by being either 100+station number or 200+station number:

table = pd.DataFrame({'route':['g','g','g','g','r','r','r'],'start':[101,102,103,104,201,202,203],
                  'stop':[102,103,104,105,202,203,204],'count':[0,0,0,0,0,0,0]})
passenger = pd.DataFrame({'start':[101,102,202],'stop':[104,103,204],
                         'passenger':[2,5,3]})

count = list(zip(passenger.start.tolist(),passenger.stop.tolist(),passenger.passenger.tolist())) #merge the start, stop and count into one list for each entry
for c in count:
    for x in range(c[0],c[1]+1): #go through each stop and add the count to the train table
        table['count'] = np.where(table.start == x, table['count'] + c[2], table['count'])
table #Now with the passenger data
David
  • 220
  • 1
  • 11