Question on pandas.dataframe merging two colums

[shomikc]

Hi

I have a dataset like below



The question is

For customers who have more than 1 booking, which is the most booked route (from_city to to_city)?

I have tried and come this far but I dont know how (the steps) to get to the final answer.

import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import seaborn as sns

df = pd.read_csv(r'C:\Edureka\Bookings.csv')
#df = pd.read_csv(r'C:\Edureka\Sessions.csv')


#a = df.pivot_table(columns=['session_id'],['booking_id'], aggfunc='size')
#print("Duplicate Sessions \n",a)


#b = df.groupby('session_id')['booking_id'].count().value_counts()
#import pandas as pd
#df2 = df.groupby(['session_id','booking_id']).size().reset_index(name='counts')

b=pd.concat(g for _, g in df.groupby("customer_id") if len(g) > 1)

c = b.sort_values(by=["customer_id","from_city","to_city"], ascending=False, kind="mergesort")
print(c)

I get the output as shown below.

Output:
customer_id   booking_id           from_city from_country            to_city  ... INR_Amount service_name no_of_passengers  days_to_departure distance_km
231   customer_97   booking_68             Gurgaon        India              Paris  ...   33274.40          MMT                1                 88      6583.3      
271   customer_97   booking_90             Gurgaon        India         Dharamsala  ...    5126.40      GOIBIBO                1                 14       416.6      
80    customer_97  booking_257          Dharamsala        India            Gurgaon  ...    6863.20      GOIBIBO                1                  3       416.6      
101   customer_97  booking_143               Delhi        India                Yol  ...    4856.80      GOIBIBO                1                 10       401.3      
127   customer_93  booking_277             Bālāpur        India         Düsseldorf  ...   43935.20        YATRA                1                112      7591.8      
316   customer_93  booking_223             Bālāpur        India         Düsseldorf  ...   41835.20        YATRA                1                127      7591.8      
256   customer_67  booking_286          Devanhalli        India            Gurgaon  ...    5971.63        YATRA                1                  2      1694.7      
333   customer_67  booking_328          Devanhalli        India            Gurgaon  ...    5635.73          MMT                1                  2      1694.7      
48    customer_49   booking_56             Gurgaon        India   Roissy-en-France  ...   22013.60      GOIBIBO                1                  1      6564.2      
259   customer_49   booking_34             Gurgaon        India   Roissy-en-France  ...   66720.00        YATRA                1                  3      6854.8      
89    customer_44   booking_69             Gurgaon        India         Rāja Sānsi  ...    4700.00      GOIBIBO                1                  3       414.6      
217   customer_44  booking_130             Gurgaon        India         Rāja Sānsi  ...    2353.60      GOIBIBO                1                 30       414.6      
329   customer_33  booking_187             Gurgaon        India             Berlin  ...   20759.20      GOIBIBO                1                  2      6008.3      
253   customer_33  booking_198             Bikaner        India            Gurgaon  ...    9924.00      GOIBIBO                2                  0       367.5      
109  customer_297   booking_29             Gurgaon        India            Tiruchi  ...    5019.20        YATRA                1                  2      1970.8      
321  customer_297  booking_258             Gurgaon        India            Tiruchi  ...   10568.80      GOIBIBO                1                  1      1970.8      
100  customer_282  booking_189              Mumbai        India           Chicalim  ...    4616.00      GOIBIBO                1                  1       425.1      
13   customer_282  booking_212              Indore        India             Mumbai  ...    4725.60      GOIBIBO                1                  0       506.2      
294   customer_28  booking_314         Madhyamgram        India  Frankfurt am Main  ...   56064.80      GOIBIBO                1                 19      7380.7      
6     customer_28  booking_129             Kolkata        India            Gurgaon  ...   13442.40      GOIBIBO                1                 13      1310.7      
140  customer_265  booking_124             Gurgaon        India   Roissy-en-France  ...   24072.00      GOIBIBO                1                  7      6854.8      
295  customer_265  booking_281             Gurgaon        India   Roissy-en-France  ...   24072.00      GOIBIBO                1                  9      6854.8      
150  customer_255  booking_291             Gurgaon        India           Bagdogra  ...    3804.80          MMT                1                  1      1128.9      
261  customer_255  booking_145             Gurgaon        India           Bagdogra  ...    7663.20          MMT                2                  4      1128.9      
326  customer_247   booking_48          Rāja Sānsi        India            Gurgaon  ...    5707.20      GOIBIBO                1                 23       414.6      
29   customer_247  booking_119             Gurgaon        India          Fiumicino  ...   21788.00      GOIBIBO                1                 25      5932.6      
145  customer_246  booking_263             Gurgaon        India   Roissy-en-France  ...   43783.20      GOIBIBO                1                  4      6564.2      
172  customer_246  booking_177             Gurgaon        India             Lisbon  ...   84777.60      GOIBIBO                2                  2      7767.7      
65   customer_246   booking_25               Delhi        India              Paris  ...   33216.00      GOIBIBO                1                  9      6585.3      
55   customer_245  booking_215                Pune        India             Nagpur  ...    7241.46          MMT                1                  7       621.7      
303  customer_245   booking_27             Gurgaon        India             Mumbai  ...    6947.26          MMT                1                  1      1125.8      
293  customer_237  booking_172  Thiruvananthapuram        India             London  ...   81499.86      GOIBIBO                1                 79      8375.4      
162  customer_237  booking_140               Kochi        India             London  ...   62807.81      GOIBIBO                1                 20      8203.4      
113  customer_235   booking_26             Gurgaon        India        Bhubaneswar  ...    6467.20      GOIBIBO                1                  2      1274.7      
335  customer_235   booking_37             Gurgaon        India        Bhubaneswar  ...    6552.00      GOIBIBO                1                  0      1274.7      
254  customer_230    booking_3          Rāja Sānsi        India              Paris  ...   24619.20      GOIBIBO                1                 11      6184.7      
276  customer_230   booking_61             Gurgaon        India              Paris  ...   57776.80      GOIBIBO                3                 18      6583.3      
151  customer_230  booking_322             Gurgaon        India            Hamburg  ...   63316.80        YATRA                2                 39      6277.5      
211  customer_217  booking_194             Durlaga        India        Bhubaneswar  ...    2176.00      GOIBIBO                1                 56       260.5      
313  customer_217  booking_162             Durlaga        India        Bhubaneswar  ...    2176.00      GOIBIBO                1                 61       260.5      
7    customer_217   booking_18         Bhubaneswar        India            Durlaga  ...    4352.00      GOIBIBO                2                 47       260.5      
300  customer_200  booking_292            Varanasi        India              Delhi  ...    2800.80          MMT                1                  9       682.4      
175  customer_200  booking_113               Delhi        India           Varanasi  ...    2837.60        YATRA                1                  9       682.4      
24   customer_179  booking_157              Mumbai        India             Jaipur  ...    6953.60      GOIBIBO                1                 15       921.8      
16   customer_179  booking_218             Gurgaon        India         Port Blair  ...   17804.80          MMT                2                 35      2486.8      
305  customer_176  booking_174             Gurgaon        India  Frankfurt am Main  ...   66385.60      GOIBIBO                1                 75      6112.5      
173  customer_176  booking_297             Gurgaon        India             Barelā  ...   11001.60      GOIBIBO                2                 11       666.7      
81   customer_176    booking_2              Barelā        India            Gurgaon  ...   13067.20      GOIBIBO                3                  3       666.7      
200  customer_160  booking_122               Patna        India            Gurgaon  ...    6440.11          MMT                1                  1       862.3      
116  customer_160  booking_313              Mumbai        India            Lucknow  ...    9441.95          MMT                1                  3      1193.1      
323  customer_160  booking_278             Gurgaon        India              Patna  ...    5232.60          MMT                1                  2       862.3      
85   customer_155  booking_261              Jaipur        India               Agra  ...    2008.00      GOIBIBO                1                 11       222.7      
274  customer_155   booking_54             Gurgaon        India           Varanasi  ...    4555.20        YATRA                1                  6       688.7      
111  customer_144  booking_296               Surat        India         Devanhalli  ...    8208.80      GOIBIBO                1                  1      1022.5      
227  customer_144  booking_173           Ahmedabad        India            Gwalior  ...   16905.46      GOIBIBO                1                  2       664.7      
163  customer_137  booking_190             Gurgaon        India         Rāja Sānsi  ...   12094.40      GOIBIBO                1                  1       421.0      
42   customer_137   booking_88             Gurgaon        India         Birmingham  ...   46142.40        YATRA                1                 19      7190.9      
191  customer_120  booking_299               Jammu        India              Dubai  ...   16753.73        YATRA                1                  5      2076.5      
190  customer_120  booking_151               Delhi        India              Jammu  ...    4604.80          MMT                1                  1       506.9

Can anyone please help me? Thanks.

[Larz60+]

your image didn't post properly.
It would be better to attach the data (if files are small enough).

[shomikc]

your image didn't post properly.
It would be better to attach the data (if files are small enough).

Hi. The dataset is as attached below. (https://imgur.com/a/NaEZfYk)

[shomikc]

your image didn't post properly.
It would be better to attach the data (if files are small enough).

Hi. Sorry for the image not being uploaded. The dataset can be found at this URL: https://imgur.com/a/NaEZfYk. Thankyou.

[snippsat]

Hi. Sorry for the image not being uploaded. The dataset can be found at this URL: https://imgur.com/a/NaEZfYk. Thankyou.

You should post the data(Bookings.csv) or sample data,can not to anything with a image.
So pretty most no one will answer,if can not do test with the data.
Looking at your output(not the raw data) can try some like this.

df = pd.DataFrame(data)
customer_counts = df['customer_id'].value_counts()
multi_booking = customer_counts[customer_counts > 1].index
multi_booking = df[df['customer_id'].isin(multi_booking)]
# Calculate the most booked route
most_booked_route = multi_booking.groupby(['from_city', 'to_city']).size().idxmax()