Trying to sort a column from text file

user_13014305 0
Viewed 106

I am trying to sort a specific column from the given text file. The text file contains below information.

Interface    Link  Input packets        (pps)     Output packets        (pps)
 ge-0/0/0      Up              0          (0)                0          (0)
 gr-0/0/0      Up              0          (0)                0          (0)
 ip-0/0/0      Up              0          (0)                0          (0)
...
 ge-0/0/8    Down              0          (0)                0          (0)
 ge-0/0/9    Down              0          (0)                0          (0)
 fxp2          Up              0                         87559
 gre           Up              0                             0
 ipip          Up              0                             0

I am trying to sort the column Output packets. I have with Pandas (pd) but Pandas can't read from a text file. So so I tried to create a list and to split it into columns with the " " and "\t" delimiter however that was unsuccessful. In fact, the whole text file comes in single list.
Third I tried to break the first line and keep it constant and then from line 2 below I tried to sort the list but it didn't work.

How can I do this?

import sys
import os
import io
import pandas as pd 
import itertools

token = open ('testfile', 'r')
linestoken = token.readline()
print(linestoken)
for line in itertools.islice(token, 2, 20):
    #print(line)

    def my_sort(line):
        line_fields = line.strip().split(' ')
        #print(line_fields)
        packets = line_fields[4]
        return packets

        temp = list(line)

        temp.sort(key=my_sort)
    #for line in contents:
    #print(line)
        print(temp)
token.close()
python
2 Answers

Your problem has many layers.

  • First you should try to get a better understanding of python itself: how do lists work, what is the significance of indentation and loop, etc.

  • If you call split(' ') each whitespace will be split. Thus a b will become ['a', ' ', 'b'], because there is another whitespace in between a and b... You have to use just split() to get the correct ['a', 'b']. (Or you can also use more complex regexp splits.)

  • You data format is wired and not consistent. Some rows have all the columns, some have the last column missing, but most significantly some rows have internal columns missing. This means, you are NOT sorting for column #4, since in some rows this is just not the case.

If I guess that you data has a fixed-column format, thus, your key is always in character columns 50 to 70, with no exception and no further special cases, then your code could be minimally changed to:


import sys
import os
import io

token = open ('testfile', 'r')
linestoken = token.readline()
linesNL = token.readlines()
lines = [line.rstrip('\n') for line in linesNL]
token.close()

def my_sort(line):
    key = line.strip()[50:70].strip()
    return int(key)

lines.sort(key=my_sort) 
for line in lines: print (line)

But be careful that this requires your data to be flawless. Even an extra empty newline in the last line will break this code.

user_110238210

You can absolutely try with pandas as well, below is my solution..

Solution:

You can use read_fwf module from pandas.io.parsers s follows.

import pandas as pd
import numpy as np

df = pd.read_fwf("textfile")
df = df.rename(columns={'packets': 'Input packet',  'packets.1': 'Output packet', '(pps).1': '(pps)'}).dropna(axis=1, how='all')
df['Output packet'] = df['Output packet'].sort_values(ascending=True).values
df = df.replace(np.nan, '')
print(df)

Explanation:

  1. When you will run df = pd.read_fwf("textfile"), it will produce below output, where you can see it's adding a suffix .1 to the duplicate names in column header such as packet.1 and (pps).1 hence we renamed them to gain the same column names again using rename function.

  2. By using dropna(axis=1, how='all') , we are dropping all the columns having all the Nan values.

  3. Lastly, sort_values(ascending=True).values to get the sorted values for a column Output packet and also remove Nan values.

...

    Interface  Link  Input  packets (pps)  Output  packets.1 (pps).1
0    ge-0/0/0    Up    NaN        0   (0)     NaN          0     (0)
1    gr-0/0/0    Up    NaN        0   (0)     NaN          0     (0)
2    ip-0/0/0    Up    NaN        0   (0)     NaN          0     (0)
3   lsq-0/0/0    Up    NaN        0   (0)     NaN          0     (0)
4    lt-0/0/0    Up    NaN        0   (0)     NaN          0     (0)
5    mt-0/0/0    Up    NaN        0   (0)     NaN        400     (0)
6    sp-0/0/0    Up    NaN        0   (0)     NaN          0     (0)
7    ge-0/0/1    Up    NaN        0   (0)     NaN          0     (0)
8    ge-0/0/2  Down    NaN        0   (0)     NaN          0     (0)
9    ge-0/0/3  Down    NaN        0   (0)     NaN        320     (0)
10   ge-0/0/4  Down    NaN        0   (0)     NaN          0     (0)
11   ge-0/0/5  Down    NaN        0   (0)     NaN          0     (0)
12   ge-0/0/6  Down    NaN        0   (0)     NaN          0     (0)
13   ge-0/0/7  Down    NaN        0   (0)     NaN          0     (0)
14   ge-0/0/8  Down    NaN        0   (0)     NaN          0     (0)
15   ge-0/0/9  Down    NaN        0   (0)     NaN          0     (0)
16       fxp2    Up    NaN        0   NaN     NaN      87559     NaN
17        gre    Up    NaN        0   NaN     NaN          0     NaN
18       ipip    Up    NaN        0   NaN     NaN          0     NaN

Result:

    Interface  Link  Input packet (pps)  Output packet (pps)
0    ge-0/0/0    Up             0   (0)              0   (0)
1    gr-0/0/0    Up             0   (0)              0   (0)
2    ip-0/0/0    Up             0   (0)              0   (0)
3   lsq-0/0/0    Up             0   (0)              0   (0)
4    lt-0/0/0    Up             0   (0)              0   (0)
5    mt-0/0/0    Up             0   (0)              0   (0)
6    sp-0/0/0    Up             0   (0)              0   (0)
7    ge-0/0/1    Up             0   (0)              0   (0)
8    ge-0/0/2  Down             0   (0)              0   (0)
9    ge-0/0/3  Down             0   (0)              0   (0)
10   ge-0/0/4  Down             0   (0)              0   (0)
11   ge-0/0/5  Down             0   (0)              0   (0)
12   ge-0/0/6  Down             0   (0)              0   (0)
13   ge-0/0/7  Down             0   (0)              0   (0)
14   ge-0/0/8  Down             0   (0)              0   (0)
15   ge-0/0/9  Down             0   (0)              0   (0)
16       fxp2    Up             0                  320
17        gre    Up             0                  400
18       ipip    Up             0                87559

Update Based on OP Comment:

df = df.sort_values(by='Output packet', ascending=True)
print(df)

    Interface  Link  Input packet (pps)  Output packet (pps)
0    ge-0/0/0    Up             0   (0)              0   (0)
15   ge-0/0/9  Down             0   (0)              0   (0)
14   ge-0/0/8  Down             0   (0)              0   (0)
13   ge-0/0/7  Down             0   (0)              0   (0)
12   ge-0/0/6  Down             0   (0)              0   (0)
11   ge-0/0/5  Down             0   (0)              0   (0)
10   ge-0/0/4  Down             0   (0)              0   (0)
17        gre    Up             0                    0
18       ipip    Up             0                    0
7    ge-0/0/1    Up             0   (0)              0   (0)
6    sp-0/0/0    Up             0   (0)              0   (0)
4    lt-0/0/0    Up             0   (0)              0   (0)
3   lsq-0/0/0    Up             0   (0)              0   (0)
2    ip-0/0/0    Up             0   (0)              0   (0)
1    gr-0/0/0    Up             0   (0)              0   (0)
8    ge-0/0/2  Down             0   (0)              0   (0)
9    ge-0/0/3  Down             0   (0)            320   (0)
5    mt-0/0/0    Up             0   (0)            400   (0)
16       fxp2    Up             0                87559
user_57048630
Related