Convert Dataframe into pivot with grouping of values into a list

Question

The Problem

I am handling an existing excel sheet which has values, which needs to transformed in a way that the Distinct values I wish to collect are spread across different Columns and against the unique values I need to list (and eventually append) one of the column's value.

The Input

sr_no, branch_code, Tier1, Tier2, Tier3, Rural
1, 12, Mumbai, Thane, 0, 0
2, 33, Navi Mumbai, Pimpri, Lonavala, 0
3, 120, Pune, Pimpri, 0, 0
4, 8, Surat, Saputara, 0, Silvassa
5, 15, Pune, Nashik, Saputara, 0 
6, 49, Banglore, Mumbai, 0, Saputara

The columns from which I want unique values to scan for are as below:
["Tier1", "Tier2", "Tier3", "Rural"] --- (A)

Getting the unique values is not the problem, ["Mumbai", "Thane", "Navi" "Pimpri", "Surat", "Pune", "Saputara", "Silvassa", "Nashik", "Banglore"]
having the unique values scanned/ matched over each of the record to collect branch_code is the challenge.

The Trials

I tried having a combined values of required columns from (A) into a list.

BranchMaster = pd.read_excel("BranchMaster.xlsx")

scan_cols = ["Tier1", "Tier2", "Tier3", "Rural"]
BranchMaster["Combined"] = BranchMaster.loc[:, scan_cols].values.tolist()

Now, I can literally iterate over each record and collect the branch_codes, but I believe there has to be a more optimized way to do the same.

The Required Output

This output I need to be in Dataframe format only, so it doesn't matter if I can store the value in CSV, TSV and so on.

city, branch_code
Mumbai, [12, 49]
Thane, [12]
Navi Mumbai, [33]
Pimpri, [33, 120]
Surat, [8]
Pune, [120, 15]
Saputara, [8, 15, 49]
Silvassa, [8]
Nashik, [15]
Banglore, [49]

Answer 1

You can do a melt and groupby:

(df.melt('branch_code', ["Tier1", "Tier2", "Tier3", "Rural"],
         value_name='city')
   .loc[lambda x: x['city']!='0']
   .groupby('city')['branch_code'].agg(list)
   .reset_index()
)

Output:

           city  branch_code
0      Banglore         [49]
1      Lonavala         [33]
2        Mumbai     [12, 49]
3        Nashik         [15]
4   Navi Mumbai         [33]
5        Pimpri    [33, 120]
6          Pune    [120, 15]
7      Saputara  [8, 15, 49]
8      Silvassa          [8]
9         Surat          [8]
10        Thane         [12]