pandas操作数据库

1.pandas需要配合sqlalchemy的使用

import pandas as pd
from sqlalchemy import create_engine

engine = create_engine("mysql+pymysql://%s:%s@%s:3306/%s?charset=utf8" % (TEST_DB.user, TEST_DB.password, TEST_DB.host, TEST_DB.db))

exec_sql = ''
source_table = pd.read_sql(exec_sql, engine)

2.遍历数据

# index代表索引，从0开始
# v代表数据库中的数据
for index,v in source_table.iterrows():
        print(index, v)



# 精确取出数据，需要注意的是，取出的数据值，是一个series类型数据，不是string，需要string()后才可使用split
v["字段名称"]即可

3.更改数据

# 使用iloc方法，
source_table.iloc[index,2] = int(result)

>>> df = pd.DataFrame(mydict)
        >>> df
              a     b     c     d
        0     1     2     3     4
        1   100   200   300   400
        2  1000  2000  3000  4000

        **Indexing just the rows**

        With a scalar integer.

        >>> type(df.iloc[0])
        <class 'pandas.core.series.Series'>
        >>> df.iloc[0]
        a    1
        b    2
        c    3
        d    4
        Name: 0, dtype: int64

        With a list of integers.

        >>> df.iloc[[0]]
           a  b  c  d
        0  1  2  3  4
        >>> type(df.iloc[[0]])
        <class 'pandas.core.frame.DataFrame'>

        >>> df.iloc[[0, 1]]
             a    b    c    d
        0    1    2    3    4
        1  100  200  300  400

        With a `slice` object.

        >>> df.iloc[:3]
              a     b     c     d
        0     1     2     3     4
        1   100   200   300   400
        2  1000  2000  3000  4000

        With a boolean mask the same length as the index.

        >>> df.iloc[[True, False, True]]
              a     b     c     d
        0     1     2     3     4
        2  1000  2000  3000  4000

        With a callable, useful in method chains. The `x` passed
        to the ``lambda`` is the DataFrame being sliced. This selects
        the rows whose index label even.

        >>> df.iloc[lambda x: x.index % 2 == 0]
              a     b     c     d
        0     1     2     3     4
        2  1000  2000  3000  4000

        **Indexing both axes**

        You can mix the indexer types for the index and columns. Use ``:`` to
        select the entire axis.

        With scalar integers.

        >>> df.iloc[0, 1]
        2

        With lists of integers.

        >>> df.iloc[[0, 2], [1, 3]]
              b     d
        0     2     4
        2  2000  4000

        With `slice` objects.

        >>> df.iloc[1:3, 0:3]
              a     b     c
        1   100   200   300
        2  1000  2000  3000

        With a boolean array whose length matches the columns.

        >>> df.iloc[:, [True, False, True, False]]
              a     c
        0     1     3
        1   100   300
        2  1000  3000

        With a callable function that expects the Series or DataFrame.

        >>> df.iloc[:, lambda df: [0, 2]]
              a     c
        0     1     3
        1   100   300
        2  1000  3000
        """

4.删除字段

# axis=1代表列
values_table = source_table.drop('字段名', axis=1)

5.数据库更新

.to_sql()更新数据时，con必须使用"sqlalchemy"，如果使用pymysql会报错

 6.选择某些列

import pandas as pd

# 从Excel中读取数据，生成DataFrame数据
# 导入Excel路径和sheet name
df = pd.read_excel(excelName, sheet_name=sheetName)

# 读取某些列，生成新的DataFrame
newDf = pd.DataFrame(df, columns=[column1, column2, column3])

7.读取某些列，并根据某个列的值筛选行

newDf = pd.DataFrame(df, columns=[column1, column2, column3])[(df.column1 == value1) & (df.column2 == value2)]

8.添加新的列

# 第一种直接赋值
df["newColumn"] = newValue

# 第二种用concat组合两个DataFrame
pd.concat([oldDf, newDf])

9.更改某一列的值

# 第一种，replace
df["column1"] = df["column1"].replace(oldValue, newValue)

# 第二种，map
df["column1"] = df["column1"].map({oldValue: newValue})

# 第三种，loc
# 将column2 中某些行（通过column1中的value1来过滤出来的）的值为value2
df.loc[df["column1"] == value1, "column2"] = value2

10.填充缺失值

# fillna填充缺失值
df["column1"] = df["column1"].fillna(value1)

11.过滤出某些列

Examples

df = pd.DataFrame(np.array(([1, 2, 3], [4, 5, 6])),
                  index=['mouse', 'rabbit'],
                  columns=['one', 'two', 'three'])
df
        one  two  three
mouse     1    2      3
rabbit    4    5      6
# select columns by name
df.filter(items=['one', 'three'])
         one  three
mouse     1      3
rabbit    4      6
# select columns by regular expression
df.filter(regex='e$', axis=1)
         one  three
mouse     1      3
rabbit    4      6
# select rows containing 'bbi'
df.filter(like='bbi', axis=0)
         one  two  three
rabbit    4    5      6

12.mean()用法

Pandas Series.mean()函数返回给定Series对象中基础数据的平均值。

用法： Series.mean(axis=None, skipna=None, level=None, numeric_only=None, **kwargs)

参数：
axis:要应用的功能的轴。
skipna:计算结果时排除NA /null值。
level:如果轴是MultiIndex(分层)，则沿特定级别计数，并折叠成标量。
numeric_only:仅包括float，int，boolean列。
**kwargs:要传递给函数的其他关键字参数。

返回：均值：标量或系列(如果指定级别)

# 求和，求平均：

import pandas as pd
student = pd.read_excel("C:/Users/Administrator/Desktop/Students.xlsx",index_col="ID")
temp = student[["Test_1","Test_2","Test_3"]]
student["total"] = temp.sum(axis=1)#axis 0为列，1为行
student["avg"] = temp.mean(axis=1)
print(student)

#算各科成绩平均，求和：

col_mean = student[["Test_1","Test_2","Test_3","total","avg"]].mean()
col_mean["Name"]="Summary"
student = student.append(col_mean,ignore_index=True)
student[["Test_1","Test_2","Test_3","total","avg"]] = student[["Test_1","Test_2","Test_3","total","avg"]].astype(int)
print(student)

转自https://www.cnblogs.com/jiangxinyang/p/9672785.html

转自https://blog.csdn.net/glittledream/article/details/87902161