How to convert all IP address lists to decimal numbers in Pandas

Question

There is huge list data, how to convert all IP addresses to decimal numbers in pandas and merge with the value of the second column

   import pandas as pd
   filename = "/Users/sda/Desktop/file"
   pdd = pd.read_csv(filename,header=None,sep='|',error_bad_lines=False, warn_bad_lines=False,skiprows=[0],
              names=['Name','Code','Ipv', 'Ip','Range','Date', 'Category'])
   pd.options.display.max_columns = None
   k = pdd[pdd['Ipv'].str.contains("ipv4") & pdd['Ip'].str.contains('[0-9]')]
   print(k[['Ip','Range','Code']])

my output:

        Ip         Range    Code
     2.16.0.0      524288   EU
     200.109.100.0 1024     RU
     200.109.102.0 1024     RU

only need to get the decimal value of the first IP address with the same country code and range number.merge with the value of the second column only

       IP         range code
    3362612224    2028  RU

anky · Accepted Answer · 2019-01-20 06:34:53Z

1

IIUC, Considering the dataframe name of the output is df, something like this:

import socket, struct

def ip2int(ip):
    """
    Convert an IP string to int
    """
    packedIP = socket.inet_aton(ip)
    return struct.unpack("!L", packedIP)[0]

df['ip_int'] = df.Ip.apply(ip2int)
df['range_sum']=df.groupby(['Code'])['Range'].transform('sum')
df[df.Code.duplicated(keep='last')]

               Ip  Range Code      ip_int  range_sum
 1  200.109.100.0   1024   RU  3362612224       2048

edited Jan 20, 2019 at 6:34

answered Jan 20, 2019 at 6:19

anky

75.3k11 gold badges46 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

warezers Over a year ago

use this function under this line? k = pdd [pdd ['Ipv'] .str.contains ("ipv4") & pdd ['Ip']. str.contains ('[0 - 9]')]

anky Over a year ago

@warezers this is after you have the output. df = k[['Ip','Range','Code']]

warezers Over a year ago

got some error: Try using .loc[row_indexer,col_indexer] = value instead See the caveats in the documentation: pandas.pydata.org/pandas-docs/stable/… k['range_sum']=k.groupby(['Code'])['Range'].transform('sum')

anky Over a year ago

@warezers that is because k is a slice try assigning k to a dataframe , df = k[['Ip','Range','Code']] , also before that execute this line pd.options.mode.chained_assignment = None after that use the code. :)

warezers Over a year ago

Now it's not an error and if I get the output of this value only? # df[df.Code.duplicated(keep='last')]) print(df[['ip_int','range_sum','Code']])

|

Collectives™ on Stack Overflow

How to convert all IP address lists to decimal numbers in Pandas

1 Answer 1

8 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related