CODEWITHSUNDEEP
pythonexcelpandas
Vlad Tretiakov
Vlad Tretiakov

Reputation: 23

Combine data into 1 worksheet in Excel via Python

I have a bunch of worksheets in single excel file in the same format. I need to take just one column out of each table and to combine those data in new worksheet and new excel file.

Example of goal

I have struggled to find solution for my question. But still I have not reached any results.

I will appreciate any help or link to helpful materials.

Upvotes: 1

Views: 122

Answers (1)

Leo
Leo

Reputation: 466

You can use pd.read_excel() and iterate over each sheet in the workbook.

import pandas as pd
data = pd.read_excel( YOUR_FILE, sheet_name=None )
ouput_dict = dict()
for i,sheet in enumerate( data.keys() ):
    temp_df = data[sheet]
    new_col_name = col + str(i)
    output_dict[ new_col_name ] = temp_df[ col ]
final_df = pd.DataFrame( data=output_dict )

Inside the for loop, temp_df will be a pandas DataFrame based on that individual sheet. In this example you could name the column new_col_name, taking the column col from that sheet. In your example, col would be "Open".

Upvotes: 2

Related Questions