创建虚拟数据

faker库
image.png

  1. import pandas as pd
  2. from faker import Faker
  3. import random as rd
  5. fake = Faker("zh-CN")
  7. # 要输入到DF中的数据字典
  8. dic = {}
  10. # 姓名
  11. list_name = []
  12. # 随机手机号
  13. list_phone_number = []
  14. # 随机国家
  15. list_country = []
  16. # 随机邮件
  17. list_email = []
  18. # 随机车牌号
  19. list_car_number = []
  20. # 随机年份，格式2022-02-22
  21. list_date = []
  22. # 随机字符串，长度在min-max之间
  23. list_str =[]
  24. # 随机年龄
  25. list_age = []
  26. # 随机金额
  27. list_money = []
  28. # 索引
  29. list_index = []
  30. for i in range(0,1000):
  31.     list_index.append(i)
  32.     list_name.append(fake.name())
  33.     list_phone_number.append(fake.phone_number())
  34.     list_country.append(fake.country())
  35.     list_email.append(fake.email())
  36.     list_car_number.append(fake.license_plate())
  37.     list_date.append(fake.date_time(tzinfo=None, end_datetime=None))
  38.     list_str.append(fake.pystr(min_chars=0, max_chars=8))
  39.     list_age.append(rd.randint(15,65))
  40.     list_money.append(rd.random()*9999)
  42. dic['Index'] = list_index
  43. dic['Name'] = list_name
  44. dic['Age'] = list_age
  45. dic['Phone'] = list_phone_number
  46. dic['Country'] = list_country
  47. dic['Email'] = list_email
  48. dic['CarNumber'] = list_car_number
  49. dic['Date'] = list_date
  50. dic['Note'] = list_str
  51. dic['Money'] = list_money
  53. df = pd.DataFrame(dic)
  54. print(df.columns)
  55. df.to_excel('./TestExcel/AlbertData.xlsx')

创建文件

  1. import pandas as pd
  3. # DataFrame-->Worksheet
  4. # 初始化数据
  5. df = pd.DataFrame({'ID': [1, 2, 3, 4, 5, 6], 'Name': ['Tim', 'Victor', 'Nick', 'Timthony', 'Yangzhongke', 'Zack']})
  6. # 设置ID为索引，而不是默认的ID 0 1 2
  7. df = df.set_index('ID')
  8. print(df.columns)
  9. print(df)
  10. excelPath = './TestExcel/output.xlsx'  # 使用相对路径
  11. df.to_excel(excelPath)
  12. print('Create excel successfully!')

读取文件

  1. import pandas as pd
  3. excelPath = './TestExcel/output.xlsx'  # 使用相对路径
  4. outExcel = pd.read_excel(excelPath, index_col='ID')
  5. # outExcel = pd.read_excel(excelPath, header=1)  # 从第1行开始作为标题
  6. # 如果excel里面没有header，但是我们知道如何操作？下面两行
  7. # 操作将Name列显示为了SubName
  8. outExcel = pd.read_excel(excelPath, header=None)
  9. outExcel.columns = ['ID', 'SubName']
  10. # 将修改好的数据SubName写到新的文件中
  11. outExcel.set_index('ID', inplace=True)
  12. outExcel.to_excel('./TestExcel/output002.xlsx')
  13. print('Done')
  14. print(outExcel.shape)  # 显示出这个表有几行几列
  15. print(outExcel.columns)  # 显示出所有列名称
  16. print(f'我是文件头两行数据:\n{outExcel.head(2)}')  # 查看前2行
  17. print(f'我是文件尾三行数据:\n{outExcel.tail(3)}')  # 后三行

行，列，单元格

  1. import pandas as pd
  3. # dic = {'x': 100, 'y': 200, 'z': 300}
  4. # s1 = pd.Series(dic)
  5. # 下面这种方式是一样的效果
  6. # index是行号 name是列号 [100,200,300]是行内容
  7. # s1 = pd.Series([100, 200, 300], index=['x', 'y', 'z'])
  8. s1 = pd.Series([1, 2, 3], index=[1, 2, 3], name='A')  # 一行数据
  9. s2 = pd.Series([10, 20, 30], index=[1, 2, 3], name='B')
  10. s3 = pd.Series([100, 200, 300], index=[2, 3, 4], name='C')
  11. # 以Dictionary的形式将Series加入到DataFrame中
  12. df = pd.DataFrame({s1.name: s1, s2.name: s2, s3.name: s3})
  13. # df = df.set_index('A')
  14. # 假如Series index是[2,3,4] 没有的则会填充NaN
  15. print(df.head())

数据区域的读取，填充数字，文字序列

  1. import pandas as pd
  2. from datetime import date, timedelta
  5. # 实现月份累加算法,d是初始时间，md是累加的月份
  6. def add_month(d, md):
  7.     yd = md // 12
  8.     m = d.month + md % 12
  9.     if m != 12:
  10.         yd += m // 12
  11.         m = m % 12
  12.     return date(d.year + yd, m, d.day)
  15. # 跳过空行16行，选取E:H列 指定ID列的数据类型为int(先转为string类型，直接转int类型不行)
  16. books = pd.read_excel('./TestExcel/output004.xlsx', skiprows=16, usecols="E:H",
  17.                       dtype={'ID': str, 'InStore': str, 'Date': str})
  18. startTime = date(2021, 12, 25)
  19. for i in books.index:
  20.     books['ID'].at[i] = i + 1
  21.     print(type(books['ID']))  # 此种写法books['ID']是pandas.core.series.Series对象
  22.     books['InStore'].at[i] = 'Yes' if i % 2 == 0 else 'NO'  # 在Python中if else可以组成表达式用
  23.     # books['Date'].at[i] = startTime + timedelta(days=i)  # 天数依次累加
  24.     # books['Date'].at[i] = date(startTime.year+i,startTime.month,startTime.day)  # 年份依次累加
  25.     books['Date'].at[i] = add_month(startTime, i)  # 月份依次累加
  26. # print(books['ID'])
  28. books.to_excel('./TestExcel/output004Test.xlsx')
  29. print(books)

填充日期序列

  1. import pandas as pd
  2. from datetime import date, timedelta
  5. # 实现月份累加算法,d是初始时间，md是累加的月份
  6. def add_month(d, md):
  7.     yd = md // 12
  8.     m = d.month + md % 12
  9.     if m != 12:
  10.         yd += m // 12
  11.         m = m % 12
  12.     return date(d.year + yd, m, d.day)
  15. # 跳过空行16行，选取E:H列 指定ID列的数据类型为int(先转为string类型，直接转int类型不行)
  16. books = pd.read_excel('./TestExcel/output004.xlsx', skiprows=16, usecols="E:H",
  17.                       dtype={'ID': str, 'InStore': str, 'Date': str})
  18. startTime = date(2021, 12, 25)
  19. for i in books.index:
  20.     books['ID'].at[i] = i + 1
  21.     print(type(books['ID']))  # 此种写法books['ID']是pandas.core.series.Series对象
  22.     books['InStore'].at[i] = 'Yes' if i % 2 == 0 else 'NO'  # 在Python中if else可以组成表达式用
  23.     # books['Date'].at[i] = startTime + timedelta(days=i)  # 天数依次累加
  24.     # books['Date'].at[i] = date(startTime.year+i,startTime.month,startTime.day)  # 年份依次累加
  25.     books['Date'].at[i] = add_month(startTime, i)  # 月份依次累加
  26. # print(books['ID'])
  28. books.to_excel('./TestExcel/output004Test.xlsx')
  29. print(books)

函数填充，计算列

  1. import pandas as pd
  4. def add_2(x):
  5.     return x + 2
  8. books = pd.read_excel('./TestExcel/output005.xlsx', dtype={'ID': str, 'Name': str},index_col='ID')
  10. for i in books.index:
  11.     books['FirstPrice'].at[i] = 20 + i;
  12.     books['LastPrice'].at[i] = 30 + i;
  13. # 列计算无需取出每行，直接列运算即可
  14. books['FinalPrice'] = books['FirstPrice'] * books['LastPrice']
  15. # 自己列加2块钱
  16. books['FinalPrice'] += 2
  17. # 另一种方式追加2块钱，函数
  18. books['FinalPrice'] = books['FinalPrice'].apply(add_2)
  19. # 另一种方式追加2块钱，lambda表达式
  20. books['FinalPrice'] = books['FinalPrice'].apply(lambda x: x + 2)
  21. print(books)
  22. books.to_excel('./TestExcel/output005.xlsx')

排序

  1. import pandas as pd
  3. books = pd.read_excel('./TestExcel/output006.xlsx', dtype={'ID': str, 'Name': str}, index_col='ID')
  4. # inplace=True是为了不生产新的DF
  5. # 按照价格排序 ascending默认为True升序排列
  6. # books.sort_values(by='FinalPrice', inplace=True, ascending=False)
  7. # 先按照Worthly排序，再按照FinalPrice排序
  8. books.sort_values(by=['Worthly', 'FinalPrice'], inplace=True, ascending=[True,False])
  9. print(books)

数据筛选、过滤

  1. import pandas as pd
  4. def lastPrice_32_to_42(a):
  5.     return a >= 32 and a < 42
  8. def finalPrice_657_to_2000(a):
  9.     return 657 <= a < 2000
  12. books = pd.read_excel('./TestExcel/output007.xlsx', index_col='ID')
  13. # 这边loc定位到某列，按照apply里面的函数关系来进行筛选
  14. # books = books.loc[books['LastPrice'].apply(lastPrice_32_to_42)].loc[books['FinalPrice'].apply(finalPrice_657_to_2000)]
  15. # 可以直接.列名来修改
  16. books = books.loc[books.LastPrice.apply(lastPrice_32_to_42)].loc[books.FinalPrice.apply(finalPrice_657_to_2000)]
  17. books.sort_values(by=['Worthly', 'FinalPrice'], inplace=True, ascending=[True, False])
  18. print(books)

柱状图

Figure_1.png

  1. import pandas as pd
  2. import matplotlib.pyplot as plt
  4. # 绘图，不要添加index_col='Field'索引列
  5. books = pd.read_excel('./TestExcel/output008.xlsx')
  6. print(books.head(5))
  7. # 按照Number列排序 降序 替换原先的DF
  8. books.sort_values(by='Number', ascending=False, inplace=True)
  9. # 使用pandas绘图：横坐标为Field 纵坐标为Number 颜色橘色 标题xxx
  10. # books.plot.bar(x='Field', y='Number',color='orange',title='InterNationalBooks')
  11. # 使用plt绘图
  12. plt.bar(books.Field, books.Number, color='orange')
  13. # 旋转标签90
  14. plt.xticks(books.Field, rotation=90)
  15. plt.xlabel('Field')
  16. plt.ylabel('Number')
  17. plt.title('InternationalBooks', fontsize=16)
  18. # 紧凑型绘图，能够将横坐标全部显示出来
  19. plt.tight_layout()
  20. plt.show()

分组柱图，深度优化

Figure_2.png

  1. import pandas as pd
  2. import matplotlib.pyplot as plt
  4. books = pd.read_excel('./TestExcel/output009.xlsx')
  5. print(books)
  6. books.sort_values(by=2021, inplace=True, ascending=False)
  7. books.plot.bar(x='Field', y=[2020, 2021],color=['orange','blue'])
  8. plt.title('National Books', fontsize=16, fontweight='bold')
  9. plt.xlabel('Field', fontweight='bold')
  10. plt.ylabel('Number', fontweight='bold')
  11. # 自定义X轴样式:斜45° 文字最末端对齐
  12. ax = plt.gca()
  13. ax.set_xticklabels(books.Field, rotation=45, ha='right')
  14. # 将图片左边和底部固定
  15. figures = plt.gcf()
  16. figures.subplots_adjust(left=0.2,bottom=0.42)
  17. # plt.tight_layout()
  18. plt.show()

叠加柱状图，水平柱状图

image.png

  1. import pandas as pd
  2. import matplotlib.pyplot as plt
  5. users = pd.read_excel('./TestExcel/output010.xlsx')
  6. users['Total'] = users.Oct+users.Nov+users.Dec
  7. users.sort_values(by='Total',ascending=False,inplace=True)
  8. print(users)
  9. # 分组柱状图-->叠加柱状图stacked
  10. users.plot.bar(x='Name',y=['Oct','Nov','Dec'],stacked=True)
  11. plt.title('User Behavior',fontsize=24,fontweight='bold')
  12. # 旋转X轴文字45°
  13. ax = plt.gca()
  14. ax.set_xticklabels(users.Name,rotation=45,ha='right')
  15. plt.tight_layout()
  16. # 水平柱状图
  17. users.sort_values(by='Total',ascending=True,inplace=True)
  18. users.plot.barh(x='Name',y=['Oct','Nov','Dec'],stacked=True)
  19. plt.tight_layout()
  20. plt.show()

饼图

image.png

  1. import pandas as pd
  2. import matplotlib.pyplot as plt
  4. students = pd.read_excel('./TestExcel/output011.xlsx',index_col='From')
  5. print(students)
  6. # 绘制饼图,默认情况下以index_col作为图例显示饼图
  7. # 排序以顺时针形式呈现，并将开始位置设置为-250
  8. # students['2017'].sort_values().plot.pie(startangle=-250)
  9. # 第二种方式counterclock=False顺时针方式呈现
  10. students['2017'].plot.pie(counterclock=False)
  11. plt.title('Source of International Students')
  12. plt.ylabel('2017',fontweight='bold')
  13. plt.show()

折线趋势图，叠加区域图

image.png

  1. import pandas as pd
  2. import matplotlib.pyplot as plt
  4. weeks = pd.read_excel('./TestExcel/output012.xlsx',index_col='Week')
  5. print(weeks)
  6. print(weeks.columns)
  7. # 折线图
  8. weeks.plot(y=['Accessories', 'Bikes', 'Clothing', 'Components', 'Grand Total'])
  9. plt.title('Sales Weekly Trend',fontsize=16,fontweight='bold')
  10. plt.ylabel('Total',fontsize=12,fontweight='bold')
  11. plt.xticks(weeks.index)
  12. # 叠加区域图
  13. weeks.plot.area(y=['Accessories', 'Bikes', 'Clothing', 'Components', 'Grand Total'])
  14. plt.title('Sales Weekly Trend',fontsize=16,fontweight='bold')
  15. plt.ylabel('Total',fontsize=12,fontweight='bold')
  16. plt.xticks(weeks.index)
  17. plt.show()

散点图、直方图、密度图、数据相关性

  1. import pandas as pd
  2. import matplotlib.pyplot as plt 
  4. # 显示所有列
  5. pd.options.display.max_columns =777
  6. homes = pd.read_excel('./TestExcel/output013.xlsx')
  7. # print(homes.head())
  8. # 散点图scatter
  9. #homes.plot.scatter(x='sqft_living',y='price')
  10. # 直方图hist 分布区间bins
  11. # homes.sqft_living.plot.hist(bins=100)
  12. # plt.xticks(range(0,max(homes.sqft_living),5000),fontsize=8,rotation=90)
  13. # 密度图kde
  14. homes.sqft_living.plot.kde()
  15. plt.xticks(range(0,max(homes.sqft_living),5000),fontsize=8,rotation=90)
  16. plt.show()
  17. # 数据相关性分析 column row relation
  18. print(homes.corr())

多表联合(从VLOOKUP到JOIN)

连接了两张表，一张学生姓名表，一张分数表
image.pngimage.png
image.png

  1. from typing import SupportsRound
  2. import pandas as pd 
  4. students = pd.read_excel('./TestExcel/output014.xlsx',sheet_name='Students',index_col='ID')
  5. scores = pd.read_excel('./TestExcel/output014.xlsx',sheet_name='Scores',index_col='ID')
  7. # students表join scores表 how表示不论是否能查询到数据始终保持students表的数据 以ID列来join
  8. # fillna 用0来填充空值
  9. # 如果左右两边ID列名字不一样 left_on right_on
  10. # table = students.merge(scores,how='left',left_on=students.index, right_on=scores.index).fillna(0)
  11. table = students.join(scores,how='left').fillna(0)
  12. # 将浮点数转为原始的int数据
  13. table.Score = table.Score.astype(int)
  14. print(table)

数据校验、轴的概念

  1. import pandas as pd
  3. def validate_score(x):
  4.     """[校验函数]  
  6.     Args:
  7.         x ([object]): [学生信息]
  8.     """    
  9.     try:
  10.         assert 0<=x.Score<=100
  11.     except: 
  12.         print(f'#{x.ID}\tStudent {x.Name} has an invalid score {x.Score}')
  13.     #第二种写法
  14.     #if not 0<=x.Score<=100:
  15.         #print(f'#{x.ID}\tStudent {x.Name} has an invalid score {x.Score}')
  17. students = pd.read_excel('./TestExcel/output015.xlsx')
  18. # axis默认为0,从上到下  1,从左到右进行校验
  19. students.apply(validate_score,axis=1)
  20. # print(students)

把一列数据分割成两列

  1. import pandas as pd
  3. Students = pd.read_excel('./TestExcel/output015.xlsx')
  4. #  一列数据分割成两列expand=True n=0切出来多少子字符串就保留多少
  5. df = Students.Name.str.split('_',expand=True)
  6. Students['StudentTitle'] = df[0].str.upper()
  7. Students['StudentIndex'] = df[1]
  8. print(Students)

求和、求平均、统计引导

image.png

  1. import pandas as pd
  3. house_price = pd.read_excel('./TestExcel/output013.xlsx')
  5. # DataFrame.sum()
  6. # axis 1横轴 0纵轴 sum求和 mean求平均
  7. temp_total = house_price[['bedrooms','bathrooms']].sum(axis=1)
  8. temp_average = house_price[['bedrooms','bathrooms']].mean(axis=1)
  9. house_price['TotalRooms'] = temp_total
  10. house_price['AverageRooms'] = temp_average
  11. # house_price['AverageRooms'] .astype(int)
  12. # 追加列方向的累加
  13. col_sum = house_price[['ID','price','bedrooms','bathrooms','sqft_living','sqft_basement']].sum()
  14. col_sum['TotalRooms'] = 'Summary'
  15. # 将自动按照col_sum的列名来进行追加
  16. house_price = house_price.append(col_sum,ignore_index=True)
  18. print(house_price)

定位、消除重复数据

image.png

  1. from os import dup
  2. import pandas as pd 
  4. students = pd.read_excel('./TestExcel/output009.xlsx')
  6. row_sum = students[['Firstyear','Lastyear']].sum(axis=1)
  7. students['TotalSales'] = row_sum
  8. col_sum = students[['Firstyear','Lastyear','TotalSales']].sum()
  9. col_sum['Field'] = 'Summary'
  10. students = students.append(col_sum,ignore_index=True)
  11. print(students)
  12. print('\n')
  14. # 取出那些重复的数据
  15. dupe = students.duplicated(subset='Field')
  16. print(dupe.any())
  17. print(dupe.count())
  18. print(dupe[dupe]) #自己和True比较
  19. dupe = dupe[dupe==True]
  20. print(f'{dupe.index}\n')
  21. print(f'{students.iloc[dupe.index]}\n') #取出那些数据
  23. # 消除重复数据，基于哪些列 subset=['Field',Firstyear'] keep='first'/'last'保留开始还是后面的
  24. students.drop_duplicates(subset='Field',inplace=True,keep='first')
  25. print(students)

旋转数据表（行转换）

image.png

  1. import pandas as pd
  3. # 显示最大列
  4. pd.options.display.max_columns = 999
  5. videos = pd.read_excel('./TestExcel/output016.xlsx',index_col='Month')
  6. # 旋转表格，要将index_col设置为旋转坐标列Month，不然就会以默认index来作为列名
  7. videos = videos.transpose()
  8. print(videos)

读取CVS、TSV、TXT文件中的数据

  1. import pandas as pd
  3. students1 = pd.read_csv('./TestOtherFiles/Students.csv')
  4. print(students1)
  6. students2 = pd.read_csv('./TestOtherFiles/Students.txt',sep='|')
  7. print(students2)

透视表、分组、聚合(GroupBy)

  1. import pandas as pd
  2. import numpy as np
  4. pd.options.display.max_columns = 777
  5. orders = pd.read_excel('./TestExcel/output017.xlsx')
  6. orders['Year'] = pd.DatetimeIndex(orders.Date).year
  7. print(orders)
  9. piovot_table1=orders.pivot_table(index='Category',columns='Year',values='Total',aggfunc=np.sum)
  10. print(piovot_table1)
  12. groups = orders.groupby(['Category','Year'])
  13. s = groups['Total'].sum()
  14. c = groups['ID'].count()
  15. piovot_table2 = pd.DataFrame({'Sum':s,'Count':c})
  16. print(piovot_table2)

线性回归、数据预测

数据分析和机器学习的边界

  1. import pandas as pd
  2. import matplotlib.pyplot as plt 
  3. from scipy.stats import linregress
  5. def validate_revenue(series):
  6.     if not 5<series.Revenue<20:
  7.         print(f'{series}值不在范围内')
  9. sales = pd.read_excel('./TestExcel/output018.xlsx',dtype={'Month':str})
  11. # 斜率，截距
  12. slope,intercept,r,p,std_err = linregress(sales.index,sales.Revenue)
  13. # 线性回归方程
  14. exp = sales.index*slope+intercept
  16. # sales.plot.area(y='Revenue')
  17. # 散点图
  18. plt.scatter(x=sales.index,y=sales['Revenue'],color='orange')
  19. plt.plot(sales.index,exp,color='blue')
  20. #plt.bar(sales.index,sales.Revenue)
  21. ax = plt.gca()
  22. ax.set_xticklabels(sales['Month'],rotation=45,ha='right')
  23. plt.title(f'y={slope}*X+{intercept}')
  24. plt.tight_layout()
  25. plt.show()
  27. # 横向验证，传入函数的是series
  28. #sales.apply(validate_revenue,axis=1)
  29. #print(sales.Month.dtype)

条件格式化

anacoda input: jupyter notebook
image.png

  1. import pandas as pd
  3. def low_score_red(s):
  4.     color = 'red' if s<21000 else 'black'
  5.     return f'color:{color}'
  7. def highest_score_green(col):
  8.     #把字体编程绿色，底层变为黄绿色
  9.     return ['background-color:lime;color:green' if s==col.max() else 'background-color:white' for s in col]
  11. students = pd.read_excel('F:/Repo/albertpython/TestExcel/output011.xlsx')
  12. students.style.applymap(low_score_red,subset=['2016','2017'])\
  13. .apply(highest_score_green,subset=['2016','2017'])

image.png

  1. import pandas as pd
  2. import seaborn as sns
  4. color_map = sns.light_palette('green',as_cmap=True)
  5. students = pd.read_excel('F:/Repo/albertpython/TestExcel/output011.xlsx')
  6. # 将颜色深度应用到数据列上
  7. students.style.background_gradient(color_map,subset=['2016','2017'])

image.png

  1. import pandas as pd
  2. #import seaborn as sns
  4. #color_map = sns.light_palette('green',as_cmap=True)
  5. students = pd.read_excel('F:/Repo/albertpython/TestExcel/output011.xlsx')
  6. # 将颜色深度应用到数据列上
  7. # students.style.background_gradient(color_map,subset=['2016','2017'])
  8. students.style.bar(color='orange',subset=['2016','2017'])

行操作集锦

  1. import pandas as pd
  3. students1 = pd.read_excel('F:/Repo/albertpython/TestExcel/output011.xlsx',sheet_name='Sheet1')
  4. students2 = pd.read_excel('F:/Repo/albertpython/TestExcel/output011.xlsx',sheet_name='Sheet2')
  6. # 两个Sheet合并为一个,重置index_col drop=True放弃原来的
  7. student_total = students1.append(students2).reset_index(drop=True)
  9. # 追加手动创建的一行
  10. stu = pd.Series({'Rank':100,'From':'USA','2016':90,'2017':100})
  11. student_total=student_total.append(stu,ignore_index=True)
  13. # 更改其中一行的值
  14. student_total['From'].at[13] = 'Mexico3'
  15. student_total.at[12,'From'] = 'Japan3'
  17. # 插入一行 切片技术
  18. stu = pd.Series({'Rank':101,'From':'USB','2016':90,'2017':100})
  19. # 左闭右开
  20. part1 = student_total[:10]
  21. part2 = student_total[10:]
  22. student_total = part1.append(stu,ignore_index=True).append(part2).reset_index(drop=True)
  24. # 删除数据行
  25. # student_total.drop(index=[0,1,2],inplace=True)
  26. # student_total.drop(index=range(4,6),inplace=True)
  28. for i in range(5,8):
  29.     student_total['From'].at[i] =''
  31. missing_data = student_total.loc[student_total['From']=='']
  32. student_total.drop(index=missing_data.index,inplace=True) #切片的方式传入
  33. student_total.reset_index(drop=True) # 重新铺一下index
  34. print(student_total)

列操作集锦

  1. import pandas as pd
  2. import numpy as np
  5. students = pd.read_excel('./TestExcel/output014.xlsx',sheet_name='Students')
  6. scores = pd.read_excel('./TestExcel/output014.xlsx',sheet_name='Scores')
  8. # 列横向拼接
  9. list_total = pd.concat([students,scores],axis=1).reset_index(drop=True)
  10. print(list_total)
  12. # 行追加第二种写法
  13. list_total2 = pd.concat([students,scores],ignore_index=True).reset_index(drop=True)
  14. print(list_total2)
  16. # 将ID相同Join进来
  17. list_total3 = students.merge(scores,how='left',on=['ID','ID'])
  19. # 追加一列：
  20. list_total3['Age'] = 25
  21. list_total3['RealAge'] = np.arange(0,len(list_total3))
  22. print(f'Total3:\n{list_total3}\n')
  23. print(np.arange(0,len(list_total3)))
  25. # 删除列
  26. list_total3.drop(columns=['Age','RealAge'],inplace=True)
  27. print(f'DropTotal3:\n{list_total3}\n')
  29. # 插入列
  30. list_total3.insert(1,column='Foo',value=26)
  31. print(f'DropTotal3:\n{list_total3}\n')
  33. # 列名大写
  34. list_total3.rename(columns={'Foo':'FOO'},inplace=True)
  35. print(f'Upper:\n{list_total3}\n')
  37. # 删除空行
  38. list_total3.dropna(inplace=True)
  39. print(f'Dropnan:\n{list_total3}\n')

读取数据库

image.png

  1. import pandas as pd
  2. import numpy as np
  3. import pyodbc as dbc 
  4. # ORM框架 create_engine
  5. import sqlalchemy
  7. books = pd.read_excel('./TestExcel/output019.xlsx')
  8. print(books)
  10. # 此处注意Server={ServerName} ServerName未必是{local}
  11. connection = dbc.connect('Driver={SQL Server};'
  12.                       'Server={DESKTOP-07OJ9VD};'
  13.                       'Database=AlbertBook;'
  14.                       'Trusted_Connection=yes;')
  18. query = 'Select Title from T_Books'
  19. df1 = pd.read_sql_query(query,connection)
  20. print(df1)

编写复杂方程

image.png

  1. import pandas as pd
  2. import numpy as np
  4. def cal_bike_complex_clothing(b,c):
  5.     return b**2+c*np.pi
  7. sales = pd.read_excel('./TestExcel/output012.xlsx')
  8. sales['complex'] = sales.apply(lambda row:cal_bike_complex_clothing(row['Bikes'],row['Clothing']),axis=1)
  10. #将列名大写
  11. sales.rename(columns={'complex':'Complex'},inplace=True)
  12. sales.drop(columns={'Components'},inplace=True)
  13. print(sales)