pandas操作Excel笔记
pip install -i https://pypi.tuna.tsinghua.edu.cn/simple python-office -U
元素
import pandas as pd
- 数据帧
df = pd.DataFrame({'ID': [1, 2, 3], 'Name': ['Tim', 'Vinctor', 'Nick']}) - Index
df.set_index('ID',inplace=True) - Series
行列表示
import pandas as pd
s1 = pd.Series({'x': 100, 'y': 200, 'z': 300})
print(s1)
L1 = [100, 200, 300]
L2 = ['x', 'y', 'z']
s2 = pd.Series(L1, index=L2)
print(s2)
print("==")
# index不能对齐则取并集
s3 = pd.Series([1, 2, 3], index=[1, 2, 3], name='A')
s4 = pd.Series([100, 20, 30], index=[1, 2, 3], name='B')
s5 = pd.Series([100, 200, 300], index=[2, 3, 4], name='C')
# index竖向
df = pd.DataFrame({s3.name: s3, s4.name: s4, s5.name: s5})
# df.set_index('A', inplace=True)
print(df)
# index横向
df = pd.DataFrame([s3, s4, s5])
print("==")
print(df)
数据填充
import pandas as pd
from datetime import date, timedelta
# date month_delta 考虑尚不周全(2.31🤣)
def add_month(d: date, md: int):
yd = md // 12
m = d.month + md % 12
if m != 12: # 再进行一次操作即可全部完成
yd += m // 12
m %= 12
return date(d.year + yd, m, d.day)
# 暂时先不设index_col为ID,否则不好操作
# 当pandas读取到NaN时会设为浮点类型,str填充方便
books = pd.read_excel('C:/Temp/books.xlsx', skiprows=5, usecols='B:E', index_col=None,
dtype={'ID': str, 'InStore': str, 'Date': str})
start = date(2018, 1, 1)
print(type(books['ID']))
for i in books.index:
books['ID'].at[i] = i + 1
# books.at[i,'ID']=i+1 # 先横后竖
books['InStore'].at[i] = 'Yes' if i % 2 == 0 else 'No'
# books['Date'].at[i] = start + timedelta(days=i) # minutes hours miliseconds
# books['Date'].at[i] = date(start.year + i, start.month, start.day)
books['Date'].at[i] = add_month(start, i)
print(books)
books.set_index('ID', inplace=True)
books.to_excel('C:/Temp/books_output.xlsx')
排序
import pandas as pd
products = pd.read_excel('C:/Temp/list.xlsx', index_col='ID')
# default from low to high | ascend=climb
products.sort_values(by=['Worthy', 'Price'], inplace=True, ascending=[True, False])
print(products)
本文作者:TomZz
本文链接:https://www.cnblogs.com/Tom-Zz/p/16577839.html
版权声明:本作品采用知识共享署名-非商业性使用-禁止演绎 2.5 中国大陆许可协议进行许可。
【推荐】国内首个AI IDE,深度理解中文开发场景,立即下载体验Trae
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步