如何使用 pandas python根据给定的ip地址将大型excel文件拆分为多个工作表 [英] How to split a large excel file into multiple worksheets based on their given ip address using pandas python
问题描述
我是熊猫和python的新手,所以遇到了一些麻烦.我有一个很大的Excel文件,我需要使用python脚本分成多个工作表.我必须根据数据中给出的IP地址进行划分.我不知道该怎么做,希望能得到一些帮助和指导.我之前不了解使用python或任何库的知识.这是我所做的,但是为每一行创建了工作簿.
I'm new to pandas and python so ran into some trouble. I have a one large excel file which i need to divide into multiple worksheets using a python script. That i have to divide base on the ip addresses given in the data. I can't figure out how to do that and would appreciate some help and guidance. I have no knowledge of working with python or any libraries before. This is what i did but created workbooks for each row.
import pandas as pd
df = pd.read_excel("D:/Users/Zakir/Desktop/MyNotebooks/Legacy.xls", sheet_name="Total", header=0, names=None, index_col=None, parse_cols=None, usecols=None, squeeze=False, dtype=None, engine=None, converters=None, true_values=None, false_values=None, skiprows=None, nrows=None, na_values=None, keep_default_na=True, verbose=False, parse_dates=False, date_parser=None, thousands=None, comment=None, skipfooter=0, convert_float=True, mangle_dupe_cols=True)
writer = pd.ExcelWriter('D:/Users/Zakir/Desktop/MyNotebooks/pandas_simple.xlsx', engine='xlsxwriter')
for index, row in df.iterrows():
df1 = df.iloc[[index]]
df1.set_index('Number',inplace=True)
df1.to_excel(writer, sheet_name=row['IPAddress'])
writer.save()
这是我拥有的一种excel文件.超过5000行.ip地址有60组,并且必须将每个组划分为自己的工作表
推荐答案
如果有足够的内存,则是一个解决方案:
one solution if you have enough memory:
from pandas import ExcelWriter
df = pd.read_excel('file',sheet_name="Total", header=0, #other settings.....#)
writer = ExcelWriter('E:/output.xlsx',engine='xlsxwriter')
print(df)
def writesheet(g):
a = g['IPAddress'].tolist()[0]
g.to_excel(writer, sheet_name=str(a), index=False)# index = True if you want to keep index
df.groupby('IPAddress').apply(writesheet)
writer.save()
这篇关于如何使用 pandas python根据给定的ip地址将大型excel文件拆分为多个工作表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!