pandas库使用

读取常见文件

import pandas as pd
import pymysql


def pd_read_csv(path):
    df = pd.read_csv(path)
    print(df.to_string())
    print(df.head())
    print(df.head(10))
    print(df.tail())
    print(df.tail(10))


def pd_read_excel(path):
    df = pd.read_excel(path)
    print(df.to_string())


def pd_read_sql():
    conn = pymysql.connect(host='localhost', user='root', password='root', database='bfl', port=3306)
    df = pd.read_sql('select * from bfl_attr', con=conn)
    print(df.to_string())


if __name__ == '__main__':
    pd_read_csv('./assets/test.csv')
    # pd_read_excel('./assets/test.xlsx')
    # pd_read_sql()
    pass

数据类型解释

import pandas as pd

'''
DataFrame:二维数据，整个表格，多行多列
Series:一维数据，一行或者一列
例1:DataFrame
                            name        age          sex            --> DataFrame.columns

                0           john        13           male
                1           lili        15           female
                2           mike        13           male
                3           Bob         77           male
                |
                ↓
                DataFrame.index    

例2:
                             ----         ---  
                            |name|       |age|           sex         
                ______________________________________________            
               |0           |john|       |13 |           male |     --> Series
                ----------------------------------------------
                1           |lili|       |15 |           female
                2           |mike|       |13 |           male
                3           |Bob |       |77 |           male
                             ----         ---  
                             |              |
                             ↓              ↓
                             Series         Series
'''


def pd_series():
    df = pd.Series([1, 'a', 5.2, 7])
    print(df)
    '''
        左侧为索引，右侧是数据
        0      1
        1      a
        2    5.2
        3      7
        dtype: object
    '''
    print(df.index)  # 获取数据索引
    print(df.values)  # 获取数据


# 创建一个具有标签索引的Series
def pd_series2():
    df = pd.Series([1, 'a', 5.2, 7], index=['a', 'b', 'c', 'd'])
    print(df)


# 通过字典建立Series
def pd_series3():
    s_data = {'a': 123, 'b': 456, 'c': 789, 'd': 'askdj'}
    df = pd.Series(s_data)
    print(df)
    print(df['a'])  # 单值通过字典的方式查询
    print(df[['a', 'b']])  # 多值通过[[]]方式查询，返回的也是一个Series


def pd_dataframe():
    data = {
        'name': ['john', 'lili', 'mike', 'Bob'],
        'age': [13, 15, 13, 17],
        'gender': ['male', 'female', 'male', 'male']
    }
    df = pd.DataFrame(data)
    print(df)
    print(df.dtypes)  # 列类型
    print(df.columns)  # 列索引
    print(df.index)  # 行索引

    # 从DataFrame中查询时，如果是查询的是一行或者一列，返回的Series，如果是多行或者多列返回的还是DataFrame

    # 查询列
    print(df['name'])
    print(type(df['name']))  # 返回的Series

    print(df[['name', 'age']])  # 多值通过[[]]方式查询
    print(type(df[['name', 'age']]))  # 返回的DataFrame

    # 查询行，loc相当于两个值[[行（单个或区间时省略方括号）],[列（单个或区间时省略方括号）]]
    print(df.loc[0])
    print(type(df.loc[0]))  # 返回的Series

    print(df.loc[0:2])
    print(type(df.loc[0:2]))  # 返回的DataFrame




if __name__ == '__main__':
    # pd_series()
    # pd_series2()
    # pd_series3()
    pd_dataframe()
    pass

查询和添加

import pandas as pd


def search():
    df = pd.read_csv('./assets/test.csv')
    # 此时已经将索引切换到了’name‘这一栏上
    df.set_index('name', inplace=True)

    # # 单条件查询，loc的参数用[]包裹，[索引的值,查询的值]
    # # 查询的是单个条件返回一个值
    # print(df.loc['Bob', 'age'])
    # # 多条件查询，loc的参数用[]包裹，[[索引的值list],[查询的值list]]
    # # 查询的是多个条件返回DataFrame
    # print(df.loc['john', ['age', 'gender']])
    # print(df.loc[['john', 'Bob'], ['age', 'gender']])

    # # 分区查询，loc[索引值的参数区间(不能重复), 查询值的区间(不能重复)]
    # print(df.loc['john':'mike', 'age':'gender'])

    # # 条件查询
    # print((df['age'] > 13) & (df['age'] < 24))

    # 函数查询
    print(df.loc[lambda df: (df['age'] > 13) & (df['age'] < 24), :])


def add():
    df = pd.read_csv('./assets/test.csv')
    # # 增加一列 after10year 为10年后的年龄
    # df.loc[:, 'after10year'] = df['age'] + 10
    # print(df)

    # 通过apply增加列，传入一个方法。axis=1指列。=0指行。
    df.loc[:, 'isAdult'] = df.apply(lambda x: '成年' if x['age'] >= 18 else '未成年', axis=1)
    print(df['isAdult'].value_counts())  # 查看计数
    print(df)


if __name__ == '__main__':
    # search()
    add()

统计和去重

import pandas as pd


def describe():
    df = pd.read_csv('./assets/test.csv')
    print(df.describe())  # 统计csv的平均值、数量、标准差、最大最小值等
    print(df['age'].max())


def unique():
    df = pd.read_csv('./assets/test.csv')
    print(df['age'].unique())  # 去重，可以看到一列中有哪些值


if __name__ == '__main__':
    unique()

数据清洗

import pandas as pd


def clean():
    df = pd.read_csv('./assets/test2.csv')
    # print(df)
    # print(df.isnull())  # 检测空值

    # axis指定删除'columns' 或者 'index', how指定删除方式，'all'全部为空才删除，'any'只要有空就删除， inplace指代是否替换df
    df.dropna(axis='columns', how='all', inplace=True)
    print(df)

    # 空置填充，如此处，age为空的地方用18填充
    # df.fillna({'age': 18}, inplace=True)
    # print(df)

    # 设置method='ffill'，空置处用上一个有的值填充
    df.fillna(method='ffill', inplace=True)
    print(df)

    # 生成清洗后的csv文件，设置index=False可以去除默认的索引
    df.to_csv('./assets/test2_clean.csv', index=False)


if __name__ == '__main__':
    clean()

数据排序

import pandas as pd


def pd_sort():
    df = pd.read_csv('./assets/test.csv')
    print(df)

    # 根据age降序排序
    df.sort_values(by='age', inplace=True, ascending=False)
    print(df)

    df.sort_values(by=['gender', 'age'], inplace=True, ascending=[True, False])
    print(df)


if __name__ == '__main__':
    pd_sort()

文件遍历

import pandas as pd
import time


def traverse(path):
    df = pd.read_csv(path)
    for index, row in df.iterrows():
        # print(index)
        # print(row['name'])
        # print(row['age'])
        # print(row['gender'])
        pass


def traverse2(path):
    df = pd.read_csv(path)
    for row in df.itertuples():
        # print(row.name)
        # print(row.age)
        # print(row.gender)
        # # 或者
        # print(getattr(row, 'name'))
        # print(getattr(row, 'age'))
        # print(getattr(row, 'gender'))
        pass


# 通过列遍历
def traverse3(path):
    df = pd.read_csv(path)
    for item in df.iteritems():
        # print(item)
        pass


# 此方法遍历速度最快，推荐使用
def traverse4(path):
    df = pd.read_csv(path)
    for index in df.index:
        # print(df.loc[index]['name'])
        pass


def use_time(func, path):
    times = time.time()
    for i in range(50):
        func(path)
    res = (time.time() - times) / 50
    print(res)


if __name__ == '__main__':
    file_path = './assets/mfs.csv'
    use_time(traverse2, file_path)
    use_time(traverse3, file_path)
    use_time(traverse4, file_path)

未完待续…