python数据分析之数据接口

# 数据接口

#csv文件
#读取方式1
import pandas as pd
df = pd.read_csv("names.csv")
print(df)
"""
  first_name last_name
0      Baked     Beans
1     Lovely      Spam
2  ["a",1,5]      Spam
"""
#指定index为某列
df = pd.read_csv("names.csv", index_col="last_name")
print(df)
"""
          first_name
last_name           
Beans          Baked
Spam          Lovely
Spam       ["a",1,5]
"""
#记录数据统计
print(len(df))

#读取前几行数据
df = pd.read_csv("names.csv", index_col="last_name", nrows=2)   #nrows=2:只显示前2行
print(df)

#如果没有表头，就会默认把第一行设置为表头，所需要特殊处理
"""
源数据
1,2,3,5
5,0,1,2
8,9,6,5
1,2,2,0
"""
df = pd.read_csv("test1.csv", header=None)
print(df)
"""
   0  1  2  3
0  1  2  3  5
1  5  0  1  2
2  8  9  6  5
3  1  2  2  0
"""
# 对于没有表头的清空，可以设置表头
names = list("ABCD")
df = pd.read_csv("test1.csv", header=None, names=names)
print(df)
"""
   A  B  C  D
0  1  2  3  5
1  5  0  1  2
2  8  9  6  5
3  1  2  2  0
"""
#读取方式2
df = pd.read_table("names.csv",sep=",") #每列数据用逗号隔开
print(df)

"""
  first_name last_name
0      Baked     Beans
1     Lovely      Spam
2  ["a",1,5]      Spam
"""
#写入csv
df.to_csv("tt.csv", encoding="utf8")


#EXCEL
#读取方式1
df = pd.read_excel("blog_20201023102414.xls",index_col="分类")
print(df.head())

#读取方式2
# pip install openpyxl
xls_file = pd.ExcelFile("blog_20201023102414.xls")
xls_data = xls_file.parse("blog", index_col="分类")
print(xls_data)

#写入excel
#  pip install xlwt
xls_data.to_excel("test.xls")


#操作数据库
#SQLite:python自带的数据库
import sqlite3
#创建表
create_sql = """create table test (
    sid varchar(10),
    sname varchar(20),
    gender varchar(2),
    age integer
)

"""
#创建数据库
con = sqlite3.connect(":memory:")       #表示使用特定文件名“:memory:”，在内存中创建数据库
con.execute(create_sql)
con.commit()
#查询
cursor = con.execute("select * from test")
data = cursor.fetchall()
print(data) #[]
#插入数据
data = [("202001","张三1","男",19),
        ("202002","张三2","男",20),
        ("202003","张三3","男",23)]

stmt = "insert into test values (?,?,?,?)"
con.executemany(stmt, data)
con.commit()

#再次查询
cursor = con.execute("select * from test")
data = cursor.fetchall()
print(data) #[]
#转换为数据框
import pandas.io.sql as sql
df = sql.read_sql_query("select * from test", con)
print(df)

#PyMysql