from pandas import Series,DataFrame
import pandas as pd

SELECT inet_aton(l.real_ip),
 count(*),
 a.addr
FROM log AS l
INNER JOIN area_ip AS a
 ON a.start_ip_num <= inet_aton(l.real_ip)
 AND a.end_ip_num >= inet_aton(l.real_ip)
GROUP BY real_ip
ORDER BY count(*)
LIMIT 0, 100;

cat pd_ng_log_stat.py
#!/usr/bin/env python
#-*- coding: utf-8 -*-
 
from ng_line_parser import NgLineParser
 
import pandas as pd
import socket
import struct
 
class PDNgLogStat(object):
 
 def __init__(self):
  self.ng_line_parser = NgLineParser()
 
 def _log_line_iter(self, pathes):
  """解析文件中的每一行并生成一个迭代器"""
  for path in pathes:
   with open(path, 'r') as f:
    for index, line in enumerate(f):
     self.ng_line_parser.parse(line)
     yield self.ng_line_parser.to_dict()
 
 def _ip2num(self, ip):
  """用于IP转化为数字"""
  ip_num = -1
  try:
   # 将IP转化成INT/LONG 数字
   ip_num = socket.ntohl(struct.unpack("I",socket.inet_aton(str(ip)))[0])
  except:
   pass
  finally:
   return ip_num
 
 def _get_addr_by_ip(self, ip):
  """通过给的IP获得地址"""
  ip_num = self._ip2num(ip)
 
  try:
   addr_df = self.ip_addr_df[(self.ip_addr_df.ip_start_num <= ip_num) & 
          (ip_num <= self.ip_addr_df.ip_end_num)]
   addr = addr_df.at[addr_df.index.tolist()[0], 'addr']
   return addr
  except:
   return None
      
 def load_data(self, path):
  """通过给的文件路径加载数据生成 DataFrame"""
  self.df = pd.DataFrame(self._log_line_iter(path))
 
 
 def uv_real_ip(self, top = 100):
  """统计cdn ip量"""
  group_by_cols = ['real_ip'] # 需要分组的列,只计算和显示该列
   
  # 直接统计次数
  url_req_grp = self.df[group_by_cols].groupby(
          self.df['real_ip'])
  return url_req_grp.agg(['count'])['real_ip'].nlargest(top, 'count')
   
 def uv_real_ip_addr(self, top = 100):
  """统计real ip 地址量"""
  cnt_df = self.uv_real_ip(top)
 
  # 添加 ip 地址 列
  cnt_df.insert(len(cnt_df.columns),
      'addr',
      cnt_df.index.map(self._get_addr_by_ip))
  return cnt_df
   
 def load_ip_addr(self, path):
  """加载IP"""
  cols = ['id', 'ip_start_num', 'ip_end_num',
    'ip_start', 'ip_end', 'addr', 'operator']
  self.ip_addr_df = pd.read_csv(path, sep='\t', names=cols, index_col='id')
  return self.ip_addr_df
 
def main():
 file_pathes = ['www.ttmark.com.access.log']
 
 pd_ng_log_stat = PDNgLogStat()
 pd_ng_log_stat.load_data(file_pathes)
 
 # 加载 ip 地址
 area_ip_path = 'area_ip.csv'
 pd_ng_log_stat.load_ip_addr(area_ip_path)
 
 # 统计 用户真实 IP 访问量 和 地址
 print pd_ng_log_stat.uv_real_ip_addr()
 
if __name__ == '__main__':
 main()

python pd_ng_log_stat.py
 
     count addr
real_ip      
60.191.123.80 101013 浙江省杭州市
-    32691 None
218.30.118.79 22523  北京市
......
136.243.152.18  889  德国
157.55.39.219  889  美国
66.249.65.170  888  美国
 
[100 rows x 2 columns]