Python pandas: Identify route/path given by two columns
Question: 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 wich 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 permormant 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 inplace 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 neet 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 identifiy 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_


Answer: You can use [`stack`](http://pandas.pydata.org/pandas-
docs/stable/generated/pandas.DataFrame.stack.html) with
[`drop_duplicates`](http://pandas.pydata.org/pandas-
docs/stable/generated/pandas.DataFrame.drop_duplicates.html) and last
[`pivot`](http://pandas.pydata.org/pandas-
docs/stable/generated/pandas.pivot.html). Next group was added for better
testing:

    
    
    print df
            group  size    from      to
    0   group32a4   500  6sq2gp  m4qfce
    1   group32a4   800  oxlwtg  ru1u5r
    2   group32a4  1200  rpziz0  oxlwtg
    3   group32a4  1400  ru1u5r  fvvskj
    4   group32a4   500  m4qfce  60m2eq
    5   group32a4    50  fvvskj  6sq2gp
    6   group13a4   500  6sq2gp  m4qfce
    7   group13a4   800  oxlwtg  ru1u5r
    8   group13a4  1200  rpziz0  oxlwtg
    9   group13a4  1400  ru1u5r  fvvskj
    10  group13a4   500  m4qfce  60m2eq
    11  group13a4    50  fvvskj  6sq2gp
    
    #set index and stack data - columns 'from' and 'to' to one column 'route'
    df = df.set_index(['group', 'size']).stack().reset_index(name='route')
    
    
    
    print df
            group  size level_2   route
    0   group32a4   500    from  6sq2gp
    1   group32a4   500      to  m4qfce
    2   group32a4   800    from  oxlwtg
    3   group32a4   800      to  ru1u5r
    4   group32a4  1200    from  rpziz0
    5   group32a4  1200      to  oxlwtg
    6   group32a4  1400    from  ru1u5r
    7   group32a4  1400      to  fvvskj
    8   group32a4   500    from  m4qfce
    9   group32a4   500      to  60m2eq
    10  group32a4    50    from  fvvskj
    11  group32a4    50      to  6sq2gp
    12  group13a4   500    from  6sq2gp
    13  group13a4   500      to  m4qfce
    14  group13a4   800    from  oxlwtg
    15  group13a4   800      to  ru1u5r
    16  group13a4  1200    from  rpziz0
    17  group13a4  1200      to  oxlwtg
    18  group13a4  1400    from  ru1u5r
    19  group13a4  1400      to  fvvskj
    20  group13a4   500    from  m4qfce
    21  group13a4   500      to  60m2eq
    22  group13a4    50    from  fvvskj
    23  group13a4    50      to  6sq2gp
    
    
    
    def f(x):
        #set column size to max
        x['size'] =  x['size'].max()
        return x.drop_duplicates('route', keep=False)
    
    #apply custom function f
    df = df.groupby('group').apply(f).reset_index(drop=True)
    print df
           group  size level_2   route
    0  group13a4  1400    from  rpziz0
    1  group13a4  1400      to  60m2eq
    2  group32a4  1400    from  rpziz0
    3  group32a4  1400      to  60m2eq
    
    #reshape data, remove column tmp
    df = df.pivot(index='group', columns='level_2').reset_index()
    df.columns = ['group','size','tmp','from', 'to']
    df = df.drop('tmp', axis=1)
    print df
           group  size    from      to
    0  group13a4  1400  rpziz0  60m2eq
    1  group32a4  1400  rpziz0  60m2eq
    

EDIT:

Similar, I think faster solution with filling DataFrame in
[`groupby`](http://pandas.pydata.org/pandas-
docs/stable/generated/pandas.DataFrame.groupby.html) with
[`apply`](http://pandas.pydata.org/pandas-
docs/stable/generated/pandas.core.groupby.GroupBy.apply.html) function `f` and
[`iat`](http://pandas.pydata.org/pandas-
docs/stable/generated/pandas.Series.iat.html),
[`iloc`](http://pandas.pydata.org/pandas-
docs/stable/generated/pandas.DataFrame.iloc.html):

    
    
    def f(x):
        #get max of column size
        m = x['size'].max()
        #remove all duplicates - stay only one value from and one value to
        x = x.drop_duplicates('route', keep=False)
        x['group'] = x.iat[0, 0]
        x['size'] = m
        x['from'] = x.iat[0, 3]
        x['to'] = x.iat[1, 3]
    
        #print x
        #return first row and columns group, size from to
        #print x.iloc[0,[0,1,4,5]]
        return  x.iloc[0,[0,1,4,5]]
    
    #apply custom function f
    df = df.groupby('group').apply(f).reset_index(drop=True)
    print df
           group  size    from      to
    0  group13a4  1400  rpziz0  60m2eq
    1  group32a4  1400  rpziz0  60m2eq
    

