import pandas as pd
import numpy as np
# 读取数据，并以表格的形式显示
df1 = pd.DataFrame(pd.read_excel('a.xlsx'))
# 判断是否为空，每个表格都会去判断，并返回True/False
a1 = df1.isnull().values
"""
[[False False False False False False False False False]
[False False True False False False False False False]
[False False False False False False False False False]
[False False False False False False False False False]
[False False False False False True False False False]
[False False False False False False False False False]
[False False False False False False False False False]]
"""
# 判断是否为非空，每个表格都会去判断，并返回True/False，会以规则的表格形式返回
a2 = df1.notnull()
"""
1 2 3 4 5 6 open_acc weight data
0 True True True True True True True True True
1 True True False True True True True True True
2 True True True True True True True True True
3 True True True True True True True True True
4 True True True True True False True True True
5 True True True True True True True True True
6 True True True True True True True True True
"""
# 判断列是否重复,比对每一行，并返回True/False
b = df1.duplicated()
"""
0 False
1 False
2 False
3 False
4 False
5 True
6 False
dtype: bool
"""
# 返回去掉重复行后的表
c = df1.drop_duplicates()
"""
1 2 3 4 5 6 open_acc weight data
0 12 12 23 34 56 67 2 12kg 2018/02/26
1 asd asd NaN aas qwe qwe 10 23000g 2018/02/6
2 123 qw q q qwe qwe 7 45000mg 2018/5/26
3 1 2 3 4 5 6 15 123t 2017/9
4 1 2 3 4 5 NaN 20 456t 18/2/2
6 12 123 4 3 er 32 12 34g 2015/3/01
"""
# 空值以。。。。方式填充
d1 = df1.fillna('\\')
"""
1 2 3 4 5 6 open_acc weight data
0 12 12 23 34 56 67 2 12kg 2018/02/26
1 asd asd \ aas qwe qwe 10 23000g 2018/02/6
2 123 qw q q qwe qwe 7 45000mg 2018/5/26
3 1 2 3 4 5 6 15 123t 2017/9
4 1 2 3 4 5 \ 20 456t 18/2/2
5 1 2 3 4 5 6 15 123t 2017/9
6 12 123 4 3 er 32 12 34g 2015/3/01
"""
# 包含空值的行删除
d2 = df1.dropna()
"""
1 2 3 4 5 6 open_acc weight data
0 12 12 23 34 56 67 2 12kg 2018/02/26
2 123 qw q q qwe qwe 7 45000mg 2018/5/26
3 1 2 3 4 5 6 15 123t 2017/9
5 1 2 3 4 5 6 15 123t 2017/9
6 12 123 4 3 er 32 12 34g 2015/3/01
"""
# 给表更换新的列名，注意字段列表的长度一定要和列的长度相同
column_names = ['a', 'b', 'c', 'd', 'e', 'f','g','h','i']
df = pd.read_excel('a.xlsx', names=column_names)
"""
a b c d e f g h i
0 12 12 23 34 56 67 2 12kg 2018/02/26
1 asd asd NaN aas qwe qwe 10 23000g 2018/02/6
2 123 qw q q qwe qwe 7 45000mg 2018/5/26
3 1 2 3 4 5 6 15 123t 2017/9
4 1 2 3 4 5 NaN 20 456t 18/2/2
5 1 2 3 4 5 6 15 123t 2017/9
6 12 123 4 3 er 32 12 34g 2015/3/01
"""
# 打印第一列
# print(df1[1])
"""
0 12
1 asd
2 123
3 1
4 1
5 1
6 12
Name: 1, dtype: object
"""
# 将列中的12，1替换成'qwe'
a = df1[1].replace([12, 1], 'qwe')
"""
0 qwe
1 qwe
2 123
3 qwe
4 qwe
5 qwe
6 qwe
Name: 1, dtype: object
"""
# 打印数据的前n行，默认为5
# print(df1.head())
# 给数据分组
"""
0-5 A 不包含0
6-10 B
11-15 C
16-20 D
"""
bins = [0, 5, 10, 15, 20]
group_names = ['A', 'B', 'C', 'D']
df1['categories'] = pd.cut(df1['open_acc'], bins, labels=group_names)
"""
1 2 3 4 ... open_acc weight data categories
0 12 12 23 34 ... 2 12kg 2018/02/26 A
1 1 asd NaN aas ... 10 23000g 2018/02/6 B
2 123 qw q q ... 7 45000mg 2018/5/26 B
3 1 2 3 4 ... 15 123t 2017/9 C
4 1 2 3 4 ... 20 456t 18/2/2 D
5 1 2 3 4 ... 15 123t 2017/9 C
6 12 123 4 3 ... 12 34g 2015/3/01 C
"""
# 获取到需要换算单位的行
rows_with_lbs = df1['weight'].str.contains('mg').fillna(False)
"""
1 2 3 4 5 6 open_acc weight data categories
2 123 qw q q qwe qwe 7 45000mg 2018/5/26 B
"""
# 换算单位
for i, lbs_row in df1[rows_with_lbs].iterrows():
# print(i) # 获取到值的行
# print(lbs_row['weight'][-2:])
weight = int(float(lbs_row['weight'][:-2]) / 1000)
df1.at[i, 'weight'] = '{}g'.format(weight)
# print(df1)
"""
1 2 3 4 ... open_acc weight data categories
0 12 12 23 34 ... 2 12kg 2018/02/26 A
1 1 asd NaN aas ... 10 23000g 2018/02/6 B
2 123 qw q q ... 7 45g 2018/5/26 B
3 1 2 3 4 ... 15 123t 2017/9 C
4 1 2 3 4 ... 20 456t 18/2/2 D
5 1 2 3 4 ... 15 123t 2017/9 C
6 12 123 4 3 ... 12 34g 2015/3/01 C
"""
# rows_with_lbs = df1['weight'].str.contains('t').fillna(False)
# for i,lbs_row in df1[rows_with_lbs].iterrows():
# # print(i) # 获取到值的行
# # print(lbs_row['weight'][:-2])
# weight = int(float(lbs_row['weight'][:-2]) * 10000000)
# df1.at[i, 'weight'] = '{}g'.format(weight)
# print(df1)
# 换算单位封装
def conversion(DataFrame, column, unit, num, new_unit='', null=False):
"""
:param DataFrame: pandas对象
:param column: 字段
:param unit: 当前单位
:param new_unit: 新单位
:param num: 当前单位换算成新单位所需要乘的数
:param null: 是否为空
:return:pandas对象
1 2 3 4 ... open_acc weight data categories
0 12 12 23 34 ... 2 1.20e+04g 2018/02/26 A
1 asd asd NaN aas ... 10 23g 2018/02/6 B
2 123 qw q q ... 7 45g 2018/5/26 B
3 1 2 3 4 ... 15 1.23e+08g 2017/9 C
4 1 2 3 4 ... 20 4.56e+08g 18/2/2 D
"""
rows_with_lbs = DataFrame[column].str.contains(unit).fillna(null)
column_len = len(unit)
for i, lbs_row in DataFrame[rows_with_lbs].iterrows():
# print(lbs_row['weight'][-column_len:])
weight = int(float(lbs_row[column][:-column_len]) * num)
# 默认4位，超出长度变为科学计数法
if len(str(weight)) > 4:
DataFrame.at[i, column] = ('%0.2e' + new_unit) % weight
"""
1 2 3 4 ... open_acc weight data categories
0 12 12 23 34 ... 2 1.20e+04g 2018/02/26 A
1 asd asd NaN aas ... 10 2.30e+01g 2018/02/6 B
2 123 qw q q ... 7 4.50e+01g 2018/5/26 B
3 1 2 3 4 ... 15 1.23e+08g 2017/9 C
4 1 2 3 4 ... 20 4.56e+08g 18/2/2 D
"""
else:
DataFrame.at[i, column] = str(weight) + new_unit
"""
1 2 3 4 ... open_acc weight data categories
0 12 12 23 34 ... 2 12000g 2018/02/26 A
1 asd asd NaN aas ... 10 23g 2018/02/6 B
2 123 qw q q ... 7 45g 2018/5/26 B
3 1 2 3 4 ... 15 123000000g 2017/9 C
4 1 2 3 4 ... 20 456000000g 18/2/2 D
"""
return DataFrame
df1 = conversion(df1, 'weight', 'kg', 1000, 'g') # kg - g
df2 = conversion(df1, 'weight', 'mg', 0.001, 'g') # mg -g
df3 = conversion(df2, 'weight', 't', 1000000, 'g') # t - g
print(df3)
