I have the following table, which has origin/destination cities and extra cities that are stops on the origin-destination route. This is an example for origin Augsburg and destinationn Telfs.

What I want from this table is to obtain the number of different existing routes, that is, the number of routes where the same stopping cities are not repeated. For example in this case it would be 11 (because one row doesnt have any city different to the origin/destination). I want to build a table like this:

any idea or solution?

to reproduce dataframe : https://drive.google.com/file/d/1nFLb0yCP24lcbFqqYRt5bq6yttjQNxGh/view?usp=sharing

CodePudding user response：

IIUC: Here is a code that identifies there are 13 unique routes not 11.

import pandas as pd
df = pd.read_clipboard()
df = df[[x for x in df.columns if not 'Unnamed' in x]]
df = df.assign(hash=df.apply(lambda x: hash("".join(x.fillna("")[1:])), axis=1))
df.groupby(["origin", "destination", "hash"])["city"].count().count()

CodePudding user response：

Here is one potential solution, with columns named "origin" and "destination":

def get_start_and_end(row):
    return row[row.last_valid_index()]


def get_number_of_unique_routes_by_group(group):
    return len(group.apply(get_start_and_end, axis=1).unique())


df.groupby(["origin", "destination"]).apply(get_number_of_unique_routes_by_group)

Output:

origin    destination
Augsburg  Telfs          13
dtype: int64

Your example dataframe only included one such group, but if you had other origin-destination groupings, they'd show up in the output too.