Bus Example

BusExample.py

import pandas as pd
import numpy as np

df = df_flat = pd.DataFrame({"BS": ['BS1 - BS5', 'BS2 - BS7', 'BS1 - BS9', 'BS9 - BS1'],
                   "N" : [1, 2, 2, 1]})

df = df.pivot(columns='BS',
              values='N')

df_flat = df_flat.pivot_table(
              columns='BS',
              values='N')

for column_name, column in zip(list(df), df):
    if int(column[2:3]) < int(column[8:9]):
        for stop in range(int(column[2:3]), int(column[8:9])):
            index = "BS" + str(stop) + "-" + str(stop + 1)
            if index not in list(df.index.values):
                df.loc[index] = np.nan
            df.loc[index, column] = df_flat.loc['N', column]
    else:
        for stop in range(int(column[2:3]), int(column[8:9]), -1):
            index = "BS" + str(stop) + "-" + str(stop - 1)
            if index not in list(df.index.values):
                df.loc[index] = np.nan
            df.loc[index, column] = df_flat.loc['N', column]

df['Total'] = df.sum(axis=1)

df = df.iloc[len(list(df_flat)):]

print(df.fillna(''))


#  Output
$ python bus.py
BS    BS1 - BS5 BS1 - BS9 BS2 - BS7 BS9 - BS1  Total
BS1-2         1         2                        3.0
BS2-3         1         2         2              5.0
BS3-4         1         2         2              5.0
BS4-5         1         2         2              5.0
BS5-6                   2         2              4.0
BS6-7                   2         2              4.0
BS7-8                   2                        2.0
BS8-9                   2                        2.0
BS9-8                                       1    1.0
BS8-7                                       1    1.0
BS7-6                                       1    1.0
BS6-5                                       1    1.0
BS5-4                                       1    1.0
BS4-3                                       1    1.0
BS3-2                                       1    1.0
BS2-1                                       1    1.0