Python excel to csv copying column data with different header names

user_12275230 0
Viewed 1366

So here is my situation. Using Python I want to copy specific columns from excel spreadsheet into specific columns into a csv worksheet.

The pre-filled column header names are named differently in each spreadsheet and I need to use a sublist as a parameter.

For example, in the first sublist, data column in excel needs to be copied from/to: 

spreadsheet      csv
"scan_date" => "date_of_scan"

Two sublists as parameters: one of names copied from excel, one of names of where to paste into csv.

Not sure if a dictionary sublist would be better than two individual sublists?

Also, the csv column header names are in row B (not row A like excel) which has complicated things such as data frames.

So, ideally I would like to have sublists converted to arrays,

  • spreadsheet iterates columns to find "scan_date"
  • copies data
  • iterates to find "date_of_scan" in csv
  • paste data
  • moves on to the second item in the sublists and repeats.

I've tried pandas and openpyxl and just can't seem to figure out the approach/syntax of how to do it.

Any help would be greatly appreciated. Thank you.

Clarification edit: The csv file has some preexisting data within. Also, I cannot change the headers into different columns. So, if "date_of_scan" is in column "RF" then it must stay in column "RF". I was able to copy, say, the 5 columns of data from excel into a temp spreadsheet and then concatenate into the csv but it always moved the pasted columns to the beginning of the csv document (columns A, B, C, D, E).

openpyxl sublist excel python
1 Answers

It is hard to know the answer without seeing you specific dataset, but it seems to me that a simpler approach might be to simply make your excel sheet a df, drop everything except the columns you want in the csv then write a csv with pandas. Here's some psuedo-code.

import pandas as pd

df=pd.read_excel('your_file_name.xlsx')

drop_cols=[,,,]  #list of columns to get rid of

df.drop(drop_cols,axis='columns')


col_dict={'a':'x','b':'y','c':'z'} #however you want to map you new columns in this example abc are old columns and xyz are new ones


#this line will actually rename your columns with the dictionary
df=df.rename(columns=col_dict)


df.to_csv('new_file_name.csv')  #write new file

and this will actually run in python, but I created the df from dummy data instead of an excel file.

#with dummy data
df=pd.DataFrame([0,1,2],index=['a','b','c']).T
col_dict={'a':'x','b':'y','c':'z'}
df=df.rename(columns=col_dict)
df.to_csv('new_file_name.csv')  #write new file
user_104435080
Related