pandas【常用】读写增删查改

import pandas as pd
df = pd.DataFrame({'A': ['aaa', 'bb', 'c'],
                   'B': [10000, 2000, 300],
                   'C': [0.001, 0.02, 0.3]})
print(df)

---

读文件

读csv

• header=None设置首行不变成header
• names设置列名字

def csv2df(fname, names=None):
    return pd.read_csv(fname, names=names, header=None)

读txt

• sep设置分隔符，jieba词典分隔符是空格

def txt2df(fname, sep=' ', names=None):
    return pd.read_table(fname, sep, names=names, header=None)

• delimiter设置分隔符

np.loadtxt(fname, dtype=float, delimiter=None)

读excel

def xlsx2df(fname, sheet_name=0):
    return pd.read_excel(fname, sheet_name=sheet_name)

读数据库

read_sql_query

import pandas as pd, pymysql
def db_to_csv(db_name, query, file):
    db = pymysql.connect('localhost', 'root', 'password', charset='utf8', db=db_name)
    df =pd.read_sql_query(query, db)
    df.to_csv(file, index=False)
db_to_csv('库名', 'SELECT * FROM 表;', '文件名.csv')

---

写文件

写csv

df.to_csv(fname, index=False)

写excel

• 单个sheet

df.to_excel(fname, index=False)

• 多个sheet

def df2sheets(ls_of_df, sheet_names, fname='temporary'):
    fname = fname.replace('.xlsx', '') + '.xlsx'
    excel_writer = pd.ExcelWriter(fname)
    for df, sheet_name in zip(ls_of_df, sheet_names):
        df.to_excel(excel_writer, sheet_name, index=False)
    excel_writer.save()

---

对象类型转换

dict→DataFrame

dt = {'A': ['a', 'b', 'c'],
      'B': [100, 200, 300]}
print(pd.DataFrame(dt))

ndarray→DataFrame

• 嵌套列表或numpy.ndarray均可

ls_of_ls = [['a', 100],
            ['b', 200],
            ['c', 300]]
print(pd.DataFrame(ls_of_ls, columns=['A', 'B']))

DataFrame→dict

def df2dt(df):
    if df.shape[1] == 2:
        return dict(df.values)
    elif df.shape[1] > 2:
        return {i[0]: i[1:] for i in df.values}

DataFrame→ndarray

print(df.values, type(df.values))

DataFrame→Series

print(df.A, type(df.A))  # A列
print(df[df.columns[0]])  # 第0列
# print(df.loc[2])  # index=2的行
# print(df.iloc[2])  # 第2+1行

---

查

列筛选

print(df[['A', 'C']])
print(df[df.columns[0:2]])  # 第0~1列

行筛选

print(df[df['B'] > 300])
print(df[(df['B'] > 300) & (df['C'] > 0.01)])
print(df[df['A'].isin(['bb', 'c'])])

定位

print(df.loc[df['A'] == 'c', ['B']])
print(df.loc[df['A'].isin(['bb', 'c']), ('A', 'C')])

---

增

列新增

df['len'] = df.A.str.len()  # 尾列，列名‘len’，值为A列字符串长度
df.insert(0, 'D', 99)  # 第0列，列名‘D’，值99
print(df)

行新增

pandas.concat

df1 = pd.DataFrame({'A': ['d'], 'B': [400], 'C': [0.4]})
df = pd.concat([df, df1])
print(df)

---

删

函数	作用
drop	普通删除
drop_duplicates	去重
dropna	丢弃缺失值

---

改

df.loc[df['A'] == 'c', ['A', 'C']] = ('cc', 33)
print(df)

排序

按age列降序

df.sort_values(by='age', ascending=False, inplace=True)