Python pandas库处理EXCEL数据的几个小技巧

1. 5个tips

1. python list去重后保留顺序
2. python dict值逆序排列
3. python pandas某一列数据多个拆分成多行拓展
4. python list转pandas，并写入excel
5. pandas读取复合表头数据，指定读取那几行作为标头
6. pandas改变某一列的值
7. pandas追加数据到excel
8. excel列名与数字转换
9. excel表格列相同内容合并
10. excel表格增加边框线
11. excel表格增加背景颜色

2. 源码

import pandas as pd

# list去重后保留顺序
old_list = ["a", "b", "a", "c", "b", "a"]
new_list = list(dict.fromkeys(old).keys())


# pandas某一列单列变多行——拓展
df = pd.DataFrame({'A': list(range(1,6)),
                    'B': ['1,3,5','2,4,6','100','200','300']})
df['A'] = df['A'].apply(lambda x: x**2)
df = df.drop('B', axis=1).join(df['B'].str.split(',', expand=True).stack().reset_index(level=1, drop=True).rename('B'))

# list转pandas
df_new = pd.DataFrame(['a','b','c','d','e'],columns=['generateColumn'])
df_new.to_excel('list2xlsx.xlsx',index=False)

# 读取复合表头
df_data = pd.read_excel('testMultiIndex.xlsx',header=[2,3,4])

# pandas改变某一列的值
df['A'] = df['A'].apply(lambda x: x**2)

参考

• https://blog.csdn.net/boysoft2002/article/details/119794260
• https://blog.csdn.net/flysnownet/article/details/134049045
• https://blog.csdn.net/shenqigongshi/article/details/131438042
• https://blog.csdn.net/shenghaomail/article/details/125754836
• https://blog.csdn.net/Xw_Classmate/article/details/109006600