1

Need a suggestion in my code.

I have a data frame in sheet1 of workbook:

column 1           column 2
000A0000              2
000B0000              3
000A0001              5
000B0001              1

My desired result:

in sheet 2 of Workbook:

column 1           column 2
 000A0000              2
 000A0001              5

In sheet 3 of Workbook:

column 1           column 2
 000B0000              3
 000B0001              1

I have done my coding:

import pandas as pd
file="workbook.xlxs"
print(data.sheet_names)
data=data.parse("sheet1")

substrings = ['A', 'B']

T = {x: df[df['sheet1'].str.contains(x, na=False, regex=False)] for x in substrings]

for key, var in T.items():
    var.to_excel(f'{key}.xlsx', index=False)

by this I can create new workbook. But I need to create new worksheet in same workbook.

Any suggestion would be appreciated.

Improve this question
1
  • what exactly do you want to write into the 3 worksheets? Commented Sep 6, 2018 at 6:58

3 Answers 3

Reset to default
2

To add sheets to the same excel file use openpyxl module as follows:

import pandas as pd
import openpyxl

#reading the sheet1 using read_excel
df = pd.read_excel('workbook.xlsx', sheet_name='Sheet1')

#creating pandas ExcelWriter object and loading the excel file using `openpyxl`    
df_writer = pd.ExcelWriter('workbook.xlsx', engine='openpyxl')
excel = openpyxl.load_workbook('workbook.xlsx')
df_writer.book = excel

#checking string in column 1 and writing those to respective sheets in same workbook
for string in ['A','B']:
    df[df['column 1'].str.contains(string)].to_excel(df_writer,sheet_name=string)
#saving and closing writer
writer.save()
writer.close()
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

@user10309160 You are Welcome!
hey, A small question. for string in ['A','B'] this selects the data which is in the list. What if I want the data which does not contain these list ? help please.
@user10309160 For example if don't want data in the above list use df[~df['column 1'].str.contains('A|B')] which gives rows where both A and B are not present.
@user10309160 It does not give the data which have either A or B. It will not include the data which contains them.
1

to_excel would not append sheets to your existing file:

use openpyxl instead:(something like below)

import pandas
from openpyxl import load_workbook

book = load_workbook('path+filename_you_want_to_write_in.xlsx')
writer = pandas.ExcelWriter('path+filename_you_want_to_write_in.xlsx', engine='openpyxl') 
writer.book = book
writer.sheets = dict((ws.title, ws) for ws in book.worksheets)

df.to_excel(writer, "Sheet_name_as_per_your_choice",index=False)

writer.save()

Also if you dynamically want to read through the sheets and not specific sheets:

f = pd.ExcelFile(file)
sheet_names = df.sheet_names
for i in list(sheet_names):
    df = pd.read_excel(f,i)

This iterates through all your sheets and provides a dataframe based on the sheets.

Improve this answer

4 Comments

Sorry.not helping. As I want to extract the data from Sheet1 , the above code is not helping me
This isn't the exact code, since I wasn;t clear with your requirement I gave you a reference where you can implement your code :)
@user10309160 : you will have to read the sheets via whatever method you want. Compute your requirements and then save it via the codes in the answer
@user10309160 : you can implement the addition code to dynamically read through all your worksheets and create a df.
0

Try using the xlsxwriter engine.

writer = pd.ExcelWriter('<< file_name >>', engine='xlsxwriter')

df.to_excel(writer, sheet_name='Sheet2')

writer.save()
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.