Baru tapi bersemangat tentang Python dan saya butuh saran Anda. Saya datang dengan kode berikut untuk membandingkan dua file CSV berdasarkan nmap scan:

import pandas as pd
from pandas import DataFrame
import os
file = raw_input('\nEnter the Old CSV file: ')
file1 = raw_input('\nEnter the New CSV file: ')
A=set(pd.read_csv(file, index_col=False, header=None)[0])
B=set(pd.read_csv(file1, index_col=False, header=None)[0])
final=list(A-B)
df = pd.DataFrame(final, columns=["host"])
df.to_csv('DIFF_'+file)

print "Completed!"

Ketika saya menjalankannya saya mendapat hasil sebagai berikut: ,

host
0,82.214.228.71;dsl-radius-02.direcpceu.com;PTR;tcp;111;rpcbind;open;;;syn-ack;;3;
1,82.214.228.70;dsl-radius-01.direcpceu.com;PTR;tcp;111;rpcbind;open;;;syn-ack;;3;

Pertanyaan saya adalah bagaimana cara menambahkan label/enter code herenama pada kolom 2,3 dll misalnya: hostanme , port , port name , state dll. Saya telah mencoba : df['hostname'] = range(1, len(df) + 1) tetapi ini menambahkan nama host pada kolom pertama bersama dengan Host ketika saya membuka file dengan Excel

2
Ivan Madolev 14 Agustus 2017, 13:47

2 jawaban

Jawaban Terbaik

Saya rasa Anda perlu read_csv dengan parameter sep=',' dan names untuk menentukan nama kolom terlebih dahulu:

file = raw_input('\nEnter the Old CSV file: ')
file1 = raw_input('\nEnter the New CSV file: ')

cols = ['hostname','port','portname', ...]
A= pd.read_csv(file, index_col=False, header=None, sep=';', names=cols)
B= pd.read_csv(file1, index_col=False, header=None, sep=';', names=cols)

Kemudian gunakan merge dengan membandingkan dengan boolean indexing jika perlu membandingkan semua kolom:

df = pd.merge(A, B, how='outer', indicator=True)
df = df[df['_merge']=='left_only'].drop('_merge',axis=1)

df.to_csv('DIFF_'+file)

print "Completed!"

Contoh:

import pandas as pd
from pandas.compat import StringIO

temp=u"""82.214.228.71;dsl-radius-02.direcpceu.com;PTR;tcp;111;rpcbind;open;;;syn-ack;;3;
82.214.228.70;dsl-radius-01.direcpceu.com;PTR;tcp;111;rpcbind;open;;;syn-ack;;3;
82.214.228.74;dsl-radius-02.direcpceu.com;PTR;tcp;111;rpcbind;open;;;syn-ack;;3;
82.214.228.75;dsl-radius-01.direcpceu.com;PTR;tcp;111;rpcbind;open;;;syn-ack;;3;"""
#after testing replace 'StringIO(temp)' to 'filename.csv'
cols = ['hostname','port','portname', 'a','b','c','d','e','f','g','h','i', 'j']
A = pd.read_csv(StringIO(temp), sep=";", names=cols)
print (A)
        hostname                         port portname    a    b        c  \
0  82.214.228.71  dsl-radius-02.direcpceu.com      PTR  tcp  111  rpcbind   
1  82.214.228.70  dsl-radius-01.direcpceu.com      PTR  tcp  111  rpcbind   
2  82.214.228.74  dsl-radius-02.direcpceu.com      PTR  tcp  111  rpcbind   
3  82.214.228.75  dsl-radius-01.direcpceu.com      PTR  tcp  111  rpcbind   

      d   e   f        g   h  i   j  
0  open NaN NaN  syn-ack NaN  3 NaN  
1  open NaN NaN  syn-ack NaN  3 NaN  
2  open NaN NaN  syn-ack NaN  3 NaN  
3  open NaN NaN  syn-ack NaN  3 NaN  

temp=u"""82.214.228.75;dsl-radius-02.direcpceu.com;PTR;tcp;111;rpcbind;open;;;syn-ack;;3;
82.214.228.70;dsl-radius-01.direcpceu.com;PTR;tcp;111;rpcbind;open;;;syn-ack;;3;
82.214.228.77;dsl-radius-02.direcpceu.com;PTR;tcp;111;rpcbind;open;;;syn-ack;;3;
"""
#after testing replace 'StringIO(temp)' to 'filename.csv'
cols = ['hostname','port','portname', 'a','b','c','d','e','f','g','h','i', 'j']
B = pd.read_csv(StringIO(temp), sep=";", names=cols)
print (B)
        hostname                         port portname    a    b        c  \
0  82.214.228.75  dsl-radius-02.direcpceu.com      PTR  tcp  111  rpcbind   
1  82.214.228.70  dsl-radius-01.direcpceu.com      PTR  tcp  111  rpcbind   
2  82.214.228.77  dsl-radius-02.direcpceu.com      PTR  tcp  111  rpcbind   

      d   e   f        g   h  i   j  
0  open NaN NaN  syn-ack NaN  3 NaN  
1  open NaN NaN  syn-ack NaN  3 NaN  
2  open NaN NaN  syn-ack NaN  3 NaN 

df1 = pd.merge(A, B, how='outer', indicator=True)

print (df1)

        hostname                         port portname    a    b        c  \
0  82.214.228.71  dsl-radius-02.direcpceu.com      PTR  tcp  111  rpcbind   
1  82.214.228.70  dsl-radius-01.direcpceu.com      PTR  tcp  111  rpcbind   
2  82.214.228.74  dsl-radius-02.direcpceu.com      PTR  tcp  111  rpcbind   
3  82.214.228.75  dsl-radius-01.direcpceu.com      PTR  tcp  111  rpcbind   
4  82.214.228.75  dsl-radius-02.direcpceu.com      PTR  tcp  111  rpcbind   
5  82.214.228.77  dsl-radius-02.direcpceu.com      PTR  tcp  111  rpcbind   

      d   e   f        g   h  i   j      _merge  
0  open NaN NaN  syn-ack NaN  3 NaN   left_only  
1  open NaN NaN  syn-ack NaN  3 NaN        both  
2  open NaN NaN  syn-ack NaN  3 NaN   left_only  
3  open NaN NaN  syn-ack NaN  3 NaN   left_only  
4  open NaN NaN  syn-ack NaN  3 NaN  right_only  
5  open NaN NaN  syn-ack NaN  3 NaN  right_only  
#only values in A
df1 = df1[df1['_merge']=='left_only'].drop('_merge',axis=1)
print (df1)
        hostname                         port portname    a    b        c  \
0  82.214.228.71  dsl-radius-02.direcpceu.com      PTR  tcp  111  rpcbind   
2  82.214.228.74  dsl-radius-02.direcpceu.com      PTR  tcp  111  rpcbind   
3  82.214.228.75  dsl-radius-01.direcpceu.com      PTR  tcp  111  rpcbind   

      d   e   f        g   h  i   j  
0  open NaN NaN  syn-ack NaN  3 NaN  
2  open NaN NaN  syn-ack NaN  3 NaN  
3  open NaN NaN  syn-ack NaN  3 NaN
#only values in B
df1 = pd.merge(A, B, how='outer', indicator=True)
df11 = df1[df1['_merge']=='right_only'].drop('_merge',axis=1)
print (df11)
        hostname                         port portname    a    b        c  \
4  82.214.228.75  dsl-radius-02.direcpceu.com      PTR  tcp  111  rpcbind   
5  82.214.228.77  dsl-radius-02.direcpceu.com      PTR  tcp  111  rpcbind   

      d   e   f        g   h  i   j  
4  open NaN NaN  syn-ack NaN  3 NaN  
5  open NaN NaN  syn-ack NaN  3 NaN 
#same values in both dataframes
df12 = df1[df1['_merge']=='both'].drop('_merge',axis=1)
print (df12)
        hostname                         port portname    a    b        c  \
1  82.214.228.70  dsl-radius-01.direcpceu.com      PTR  tcp  111  rpcbind   

      d   e   f        g   h  i   j  
1  open NaN NaN  syn-ack NaN  3 NaN  

Tetapi jika perlu membandingkan hanya kolom pertama hostname gunakan isin untuk mask, ~ untuk inverting dengan boolean indexing:

df2 = A[~A['hostname'].isin(B['hostname'])]
print (df2)
        hostname                         port portname    a    b        c  \
0  82.214.228.71  dsl-radius-02.direcpceu.com      PTR  tcp  111  rpcbind   
2  82.214.228.74  dsl-radius-02.direcpceu.com      PTR  tcp  111  rpcbind   

      d   e   f        g   h  i   j  
0  open NaN NaN  syn-ack NaN  3 NaN  
2  open NaN NaN  syn-ack NaN  3 NaN  
3
jezrael 14 Agustus 2017, 11:27

Anda dapat menambahkan label tempat Anda mendefinisikan kerangka data. Misalnya, yang berikut ini harus berfungsi:

df = pd.DataFrame(final, columns=["host"].append([x for x in range(1, len(df) + 1)] ))
1
Amit Verma 14 Agustus 2017, 10:54