Pandas基础之数据加载与存储

发表于 更新于 分类于 阅读次数：

本文主要介绍Pandass读取各种类型的数据文件，实现数据加载与存储。常见的文件格式主要有：

  1. 文本文件
  2. JSON数据
  3. Excel文件
  4. 使用HTML和WEB API获取的数据
  5. 数据库
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
import pandas as pd
import numpy as np
```

# 文本文件

## 读取文本格式的数据

pandas中的解析函数

| 函数             | 说明                                                         |
| ---------------- | ------------------------------------------------------------ |
| `read_csv`       | 从文件、URL、文件型对象加载带分隔符的数据，默认分隔符为逗号  |
| `read_table`     | 从文件、URL、文件型对象加载带分隔符的数据。默认分隔符为制表符"\t" |
| `read_fwf`       | 读取定宽列格式数据，即：无分隔符                             |
| `read_clipboard` | 读取粘贴板数据                                               |


```python
!cat './examples/ex1.csv'
a,b,c,d,message
1,2,3,4,hello
5,6,7,8,world
9,10,11,12,foo

​

1
2
df = pd.read_csv('./examples/ex1.csv')
df
a b c d message
0 1 2 3 4 hello
1 5 6 7 8 world
2 9 10 11 12 foo 
1
2
pd.read_table('./examples/ex1.csv', sep=',')
df
a b c d message
0 1 2 3 4 hello
1 5 6 7 8 world
2 9 10 11 12 foo
a b c d message
0 1 2 3 4 hello
1 5 6 7 8 world
2 9 10 11 12 foo 
1
!cat './examples/ex2.csv'
1,2,3,4,hello
5,6,7,8,world
9,10,11,12,foo

​

1
2
3
# 数据无标题行
df = pd.read_csv('./examples/ex2.csv',header=None)
df
0 1 2 3 4
0 1 2 3 4 hello
1 5 6 7 8 world
2 9 10 11 12 foo 
1
2
3
# 指定列索引
df = pd.read_csv('./examples/ex2.csv',names=['a', 'b', 'c', 'd', 'message'])
df
a b c d message
0 1 2 3 4 hello
1 5 6 7 8 world
2 9 10 11 12 foo 
1
2
3
# 指定行索引
names = ['a', 'b', 'c', 'd', 'message']
pd.read_csv('./examples/ex2.csv', names=names, index_col='message')
a b c d
message
hello 1 2 3 4
world 5 6 7 8
foo 9 10 11 12 
1
pd.read_csv('./examples/ex2.csv', names=names, index_col=4)
a b c d
message
hello 1 2 3 4
world 5 6 7 8
foo 9 10 11 12 
1
!cat './examples/csv_mindex.csv'
key1,key2,value1,value2
one,a,1,2
one,b,3,4
one,c,5,6
one,d,7,8
two,a,9,10
two,b,11,12
two,c,13,14
two,d,15,16

​

1
2
parsed = pd.read_csv('./examples/csv_mindex.csv', index_col=['key1', 'key2'])
parsed
value1 value2
key1 key2
one a 1 2
b 3 4
c 5 6
d 7 8
two a 9 10
b 11 12
c 13 14
d 15 16 
1
!cat './examples/ex3.txt'
            A         B         C
            aaa -0.264438 -1.026059 -0.619500
            bbb  0.927272  0.302904 -0.032399
            ccc -0.264273 -0.386314 -0.217601
            ddd -0.871858 -0.348382  1.100491

​

1
2
result = pd.read_table('./examples/ex3.txt', sep='\s+')
result
A B C
aaa -0.264438 -1.026059 -0.619500
bbb 0.927272 0.302904 -0.032399
ccc -0.264273 -0.386314 -0.217601
ddd -0.871858 -0.348382 1.100491
  • 默认第一行为标题行，列名比标题行少1，故推断第一列是DataFrame的行索引。
1
!cat './examples/ex4.csv'
# hey!
a,b,c,d,message
# just wanted to make things more difficult for you
# who reads CSV files with computers, anyway?
1,2,3,4,hello
5,6,7,8,world
9,10,11,12,foo

​

1
2
df = pd.read_csv('./examples/ex4.csv', skiprows=[0,2,3])
df
a b c d message
0 1 2 3 4 hello
1 5 6 7 8 world
2 9 10 11 12 foo
  • 默认情况下，pandas会用一组经常出现的标记值进行识别，NA、-1、#IND、NULL等。
  • 也可以用na_values指定一组表示缺失值的字符串。
1
!cat './examples/ex5.csv'
something,a,b,c,d,message
one,1,2,3,4,NA
two,5,6,,8,world
three,9,10,11,12,foo

​

1
2
result=pd.read_csv("./examples/ex5.csv")
result
something a b c d message
0 one 1 2 3.0 4 NaN
1 two 5 6 NaN 8 world
2 three 9 10 11.0 12 foo 
1
result.isnull()
something a b c d message
0 False False False False False True
1 False False False True False False
2 False False False False False False 
1
2
result = pd.read_csv("./examples/ex5.csv", na_values=['NULL',])
result
something a b c d message
0 one 1 2 3.0 4 NaN
1 two 5 6 NaN 8 world
2 three 9 10 11.0 12 foo
  • 也可以用字典为各列指定不同的NA标记值。
1
2
3
!cat './examples/ex5.csv'
sentinels = {'message':['foo', 'NA'], 'something':['two']}
pd.read_csv('./examples/ex5.csv', na_values=sentinels)
something,a,b,c,d,message
one,1,2,3,4,NA
two,5,6,,8,world
three,9,10,11,12,foo

​

something a b c d message
0 one 1 2 3.0 4 NaN
1 NaN 5 6 NaN 8 world
2 three 9 10 11.0 12 NaN

read_csv和read_table函数的参数

参数 说明
path 文件系统位置、URL、文件型对象的字符串
sep或delimiter 用于对列中各字段进行拆分的字符序列或正则表达式
header 用作列名的行号，默认为0，如果没有header行则应设置header=None
index_col 用作行索引的列编号或列名。可以是单个名称/数字，或由多个名称/数字组成的列表（层次化索引）
names 用于结果的列名列表，配合header=None
skiprows 需要跳过的行数（从文件开始算起），或需要跳过行的行号列表（从0开始）
na_values 一组用于替换为NaN的值
parse_dates 尝试将数据解析为日期，默认为False。如果为True，则尝试解析所有列。也可以指定需要解析的一组列号或列名。如果列表的元素为列表或元组，则会将多个列组合到一起在进行日期解析工作。
encoing 用于unicode的文本编码格式。
squeeze 如果数据解析后只有一列，则返回Series
  • 读取大文件时，读取小部分或逐块对文件进行迭代
1
2
result = pd.read_csv("./examples/ex6.csv")
result
one two three four key
0 0.467976 -0.038649 -0.295344 -1.824726 L
1 -0.358893 1.404453 0.704965 -0.200638 B
2 -0.501840 0.659254 -0.421691 -0.057688 G
3 0.204886 1.074134 1.388361 -0.982404 R
4 0.354628 -0.133116 0.283763 -0.837063 Q
... ... ... ... ... ...
9995 2.311896 -0.417070 -1.409599 -0.515821 L
9996 -0.479893 -0.650419 0.745152 -0.646038 E
9997 0.523331 0.787112 0.486066 1.093156 K
9998 -0.362559 0.598894 -1.843201 0.887292 G
9999 -0.096376 -1.012999 -0.657431 -0.573315 0

10000 rows × 5 columns 

1
pd.read_csv('./examples/ex6.csv', nrows=5)
one two three four key
0 0.467976 -0.038649 -0.295344 -1.824726 L
1 -0.358893 1.404453 0.704965 -0.200638 B
2 -0.501840 0.659254 -0.421691 -0.057688 G
3 0.204886 1.074134 1.388361 -0.982404 R
4 0.354628 -0.133116 0.283763 -0.837063 Q

将数据写出到文本格式

用用DataFrame的to_csv函数

1
2
data = pd.read_csv('examples/ex5.csv')
data
something a b c d message
0 one 1 2 3.0 4 NaN
1 two 5 6 NaN 8 world
2 three 9 10 11.0 12 foo 
1
2
data.to_csv('examples/out.csv')   # 写入到以逗号分割的文件中
!cat './examples/out.csv'
,something,a,b,c,d,message
0,one,1,2,3.0,4,
1,two,5,6,,8,world
2,three,9,10,11.0,12,foo

​

1
2
data.to_csv('examples/out2.csv', sep='|')
!cat './examples/out2.csv'
|something|a|b|c|d|message
0|one|1|2|3.0|4|
1|two|5|6||8|world
2|three|9|10|11.0|12|foo

​

  • 缺失值在输出结果中默认会被表示为空字符串，也可以通过na_rep参数表示为别的标记值。
1
2
import sys
data.to_csv(sys.stdout, na_rep='NULL')
,something,a,b,c,d,message

0,one,1,2,3.0,4,NULL

1,two,5,6,NULL,8,world

2,three,9,10,11.0,12,foo
  • 默认会输出行和列的标签，但也可以用index=False和header=False分别禁用行索引和列索引。
  • 还可以只写出部分列，并columns指定顺序。
1
data.to_csv(sys.stdout, index=False, header=False)
one,1,2,3.0,4,

two,5,6,,8,world

three,9,10,11.0,12,foo
1
data.to_csv(sys.stdout, index=False, columns=['a', 'b', 'c'])
a,b,c

1,2,3.0

5,6,

9,10,11.0
1
2
3
4
dates = pd.date_range('1/1/2018', periods=7)
dates
ts = pd.Series(np.arange(7), index=dates)
ts
DatetimeIndex(['2018-01-01', '2018-01-02', '2018-01-03', '2018-01-04',
               '2018-01-05', '2018-01-06', '2018-01-07'],
              dtype='datetime64[ns]', freq='D')
2018-01-01    0
2018-01-02    1
2018-01-03    2
2018-01-04    3
2018-01-05    4
2018-01-06    5
2018-01-07    6
Freq: D, dtype: int32
1
2
ts.to_csv('examples/ts.csv') # Series的输出
!cat './examples/ts.csv'
,0
2018-01-01,0
2018-01-02,1
2018-01-03,2
2018-01-04,3
2018-01-05,4
2018-01-06,5
2018-01-07,6

​

1
pd.read_csv('./examples/ts.csv', parse_dates=True, header=None, index_col=0)
1
0
NaT 0
2018-01-01 0
2018-01-02 1
2018-01-03 2
2018-01-04 3
2018-01-05 4
2018-01-06 5
2018-01-07 6

手工处理分隔符

1
!cat './examples/ex7.csv'
"a","b","c"
"1","2","3"
"4","5","6","7"

​

1
2
# 由于文件行长度不一致，本命令会出错
pd.read_csv('./examples/ex7.csv')
---------------------------------------------------------------------------

ParserError                               Traceback (most recent call last)

<ipython-input-36-9a3d588a75a4> in <module>
----> 1 pd.read_csv('./examples/ex7.csv')


D:\program\Scoop\apps\python\current\Lib\site-packages\pandas\io\parsers.py
in parser_f(filepath_or_buffer, sep, delimiter, header, names,
index_col, usecols, squeeze, prefix, mangle_dupe_cols, dtype, engine,
converters, true_values, false_values, skipinitialspace, skiprows,
skipfooter, nrows, na_values, keep_default_na, na_filter, verbose,
skip_blank_lines, parse_dates, infer_datetime_format, keep_date_col,
date_parser, dayfirst, cache_dates, iterator, chunksize, compression,
thousands, decimal, lineterminator, quotechar, quoting, doublequote,
escapechar, comment, encoding, dialect, error_bad_lines,
warn_bad_lines, delim_whitespace, low_memory, memory_map,
float_precision)
    674         )
    675 
--> 676         return _read(filepath_or_buffer, kwds)
    677 
    678     parser_f.__name__ = name


D:\program\Scoop\apps\python\current\Lib\site-packages\pandas\io\parsers.py
in _read(filepath_or_buffer, kwds)
    452 
    453     try:
--> 454         data = parser.read(nrows)
    455     finally:
    456         parser.close()


D:\program\Scoop\apps\python\current\Lib\site-packages\pandas\io\parsers.py
in read(self, nrows)
   1131     def read(self, nrows=None):
   1132         nrows = _validate_integer("nrows", nrows)
-> 1133         ret = self._engine.read(nrows)
   1134 
   1135         # May alter columns / col_dict


D:\program\Scoop\apps\python\current\Lib\site-packages\pandas\io\parsers.py
in read(self, nrows)
   2035     def read(self, nrows=None):
   2036         try:
-> 2037             data = self._reader.read(nrows)
   2038         except StopIteration:
   2039             if self._first_chunk:


pandas\_libs\parsers.pyx in pandas._libs.parsers.TextReader.read()


pandas\_libs\parsers.pyx in pandas._libs.parsers.TextReader._read_low_memory()


pandas\_libs\parsers.pyx in pandas._libs.parsers.TextReader._read_rows()


pandas\_libs\parsers.pyx in pandas._libs.parsers.TextReader._tokenize_rows()


pandas\_libs\parsers.pyx in pandas._libs.parsers.raise_parser_error()


ParserError: Error tokenizing data. C error: Expected 3 fields in line 3, saw 4

​

1
2
3
4
5
import csv
f = open('examples/ex7.csv')  # 打开文件
reader = csv.reader(f)  # 读取文件
for line in reader:
    print(line)
['a', 'b', 'c']
['1', '2', '3']
['4', '5', '6', '7']

​

1
2
3
lines = list(csv.reader(open('./examples/ex7.csv')))
lines
header, values = lines[0], lines[1:]
[['a', 'b', 'c'], ['1', '2', '3'], ['4', '5', '6', '7']]
1
header
['a', 'b', 'c']
1
values
[['1', '2', '3'], ['4', '5', '6', '7']]
1
list(zip(values,values))
[(['1', '2', '3'], ['1', '2', '3']),
 (['4', '5', '6', '7'], ['4', '5', '6', '7'])]
1
list(zip(values[0],values[1]))
[('1', '4'), ('2', '5'), ('3', '6')]
1
list(zip(*values))
[('1', '4'), ('2', '5'), ('3', '6')]
  • * operator to unpack the arguments out of a list or tuple.For examle:
1
list(range(3, 6))`           # normal call with separate arguments

[3, 4, 5]

1
2
args = [3, 6]
list(range(*args))            # call with arguments unpacked from a list

[3, 4, 5]

1
2
for z in zip(*values):
    print(z)
('1', '4')
('2', '5')
('3', '6')

​

1
2
data_list = [(h,v) for h,v in zip(header, zip(*values))]
data_list
[('a', ('1', '4')), ('b', ('2', '5')), ('c', ('3', '6'))]
1
2
data_dict = {h:v for h,v in zip(header,zip(*values))}
data_dict
{'a': ('1', '4'), 'b': ('2', '5'), 'c': ('3', '6')}
1
2
df = pd.DataFrame(data_dict)
df
a b c
0 1 2 3
1 4 5 6

JSON数据

  • JSON（JavaScript Object Notation）,通过HTTP请求在web浏览器和其他应用程序直接按发送数据的标准格式之一。比表格型文本格式（如CSV）更灵活。
  • Python标准库中内置了json库，通过json.loads可以将JSON字符串转换成Python形式。

Python标准库读取

1
2
3
4
5
6
obj = """
{"name":"Wes",
"places_lived":["United States", "Spain", "Germany"],
"pet":null,
"siblings":[{"names":"Scott", "age":25, "pet":"Zuko"},{"name":"Katie", "age":33, "pet":"Cisco"}]
}"""
1
import json
1
2
3
4
5
6
obj = """
{"name":"Wes",
"places_lived":["United States", "Spain", "Germany"],
"pet":null,
"siblings":[{"name":"Scott", "age":25, "pet":"Zuko"},{"name":"Katie", "age":33, "pet":"Cisco"}]
}"""
1
2
result = json.loads(obj)
result
{'name': 'Wes',
 'places_lived': ['United States', 'Spain', 'Germany'],
 'pet': None,
 'siblings': [{'name': 'Scott', 'age': 25, 'pet': 'Zuko'},
  {'name': 'Katie', 'age': 33, 'pet': 'Cisco'}]}
1
result['name']
'Wes'
1
result['siblings']
[{'name': 'Scott', 'age': 25, 'pet': 'Zuko'},
 {'name': 'Katie', 'age': 33, 'pet': 'Cisco'}]
1

1
2
siblings = pd.DataFrame(result['siblings'])
siblings
name age pet
0 Scott 25 Zuko
1 Katie 33 Cisco

Pandas读取

  • pandas 已有read_json和to_json函数，具体看查阅相关函数帮助。其主要参数orient可用于指定读入数据的格式：
参数 说明
split dict like {index -> [index], columns -> [columns], data -> [values]}
records list like [{column -> value}, ... , {column -> value}]
index dict like {index -> {column -> value}}
columns dict like {column -> {index -> value}}
values just the values array 
1
2
3
df = pd.DataFrame([['a', 'b'], ['c', 'd']],
                  index=['row 1', 'row 2'],
                  columns=['col 1', 'col 2'])
1
df.to_json('./examples/json_split', orient="split")
1
!cat ./examples/json_split
{"columns":[0,1,2],"index":["a","b","c"],"data":[[1,4,7],[2,5,8],[3,6,9]]}

​

1
df.to_json(orient='records')
'[{"col 1":"a","col 2":"b"},{"col 1":"c","col 2":"d"}]'
1
df.to_json(orient='index')
'{"row 1":{"col 1":"a","col 2":"b"},"row 2":{"col 1":"c","col 2":"d"}}'
1
df.to_json(orient='columns')
'{"col 1":{"row 1":"a","row 2":"c"},"col 2":{"row 1":"b","row 2":"d"}}'
1
df.to_json(orient='values')
'[["a","b"],["c","d"]]'
1
!cat './examples/test.json'
{"columns":[0,1,2],"index":["a","b","c"],"data":[[1,4,7],[2,5,8],[3,6,9]]}

​

1
2
df = pd.read_json('./examples/test.json', orient='split')
df
0 1 2
a 1 4 7
b 2 5 8
c 3 6 9

Excel数据

读取Excle数据

  • pandas读取Excel文件用到了xlrd和openpyxl包，因此必须事先安装。
  • pandas的read_excel函数读取文件返回DataFrame
1
pd.read_excel('./examples/ex1.xlsx')
Unnamed: 0 a b c d message
0 0 1 2 3 4 hello
1 1 5 6 7 8 world
2 2 9 10 11 12 foo 
1
2
df = pd.read_excel('./examples/ch7.xlsx', sheet_name=1)
df
teacher_id teacher_name
0 1 zhangsan
1 2 lisi
2 3 wangwu
3 4 zhaoliu
  • 也可以直接用xlrd包读取excel文件。
1
import xlrd
1
2
3
4
5
book = xlrd.open_workbook('./examples/projectdata.xlsx')
for i in range(book.nsheets):
    table = book.sheets()[i]
    data = [table.cell(row, col).value for (row, col) in ((2,2),(2,6),(4,2))]
    print(data)

['野店村', '野店镇', 2195.0] ['焦坡村', '野店镇', 1497.0] ['东山村', '野店镇', 623.0] ['朱家坡', '野店镇', 1150.0] ['苏家沟', '野店镇', 444.0] ['烟庄', '野店镇', 1081.0] ['毛坪村', '野店镇', 1365.0] ['伊家圈', '野店镇', 809.0] ['石泉', '野店镇', 1638.0] ['新庄村', '野店镇', 1100.0] ['南峪村', '野店镇', 994.0] ['南晏子', '野店镇', 994.0] ['北坪', '野店镇', 955.0] ['棋盘石', '野店镇', 1743.0] ['安平崮', '野店镇', 1601.0] ['东坪', '野店镇', 1357.0] ['上东门', '野店镇', 1791.0] ['板崮崖', '野店镇', 1975.0] ['桑子峪', '野店镇', 1300.0] ['梭庄', '野店镇', 1013.0] ['石槽', '野店镇', 895.0] ['黄崖', '野店镇', 791.0] ['大山', '野店镇', 1117.0] ['大石头', '野店镇', 1027.0] ['黄土良', '野店镇', 854.0] ['演马庄', '野店镇', 1186.0] ['北晏子', '野店镇', 766.0] ['朝阳', '野店镇', 473.0] ['寨后万', '野店镇', 468.0] ['立新', '野店镇', 521.0] ['白杨峪', '野店镇', 666.0] ['郭庄', '野店镇', 732.0] ['翻金峪', '野店镇', 539.0] ['西梭庄', '野店镇', 288.0] ['马头崖', '野店镇', 351.0] ['对景峪', '野店镇', 253.0] ['石仁坡', '野店镇', 275.0]

​

写入Excel文件

1
2
3
df1 = pd.DataFrame([['a', 'b'], ['c', 'd']],
                   index=['row 1', 'row 2'],
                   columns=['col 1', 'col 2']) 
1
df1.to_excel("./examples/output.xlsx") 
1
2
3
4
df2 = df1.copy()
with pd.ExcelWriter('./examples/output2.xlsx') as writer:  
    df1.to_excel(writer, sheet_name='Sheet_name_1')
    df2.to_excel(writer, sheet_name='Sheet_name_2')

使用HTML和Web API获取数据

许多网站提供通过JSON或其他格式提供数据的公共API，通过Python访问API，譬如request包

1
import requests
1
2
3
4
5
6
url = 'http://api.map.baidu.com/geocoder?'
params = {}
params['output'] = 'json'
params['address'] = '上海海事大学'
params['ak'] = 'WS4aniHIt4ITPIVnbwfvN54DynG00a7T'
params
{'output': 'json',
 'address': '上海海事大学',
 'ak': 'WS4aniHIt4ITPIVnbwfvN54DynG00a7T'}
1
resp = requests.get(url, params=params)
1
resp
<Response [200]>
1
2
data = json.loads(resp.text)
data
{'status': 'OK',
 'result': {'location': {'lng': 116.413384, 'lat': 39.910925},
  'precise': 0,
  'confidence': 20,
  'level': '城市'}}
1
2
df = pd.DataFrame(data['result']['location'], index=['SMU'])
df
lng lat
SMU 116.413384 39.910925

数据库

将数据从SQL加载到DataFrame

1
import sqlite3
1
conn = sqlite3.connect('./examples/data.sqlite')
1
2
3
cursor = conn.execute("select Name, Town, Pop, Lat, Lng from village")
rows = cursor.fetchall()
rows

[('下东门村', '坦埠镇', 2045, 35.852457, 118.189404), ('东坦埠村', '坦埠镇', 1853, 35.801192, 118.228538), ('东崖村', '坦埠镇', 1302, 35.824206, 118.215981), ('东河南村', '坦埠镇', 1063, 35.789275, 118.234346), ('东西崖村', '坦埠镇', 2120, 35.841863, 118.202611), ('代家庄村', '坦埠镇', 1181, 35.74744, 118.036742), ('南极山村', '坦埠镇', 325, 35.843734, 118.187794), ('响水庄村', '坦埠镇', 550, 35.812858, 118.206505), ('官庄村', '坦埠镇', 1220, 35.74744, 118.036742), ('张家庄村', '坦埠镇', 1768, 35.822732, 118.13797), ('故县村', '坦埠镇', 1859, 35.802529, 118.207691), ('来石万村', '坦埠镇', 515, 35.812489, 118.194266), ('来石庄村', '坦埠镇', 701, 35.806575, 118.20942), ('水明崖村', '坦埠镇', 1060, 35.789972, 118.181779), ('沙沟村', '坦埠镇', 1062, 35.784318, 118.207658), ('海龙万村', '坦埠镇', 329, 35.820998, 118.166729), ('潘庄村', '坦埠镇', 1169, 35.78692, 118.195158), ('砚池万村', '坦埠镇', 518, 35.829046, 118.180634), ('西坦埠村', '坦埠镇', 1518, 35.80605, 118.227207), ('西崖村', '坦埠镇', 1337, 35.836513, 118.198651), ('西河南村', '坦埠镇', 1142, 35.789267, 118.222858), ('诸夏村', '坦埠镇', 2045, 35.786453, 118.16225), ('蹇家庄村', '坦埠镇', 678, 35.821792, 118.203438), ('鑫中山村', '坦埠镇', 362, 35.802783, 118.189009), ('寨后村', '坦埠镇', 516, 35.799194, 118.158137), ('张林村', '坦埠镇', 989, 35.80348, 118.165427), ('石龙庄村', '坦埠镇', 426, 35.793162, 118.165548), ('艾山前村', '坦埠镇', 656, 35.797019, 118.19505), ('阚家庄村', '坦埠镇', 993, 35.827922, 118.15255), ('黄家庄村', '坦埠镇', 1052, 35.836872, 118.181114), ('黄家洼村', '坦埠镇', 334, 35.826161, 118.119698), ('龙虎寨村', '坦埠镇', 965, 35.780729, 118.195224), ('红柞崖村', '岱崮', 652, 36.010041, 118.191358), ('贾庄村', '岱崮', 2216, 35.998015, 118.132512), ('万杨峪村', '岱崮', 822, 35.74744, 118.036742), ('先头峪村', '岱崮', 796, 35.982208, 118.085294), ('井旺庄', '岱崮', 1827, 36.000409, 118.087207), ('五里沟', '岱崮', 1205, 35.89437, 117.997182), ('王家峪村', '岱崮', 373, 36.030869, 118.129848), ('杨宝泉村', '岱崮', 840, 36.006901, 118.162394), ('八亩地村', '岱崮', 1579, 35.999194, 118.171979), ('河东村', '岱崮', 1296, 35.912506, 118.155096), ('冶子河村', '岱崮', 1134, 35.902247, 118.173999), ('下旺村', '岱崮', 2273, 35.895178, 118.194262), ('尹家洼村', '岱崮', 1650, 35.881766, 118.229427), ('杜家坡', '岱崮', 868, 35.87774, 118.218526), ('马子石沟', '岱崮', 868, 35.880192, 118.197051), ('柳树头村', '岱崮', 1571, 35.890074, 118.213183), ('大朱家庄', '岱崮', 2066, 35.904369, 118.222296), ('西上峪村', '岱崮', 564, 35.935611, 118.219236), ('东上峪村', '岱崮', 699, 35.93708, 118.225169), ('黑土洼村', '岱崮', 540, 35.74744, 118.036742), ('石门峪村', '岱崮', 711, 35.921427, 118.223561), ('大岭村', '岱崮', 1135, 35.918697, 118.211585), ('尖洼村', '岱崮', 894, 35.925554, 118.209711), ('蒋家庄', '岱崮', 2314, 35.926491, 118.196411), ('公家庄', '岱崮', 1153, 35.843532, 118.025282), ('笊篱坪村', '岱崮', 952, 35.939071, 118.149433), ('梁家场村', '岱崮', 344, 35.951018, 118.11926), ('岱崮村', '岱崮', 996, 35.974475, 118.105563), ('上茶局峪村', '岱崮', 364, 35.984201, 118.129621), ('茶局峪村', '岱崮', 1774, 35.975941, 118.138449), ('台头村', '岱崮', 2124, 35.97329, 118.159674), ('燕窝村', '岱崮', 1068, 35.963639, 118.176176), ('丁家庄', '岱崮', 1524, 35.970627, 118.188269), ('核桃万村', '岱崮', 772, 35.941694, 118.166578), ('坡里', '岱崮', 2973, 35.94733, 118.191423), ('犁掩沟村', '岱崮', 846, 35.99056, 118.207398), ('东峪村', '岱崮', 2047, 35.958452, 118.19862), ('东指村', '岱崮', 417, 35.986775, 118.192981), ('上旺村', '岱崮', 1099, 35.991594, 118.184672), ('丁家庄', '岱崮', 1524, 35.970627, 118.188269), ('大崮村', '岱崮', 1503, 35.922014, 118.159084), ('十字涧村', '岱崮', 1776, 35.949667, 118.140878), ('板古泉村', '岱崮', 2124, 35.74744, 118.036742), ('上峪村', '旧寨乡', 725, 35.072409, 118.340768), ('上河村', '旧寨乡', 1187, 35.812068, 118.061077), ('东彭吴村', '旧寨乡', 930, 35.760208, 118.143571), ('东里庄村', '旧寨乡', 1062, 35.779252, 118.068459), ('九峪子村', '旧寨乡', 319, 35.772879, 118.138525), ('书堂村', '旧寨乡', 632, 35.81283, 118.031386), ('于家岭村', '旧寨乡', 918, 35.751769, 118.163973), ('八里庙子村', '旧寨乡', 1103, 35.761812, 118.125906), ('刘家上峪村', '旧寨乡', 604, 35.768266, 118.090707), ('北峪村', '旧寨乡', 887, 35.763524, 118.110015), ('北楼村', '旧寨乡', 1949, 35.785858, 118.138986), ('北莫村', '旧寨乡', 1121, 35.74744, 118.036742), ('南莫庄', '旧寨乡', 1030, 35.791653, 118.045649), ('卧龙湾村', '旧寨乡', 602, 35.78381, 118.016804), ('双河村', '旧寨乡', 1127, 35.74744, 118.036742), ('双福村', '旧寨乡', 738, 35.74744, 118.036742), ('向阳峪村', '旧寨乡', 841, 35.796676, 118.143435), ('吕家庄子村', '旧寨乡', 1524, 35.778026, 118.117436), ('大上峪村', '旧寨乡', 2136, 35.790827, 118.098933), ('大洼村', '旧寨乡', 436, 35.603391, 117.910959), ('常坪村', '旧寨乡', 675, 35.798432, 118.025276), ('庙后村', '旧寨乡', 224, 35.775571, 118.144326), ('康山村', '旧寨乡', 562, 35.760525, 118.177926), ('旧寨村', '旧寨乡', 1610, 35.751824, 118.108143), ('晏子峪村', '旧寨乡', 376, 35.772186, 118.057282), ('李家宅子村', '旧寨乡', 716, 35.76936, 118.133206), ('杏山子村', '旧寨乡', 1178, 35.795318, 118.130631), ('杨家林子村', '旧寨乡', 503, 35.756101, 118.0583), ('殷家岭村', '旧寨乡', 1012, 35.76342, 118.06932), ('沈家庄', '旧寨乡', 641, 35.785586, 118.061932), ('王家庄子村', '旧寨乡', 655, 35.739515, 118.143367), ('禹家庄子村', '旧寨乡', 606, 35.753398, 118.150244), ('莲汪崖村', '旧寨乡', 1198, 35.763639, 118.083551), ('蒲峪村', '旧寨乡', 500, 35.797177, 118.014627), ('西彭吴村', '旧寨乡', 1563, 35.754485, 118.12809), ('西里庄村', '旧寨乡', 1280, 35.777757, 118.046605), ('长岭村', '旧寨乡', 619, 35.788439, 118.08592), ('马家庄子村', '旧寨乡', 478, 35.751724, 118.124347), ('马家良村', '旧寨乡', 874, 35.743729, 118.127049), ('高家庄子村', '旧寨乡', 881, 35.74744, 118.036742), ('龙山村', '旧寨乡', 512, 35.772882, 118.176527), ('龙汪峪村', '旧寨乡', 529, 35.74744, 118.036742), ('龚家庄子村', '旧寨乡', 726, 35.766859, 118.166296), ('孙麻社区', '桃墟镇', 2036, 35.74744, 118.036742), ('宝德居委会', '开发区', 2693, 35.697188, 117.974349), ('新城', '开发区', 674, 35.74744, 118.036742), ('太保庄', '开发区', 1898, 35.691557, 117.987268), ('徐家沟', '开发区', 1105, 35.639092, 117.915499), ('李家保德村', '开发区', 872, 35.722928, 117.989085), ('店子村', '开发区', 896, 35.720292, 117.980137), ('向阳庄村', '开发区', 460, 35.706542, 117.993674), ('白杨铺村', '开发区', 672, 35.709872, 118.00468), ('联合庄村', '开发区', 1078, 35.689056, 118.015745), ('岳庄', '开发区', 1503, 35.676823, 118.026786), ('刘官庄', '开发区', 2872, 35.711753, 118.024191), ('曹庄', '开发区', 1503, 35.704069, 118.012471), ('乔家庄', '联城镇', 943, 35.678917, 117.914599), ('任家城子', '联城镇', 293, 35.6919, 117.866626), ('刘家官庄', '联城镇', 1200, 35.628112, 117.894389), ('刘庄', '联城镇', 843, 35.650152, 118.07871), ('南鲁村', '联城镇', 851, 35.74744, 118.036742), ('台上村', '联城镇', 558, 35.720582, 117.809087), ('和恬村', '联城镇', 885, 35.74744, 118.036742), ('堂子村', '联城镇', 1173, 35.711465, 117.82133), ('大城子村', '联城镇', 656, 35.700338, 117.870623), ('大庄', '联城镇', 1476, 35.645863, 117.890043), ('大王洼家村', '联城镇', 786, 35.74744, 118.036742), ('大宋家城子村', '联城镇', 495, 35.698305, 117.866052), ('宋家榛子崖村', '联城镇', 1042, 35.694631, 117.787907), ('对山庄', '联城镇', 664, 35.74744, 118.036742), ('小山口村', '联城镇', 437, 35.727739, 117.855498), ('小庄', '联城镇', 596, 35.657082, 117.892325), ('山南村', '联城镇', 1156, 35.687809, 117.894552), ('崔家城子村', '联城镇', 885, 35.704328, 117.869783), ('布洼村', '联城镇', 1675, 35.727501, 117.78653), ('常岭村', '联城镇', 1026, 35.650234, 117.847307), ('常马庄', '联城镇', 1205, 35.68132, 117.809538), ('张家村', '联城镇', 527, 35.685096, 117.830754), ('东西崖村', '联城镇', 660, 35.841863, 118.202611), ('李家北山', '联城镇', 563, 35.71589, 117.869144), ('杨家庄', '联城镇', 1210, 35.810801, 117.958905), ('桃花峪村', '联城镇', 638, 35.706062, 117.855726), ('沙沟峪村', '联城镇', 1552, 35.712733, 117.804048), ('牛头峪村', '联城镇', 502, 35.713326, 117.788659), ('王去峪村', '联城镇', 807, 35.722998, 117.830882), ('王家洼村', '联城镇', 768, 35.74744, 118.036742), ('相家庄子', '联城镇', 796, 35.734183, 117.828801), ('禹家城子', '联城镇', 516, 35.709803, 117.872653), ('类家城子村', '联城镇', 1222, 35.712689, 117.886034), ('聚来庄村', '联城镇', 635, 35.681386, 117.853378), ('花峪村委会', '联城镇', 410, 35.74744, 118.036742), ('董家台村委会', '联城镇', 411, 35.74744, 118.036742), ('虎路坡村', '联城镇', 1097, 35.638297, 117.874371), ('许家沟', '联城镇', 463, 35.685639, 117.818567), ('郭家场', '高都镇', 1256, 35.74744, 118.036742), ('钓鱼台村', '联城镇', 747, 35.669546, 117.886672), ('东西崖村', '联城镇', 672, 35.841863, 118.202611), ('青山村', '联城镇', 1133, 35.74744, 118.036742), ('鸿喜庄村', '联城镇', 1317, 35.74744, 118.036742), ('龙榜崖村', '联城镇', 1154, 35.687887, 117.841692), ('蒙阴街道', '蒙阴街道', '', 35.74744, 118.036742), ('上五庄村', '高都镇', 1933, 35.875311, 117.980309), ('上温村', '高都镇', 1711, 35.867118, 117.991366), ('下温村', '高都镇', 951, 35.855217, 117.989643), ('上坦埠林村', '高都镇', 513, 35.846165, 117.989548), ('下坦埠林村委会', '高都镇', 643, 35.74744, 118.036742), ('汇泉坪村', '高都镇', 543, 35.843026, 118.000809), ('眼光泉村', '高都镇', 222, 35.834244, 117.986448), ('邓家崖村', '高都镇', 704, 35.821388, 118.017279), ('石星沟村', '高都镇', 388, 35.8094, 118.011149), ('唐家峪村', '高都镇', 1024, 35.84494, 117.952603), ('下薛家峪村', '高都镇', 1250, 35.857803, 117.94018), ('上薛家峪村', '高都镇', 633, 35.74744, 118.036742), ('黄泥沟村', '高都镇', 380, 35.858061, 117.949015), ('黑石山村', '高都镇', 419, 35.872042, 117.962492), ('三山子村', '高都镇', 436, 35.864846, 117.968456), ('庙子后村', '高都镇', 366, 35.851937, 117.967894), ('高都村', '高都镇', 2455, 35.83186, 117.945624), ('河西村', '高都镇', 1369, 35.837887, 117.93976), ('西峪村', '高都镇', 501, 35.831109, 117.931095), ('西山村', '高都镇', 532, 35.842604, 117.928678), ('南坪村', '高都镇', 635, 35.821706, 117.934605), ('包龙沟村', '高都镇', 536, 35.813933, 117.936611), ('东沟村', '高都镇', 456, 35.824295, 117.970858), ('东坡村', '高都镇', 1271, 35.821474, 117.920255), ('小孙官庄村', '高都镇', 1407, 35.824301, 117.909578), ('大孙官庄村', '高都镇', 1435, 35.81482, 117.905057), ('洪沟村', '高都镇', 1478, 35.803974, 117.911641), ('李官庄村', '高都镇', 804, 35.796984, 117.895168), ('郑家庄村', '高都镇', 1685, 35.79048, 117.893682), ('西住佛村', '高都镇', 1437, 35.781666, 117.886638), ('后佛峪村', '高都镇', 962, 35.791492, 117.906746), ('蔡庄村', '高都镇', 1883, 35.828102, 118.003576), ('新星村', '高都镇', 731, 35.74744, 118.036742), ('野店村', '野店镇', 2195, 35.877685, 118.10241), ('焦坡村', '野店镇', 1497, 35.880439, 118.126699), ('东山村', '野店镇', 623, 35.914668, 118.102758), ('朱家坡', '野店镇', 1150, 35.906729, 118.086829), ('苏家沟', '野店镇', 444, 35.898097, 118.07988), ('烟庄', '野店镇', 1081, 35.877183, 118.026699), ('毛坪村', '野店镇', 1365, 35.889508, 118.015263), ('伊家圈', '野店镇', 809, 35.872367, 118.07799), ('石泉', '野店镇', 1638, 35.887257, 118.044913), ('新庄村', '野店镇', 1100, 35.874192, 118.055733), ('南峪村', '野店镇', 994, 35.863728, 118.022558), ('南晏子', '野店镇', 994, 35.847071, 118.067624), ('北坪', '野店镇', 955, 35.833707, 118.071337), ('棋盘石', '野店镇', 1743, 35.873716, 118.176459), ('安平崮', '野店镇', 1601, 35.885223, 118.164692), ('东坪', '野店镇', 1357, 35.880027, 118.144061), ('上东门', '野店镇', 1791, 35.867798, 118.164627), ('板崮崖', '野店镇', 1975, 35.857609, 118.140303), ('桑子峪', '野店镇', 1300, 35.838863, 118.121616), ('梭庄', '野店镇', 1013, 35.928776, 118.100586), ('石槽', '野店镇', 895, 35.935257, 118.053487), ('黄崖', '野店镇', 791, 35.939523, 118.030022), ('大山', '野店镇', 1117, 35.915253, 118.041416), ('大石头', '野店镇', 1027, 35.912876, 118.064246), ('黄土良', '野店镇', 854, 35.926078, 118.076015), ('演马庄', '野店镇', 1186, 35.897781, 118.111121), ('北晏子', '野店镇', 766, 35.859884, 118.063622), ('朝阳', '野店镇', 473, 35.83079, 118.048857), ('寨后万', '野店镇', 468, 35.833584, 118.02734), ('立新', '野店镇', 521, 35.855882, 118.046792), ('白杨峪', '野店镇', 666, 35.954118, 118.070659), ('郭庄', '野店镇', 732, 35.944072, 118.080331), ('翻金峪', '野店镇', 539, 35.956199, 118.084083), ('西梭庄', '野店镇', 288, 35.934126, 118.088294), ('马头崖', '野店镇', 351, 35.936568, 118.099446), ('对景峪', '野店镇', 253, 35.96539, 118.056732), ('石仁坡', '野店镇', 275, 35.74744, 118.036742)]

1
2
# This read-only property returns a list of tuples describing the columns in a result set. 
cursor.description
(('Name', None, None, None, None, None, None),
 ('Town', None, None, None, None, None, None),
 ('Pop', None, None, None, None, None, None),
 ('Lat', None, None, None, None, None, None),
 ('Lng', None, None, None, None, None, None))
1
list(zip(*cursor.description))[0]
('Name', 'Town', 'Pop', 'Lat', 'Lng')
1
pd.DataFrame(rows, columns=list(zip(*cursor.description))[0])
Name Town Pop Lat Lng
0 下东门村 坦埠镇 2045 35.852457 118.189404
1 东坦埠村 坦埠镇 1853 35.801192 118.228538
2 东崖村 坦埠镇 1302 35.824206 118.215981
3 东河南村 坦埠镇 1063 35.789275 118.234346
4 东西崖村 坦埠镇 2120 35.841863 118.202611
... ... ... ... ... ...
241 翻金峪 野店镇 539 35.956199 118.084083
242 西梭庄 野店镇 288 35.934126 118.088294
243 马头崖 野店镇 351 35.936568 118.099446
244 对景峪 野店镇 253 35.965390 118.056732
245 石仁坡 野店镇 275 35.747440 118.036742

246 rows × 5 columns