[Python] Python pandas: Identify route/path given by two columns

I have an input file (csv-file) with data which has duplicate entries in the column group and might have duplicate entries in the column size.

A snippet with the data of just one group is given below. However, there are several groups in the real data file. So this is just a shortened and simplified example (sample.csv):

group,size,from,to
group32a4,0500,6sq2gp,m4qfce
group32a4,0800,oxlwtg,ru1u5r
group32a4,1200,rpziz0,oxlwtg
group32a4,1400,ru1u5r,fvvskj
group32a4,0500,m4qfce,60m2eq
group32a4,0050,fvvskj,6sq2gp


Since the data is coming from external software I am not able to change anything concerning the data format or data layout. So I need to import the data for further data handling and do the following tasks:

1. Keep one entry for each group, only. This entry must have the biggest value in the column size.
2. Get the path which routes through the group and can be arranged from the columns from and to.

I decided to use pandas for data handling since the real data file is rather complex and I wanted to have the capability of its performant features. However, if there are any other (more suitable) tools or approaches using other Python modules those would be totally fine and not a problem at all.

In order to accomplish the first task I did:

import pandas as pd

# open file and read data
with open('sample.csv') as f:
data = pd.read_csv(f)

# sort descending by columns `group` and `size`
# sorting descending because `df.drop_duplicates()` keeps first element by default
df_sorted = data.sort_values(['group', 'size'], ascending=False)

# drop duplicates in order to keep first entry only
one_entry = df_sorted.drop_duplicates('group')

# print handled data
print(one_entry)


Which leads to the desired output:

group size from to
3 group32a4 1400 ru1u5r fvvskj


So, I need to accomplish the second task. Since all of the above data handling was not done in place I am able to access all stages of data throughout the data handling procedure.

Unfortunately, I do not have any idea about how to do that. I have some conceptual thoughts about how that could be done. First of all I need to arrange the route out of each group subset. In the example given above that would result in:

rpziz0 --> oxlwtg --> ru1u5r --> fvvskj --> 6sq2gp --> m4qfce --> 60m2eq


After that I need to extract source and destination and summarize the route like that:

rpziz0 --> 60m2eq


Which should result into this overall output:

group size from to
3 group32a4 1400 rpziz0 60m2eq


So the question I came up with is as follows:

How can I identify the route out of each subset which is defined by each group tag (using pandas' methods preferably)?

Note: Using Python 3.4.3, Pandas 0.17.1

Continue reading...