0

I want to copy the columns from an Excel file in a certain order, and then paste the columns I concatenated to the data frame df1 into another excel file at a certain interval.

In other words, in the dataframe, paste the first column from A1 to A1 in the excel file, the second column to A3 to the third column A5 ... (assuming we have pasted 50 such columns). I've used these codes so far. But I'm blocked at this point. Thanks in advance for your help.

import os
import pandas as pd
from os.path import expanduser

os.chdir('C:\Table')  

files = os.listdir('C:\Table')

print('List of files at *.xls ve *.xlsx format:\n', files)

all_files = [f for f in files if (f[-3:] == 'xls' or f[-4:] == 'xlsx')]

df1 = pd.DataFrame() # Creating empty dataframe

for f in all_files:
    # Take values on C column
    names= pd.read_excel(f, skiprows=1, parse_cols="C:C",sheetname='Sheet1', header=None)
    df1 = pd.concat([df1, names[:1]], axis=1) 

print(df1)

home = expanduser("~\Desktop") #For saving desktop

Saving = input("Please Insert Name Of File:")

writer = pd.ExcelWriter(os.path.join(home,Saving+'.xlsx'), engine='xlsxwriter')
df1.to_excel(writer,startcol=1,startrow=5, sheet_name='Sheet1', header=None, index=False)
workbook = writer.book
worksheet = writer.sheets['Sheet1']
writer.save()
Stefan Falk
  • 23,898
  • 50
  • 191
  • 378
Yusuf
  • 21
  • 5
  • So what exactly is your goal? You want to read from an Excel file, but only some specific columns and then save those columns to another Excel file - is that correct? What exactly is not working? – Stefan Falk Oct 12 '17 at 20:32
  • Btw you can use `os.path.splitext(f)[1]` to extract a file extension. – Stefan Falk Oct 12 '17 at 20:36
  • I copy a specific cell of all the Excel files in a folder. I paste them side by side in a dataframe with the concatenate. I want to paste the columns in this dataframe to another Excel file in a specific order(for example, the first column in dataframe to A1 in Excel file, the second column in dataframe to A3 in Excel file, the third column in dataframe to A5 in Excel file, the fourth column in dataframe to A7 in Excel file, the fifth column in dataframe to A9 in Excel file) .In total, I have more than 50 columns on the dataframe.I want to do this automatically. But I do not know how to do it. – Yusuf Oct 12 '17 at 20:39
  • It's going to boild down to *how* you want to sort the columns. Reordering columns is not a problem [see this](https://stackoverflow.com/questions/11067027/python-pandas-re-ordering-columns-in-a-dataframe-based-on-column-name) example. – Stefan Falk Oct 13 '17 at 08:18

0 Answers0