Fhyujk

Am I a Rude Number?

Can I write a book of my D&D game?

How to remove lines through the legend markers in ListPlot?

Writing a character who is going through a civilizing process without overdoing it?

Can a hotel cancel a confirmed reservation?

Why "Points exist" is not an axiom in Geometry

How would an AI self awareness kill switch work?

How long is the D&D Starter Set campaign?

How do Chazal know that the descendants of a Mamzer may never marry into the general populace?

Difference between `vector<int> v;` and `vector<int> v = vector<int>();`

Why isn't there a non-conducting core wire for high-frequency coil applications

Why is working on the same position for more than 15 years not a red flag?

What's a good word to describe a public place that looks like it wouldn't be rough?

How do you funnel food off a cutting board?

Why did other German political parties disband so fast when Hitler was appointed chancellor?

Eww, those bytes are gross

Porting Linux to another platform requirements

Publishing research using outdated methods

Why exactly do action photographers need high fps burst cameras?

Why Normality assumption in linear regression

Dilemma of explaining to interviewer that he is the reason for declining second interview

Roman Numerals equation 1

What is the wife of a henpecked husband called?

How can animals be objects of ethics without being subjects as well?

How to Write Multiple Data Frames in an Excel Sheet

Do data scientists use Excel?How do I merge two data frames in Python Pandas?Excel formula questionIs Excel sufficient for data science?Create new data frames from existing data frame based on unique column valuesCombine Pandas DataFrames with year columnsHow do I compare columns in different data frames?Spearmanr on two pandas dataframesExcel All Possible Combinations for MacOSCategorical data into numeric in excel

1

$begingroup$

I have multiple data frames with same column names. I want to write them together to an excel sheet stacked vertically on top of each other. And between each, there will be a text occupying a row. This is what I have in mind.

I tried the pandas.ExcelWriter() method, but each dataframe overwrites the previous frame in the sheet, instead of appending.

Note that, I still need multiple sheets for different dataframe, but also multiple dataframes on each sheet. Is it possible? Or any other python library which can dynamically generate the excel sheet from pandas dataframes?

pandas dataframe excel data.table

share|improve this question

edited 4 hours ago

Della

asked 4 hours ago

DellaDella

1104

$endgroup$

add a comment | 

1

$begingroup$

I have multiple data frames with same column names. I want to write them together to an excel sheet stacked vertically on top of each other. And between each, there will be a text occupying a row. This is what I have in mind.

I tried the pandas.ExcelWriter() method, but each dataframe overwrites the previous frame in the sheet, instead of appending.

Note that, I still need multiple sheets for different dataframe, but also multiple dataframes on each sheet. Is it possible? Or any other python library which can dynamically generate the excel sheet from pandas dataframes?

pandas dataframe excel data.table

share|improve this question

edited 4 hours ago

Della

asked 4 hours ago

DellaDella

1104

$endgroup$

add a comment | 

$begingroup$

I have multiple data frames with same column names. I want to write them together to an excel sheet stacked vertically on top of each other. And between each, there will be a text occupying a row. This is what I have in mind.

I tried the pandas.ExcelWriter() method, but each dataframe overwrites the previous frame in the sheet, instead of appending.

Note that, I still need multiple sheets for different dataframe, but also multiple dataframes on each sheet. Is it possible? Or any other python library which can dynamically generate the excel sheet from pandas dataframes?

pandas dataframe excel data.table

share|improve this question

edited 4 hours ago

Della

asked 4 hours ago

DellaDella

1104

$endgroup$

share|improve this question

edited 4 hours ago

Della

asked 4 hours ago

DellaDella

1104

share|improve this question

edited 4 hours ago

Della

asked 4 hours ago

DellaDella

1104

asked 4 hours ago

DellaDella

1104

asked 4 hours ago

DellaDella

1104

3 Answers
3

active

oldest

votes

2

$begingroup$

Several dataframes to same sheet from here and here with selected sheet:

# Position the dataframes in the worksheet.

df1.to_excel(writer, sheet_name='Sheet1')  # Default position, cell A1.

df2.to_excel(writer, sheet_name='Sheet1', startcol=3)

df3.to_excel(writer, sheet_name='Sheet1', startrow=6)

share|improve this answer

answered 1 hour ago

chacidchacid

414

New contributor

chacid is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

$endgroup$

add a comment | 

2

$begingroup$

an example to write in same sheet:

import pandas as pd



data1 = """

class    precision   recall 

<18      0.0125         12           

18-24    0.0250         16 

25-34    0.00350         4

"""

data2 = """

class    precision   recall 

<18      0               0           

18-24    0.25            6 

25-34    0.35            5

"""



#create 2 df for sample

df1 = pd.read_csv(pd.compat.StringIO(data1), sep='s+')

df1.name = "Dataframe1"

df2 = pd.read_csv(pd.compat.StringIO(data2), sep='s+')

df2.name = "Dataframe2"

print(df1);print(df2)



writer = pd.ExcelWriter('e:\test.xlsx',engine='xlsxwriter')

workbook=writer.book

worksheet=workbook.add_worksheet('Result')

writer.sheets['Result'] = worksheet

worksheet.write_string(0, 0, df1.name)



df1.to_excel(writer,sheet_name='Result',startrow=1 , startcol=0)

worksheet.write_string(df1.shape[0] + 4, 0, df2.name)

df2.to_excel(writer,sheet_name='Result',startrow=df1.shape[0] + 5, startcol=0)

output:

if you want to write in different sheets:

import pandas as pd



# Create a Pandas Excel writer using XlsxWriter as the engine.

writer = pd.ExcelWriter('e:\test.xlsx', engine='xlsxwriter')



# Write each dataframe to a different worksheet. you could write different string like above if you want

df1.to_excel(writer, sheet_name='Sheet1')

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



# Close the Pandas Excel writer and output the Excel file.

writer.save()

share|improve this answer

answered 59 mins ago

FrenchyFrenchy

1213

New contributor

Frenchy is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

$endgroup$

add a comment | 

0

$begingroup$

You can open the excel editor and write to it and then save

writer = pd.ExcelWriter('test.xlsx', engine='xlsxwriter')

data.to_excel(writer, sheet_name='Sheet1',

                  encoding='utf-8', index=False)

writer.save()

Please refer this answer https://stackoverflow.com/questions/34744863/python-how-to-use-excelwriter-to-write-into-an-existing-worksheet

share|improve this answer

answered 34 mins ago

ItachiItachi

1613

$endgroup$

add a comment | 

Your Answer

StackExchange.ifUsing("editor", function () {
return StackExchange.using("mathjaxEditing", function () {
StackExchange.MarkdownEditor.creationCallbacks.add(function (editor, postfix) {
StackExchange.mathjaxEditing.prepareWmdForMathJax(editor, postfix, [["$", "$"], ["\\(","\\)"]]);
});
});
}, "mathjax-editing");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "557"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdatascience.stackexchange.com%2fquestions%2f46437%2fhow-to-write-multiple-data-frames-in-an-excel-sheet%23new-answer', 'question_page');
}
);

Post as a guest

Name

Email

Required, but never shown

2

$begingroup$

Several dataframes to same sheet from here and here with selected sheet:

# Position the dataframes in the worksheet.

df1.to_excel(writer, sheet_name='Sheet1')  # Default position, cell A1.

df2.to_excel(writer, sheet_name='Sheet1', startcol=3)

df3.to_excel(writer, sheet_name='Sheet1', startrow=6)

share|improve this answer

answered 1 hour ago

chacidchacid

414

New contributor

chacid is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

$endgroup$

add a comment | 

2

$begingroup$

Several dataframes to same sheet from here and here with selected sheet:

# Position the dataframes in the worksheet.

df1.to_excel(writer, sheet_name='Sheet1')  # Default position, cell A1.

df2.to_excel(writer, sheet_name='Sheet1', startcol=3)

df3.to_excel(writer, sheet_name='Sheet1', startrow=6)

share|improve this answer

answered 1 hour ago

chacidchacid

414

New contributor

chacid is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

$endgroup$

add a comment | 

$begingroup$

Several dataframes to same sheet from here and here with selected sheet:

# Position the dataframes in the worksheet.

df1.to_excel(writer, sheet_name='Sheet1')  # Default position, cell A1.

df2.to_excel(writer, sheet_name='Sheet1', startcol=3)

df3.to_excel(writer, sheet_name='Sheet1', startrow=6)

share|improve this answer

answered 1 hour ago

chacidchacid

414

New contributor

chacid is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

$endgroup$

# Position the dataframes in the worksheet.

df1.to_excel(writer, sheet_name='Sheet1')  # Default position, cell A1.

df2.to_excel(writer, sheet_name='Sheet1', startcol=3)

df3.to_excel(writer, sheet_name='Sheet1', startrow=6)

share|improve this answer

answered 1 hour ago

chacidchacid

414

New contributor

chacid is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

answered 1 hour ago

chacidchacid

414

New contributor

chacid is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

answered 1 hour ago

chacidchacid

414

2

$begingroup$

an example to write in same sheet:

import pandas as pd



data1 = """

class    precision   recall 

<18      0.0125         12           

18-24    0.0250         16 

25-34    0.00350         4

"""

data2 = """

class    precision   recall 

<18      0               0           

18-24    0.25            6 

25-34    0.35            5

"""



#create 2 df for sample

df1 = pd.read_csv(pd.compat.StringIO(data1), sep='s+')

df1.name = "Dataframe1"

df2 = pd.read_csv(pd.compat.StringIO(data2), sep='s+')

df2.name = "Dataframe2"

print(df1);print(df2)



writer = pd.ExcelWriter('e:\test.xlsx',engine='xlsxwriter')

workbook=writer.book

worksheet=workbook.add_worksheet('Result')

writer.sheets['Result'] = worksheet

worksheet.write_string(0, 0, df1.name)



df1.to_excel(writer,sheet_name='Result',startrow=1 , startcol=0)

worksheet.write_string(df1.shape[0] + 4, 0, df2.name)

df2.to_excel(writer,sheet_name='Result',startrow=df1.shape[0] + 5, startcol=0)

output:

if you want to write in different sheets:

import pandas as pd



# Create a Pandas Excel writer using XlsxWriter as the engine.

writer = pd.ExcelWriter('e:\test.xlsx', engine='xlsxwriter')



# Write each dataframe to a different worksheet. you could write different string like above if you want

df1.to_excel(writer, sheet_name='Sheet1')

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



# Close the Pandas Excel writer and output the Excel file.

writer.save()

share|improve this answer

answered 59 mins ago

FrenchyFrenchy

1213

New contributor

Frenchy is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

$endgroup$

add a comment | 

2

$begingroup$

an example to write in same sheet:

import pandas as pd



data1 = """

class    precision   recall 

<18      0.0125         12           

18-24    0.0250         16 

25-34    0.00350         4

"""

data2 = """

class    precision   recall 

<18      0               0           

18-24    0.25            6 

25-34    0.35            5

"""



#create 2 df for sample

df1 = pd.read_csv(pd.compat.StringIO(data1), sep='s+')

df1.name = "Dataframe1"

df2 = pd.read_csv(pd.compat.StringIO(data2), sep='s+')

df2.name = "Dataframe2"

print(df1);print(df2)



writer = pd.ExcelWriter('e:\test.xlsx',engine='xlsxwriter')

workbook=writer.book

worksheet=workbook.add_worksheet('Result')

writer.sheets['Result'] = worksheet

worksheet.write_string(0, 0, df1.name)



df1.to_excel(writer,sheet_name='Result',startrow=1 , startcol=0)

worksheet.write_string(df1.shape[0] + 4, 0, df2.name)

df2.to_excel(writer,sheet_name='Result',startrow=df1.shape[0] + 5, startcol=0)

output:

if you want to write in different sheets:

import pandas as pd



# Create a Pandas Excel writer using XlsxWriter as the engine.

writer = pd.ExcelWriter('e:\test.xlsx', engine='xlsxwriter')



# Write each dataframe to a different worksheet. you could write different string like above if you want

df1.to_excel(writer, sheet_name='Sheet1')

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



# Close the Pandas Excel writer and output the Excel file.

writer.save()

share|improve this answer

answered 59 mins ago

FrenchyFrenchy

1213

New contributor

Frenchy is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

$endgroup$

add a comment | 

$begingroup$

an example to write in same sheet:

import pandas as pd



data1 = """

class    precision   recall 

<18      0.0125         12           

18-24    0.0250         16 

25-34    0.00350         4

"""

data2 = """

class    precision   recall 

<18      0               0           

18-24    0.25            6 

25-34    0.35            5

"""



#create 2 df for sample

df1 = pd.read_csv(pd.compat.StringIO(data1), sep='s+')

df1.name = "Dataframe1"

df2 = pd.read_csv(pd.compat.StringIO(data2), sep='s+')

df2.name = "Dataframe2"

print(df1);print(df2)



writer = pd.ExcelWriter('e:\test.xlsx',engine='xlsxwriter')

workbook=writer.book

worksheet=workbook.add_worksheet('Result')

writer.sheets['Result'] = worksheet

worksheet.write_string(0, 0, df1.name)



df1.to_excel(writer,sheet_name='Result',startrow=1 , startcol=0)

worksheet.write_string(df1.shape[0] + 4, 0, df2.name)

df2.to_excel(writer,sheet_name='Result',startrow=df1.shape[0] + 5, startcol=0)

output:

if you want to write in different sheets:

import pandas as pd



# Create a Pandas Excel writer using XlsxWriter as the engine.

writer = pd.ExcelWriter('e:\test.xlsx', engine='xlsxwriter')



# Write each dataframe to a different worksheet. you could write different string like above if you want

df1.to_excel(writer, sheet_name='Sheet1')

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



# Close the Pandas Excel writer and output the Excel file.

writer.save()

share|improve this answer

answered 59 mins ago

FrenchyFrenchy

1213

New contributor

Frenchy is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

$endgroup$

import pandas as pd



data1 = """

class    precision   recall 

<18      0.0125         12           

18-24    0.0250         16 

25-34    0.00350         4

"""

data2 = """

class    precision   recall 

<18      0               0           

18-24    0.25            6 

25-34    0.35            5

"""



#create 2 df for sample

df1 = pd.read_csv(pd.compat.StringIO(data1), sep='s+')

df1.name = "Dataframe1"

df2 = pd.read_csv(pd.compat.StringIO(data2), sep='s+')

df2.name = "Dataframe2"

print(df1);print(df2)



writer = pd.ExcelWriter('e:\test.xlsx',engine='xlsxwriter')

workbook=writer.book

worksheet=workbook.add_worksheet('Result')

writer.sheets['Result'] = worksheet

worksheet.write_string(0, 0, df1.name)



df1.to_excel(writer,sheet_name='Result',startrow=1 , startcol=0)

worksheet.write_string(df1.shape[0] + 4, 0, df2.name)

df2.to_excel(writer,sheet_name='Result',startrow=df1.shape[0] + 5, startcol=0)

import pandas as pd



# Create a Pandas Excel writer using XlsxWriter as the engine.

writer = pd.ExcelWriter('e:\test.xlsx', engine='xlsxwriter')



# Write each dataframe to a different worksheet. you could write different string like above if you want

df1.to_excel(writer, sheet_name='Sheet1')

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



# Close the Pandas Excel writer and output the Excel file.

writer.save()

share|improve this answer

answered 59 mins ago

FrenchyFrenchy

1213

New contributor

Frenchy is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

answered 59 mins ago

FrenchyFrenchy

1213

New contributor

Frenchy is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

answered 59 mins ago

FrenchyFrenchy

1213

0

$begingroup$

You can open the excel editor and write to it and then save

writer = pd.ExcelWriter('test.xlsx', engine='xlsxwriter')

data.to_excel(writer, sheet_name='Sheet1',

                  encoding='utf-8', index=False)

writer.save()

Please refer this answer https://stackoverflow.com/questions/34744863/python-how-to-use-excelwriter-to-write-into-an-existing-worksheet

share|improve this answer

answered 34 mins ago

ItachiItachi

1613

$endgroup$

add a comment | 

0

$begingroup$

You can open the excel editor and write to it and then save

writer = pd.ExcelWriter('test.xlsx', engine='xlsxwriter')

data.to_excel(writer, sheet_name='Sheet1',

                  encoding='utf-8', index=False)

writer.save()

Please refer this answer https://stackoverflow.com/questions/34744863/python-how-to-use-excelwriter-to-write-into-an-existing-worksheet

share|improve this answer

answered 34 mins ago

ItachiItachi

1613

$endgroup$

add a comment | 

$begingroup$

You can open the excel editor and write to it and then save

writer = pd.ExcelWriter('test.xlsx', engine='xlsxwriter')

data.to_excel(writer, sheet_name='Sheet1',

                  encoding='utf-8', index=False)

writer.save()

Please refer this answer https://stackoverflow.com/questions/34744863/python-how-to-use-excelwriter-to-write-into-an-existing-worksheet

share|improve this answer

answered 34 mins ago

ItachiItachi

1613

$endgroup$

writer = pd.ExcelWriter('test.xlsx', engine='xlsxwriter')

data.to_excel(writer, sheet_name='Sheet1',

                  encoding='utf-8', index=False)

writer.save()

share|improve this answer

answered 34 mins ago

ItachiItachi

1613

answered 34 mins ago

ItachiItachi

1613

answered 34 mins ago

ItachiItachi

1613