Insert and rename columns in multiple csv files and merging into one csv

Question

I have plenty of csvs that look like this:

I want to rename column names because they are too long and to insert new column named Company with same values (eg. aapl (ticker for Apple)). Here's the code how I did that:

data = pd.read_csv('aapl.csv', index_col=0)
data.insert(0, 'Company', 'aapl')
data.rename(columns={'Unnamed: 0': 'Company', 'Working Capital / Total Assets':'WC/TA', 'Retained Earnings / Total Assets': 'RE/TA', 'EBIT / Total Assets':'EB/TA','Current Market Cap / Total Liabilites':'MC/TL', 'Revenue / Total Assets':'RV/TA','Net Income / Total Assets (ROA)':'NI/TA', 'Total Liabilites / Total Assets':'TL/TA', 'Current Assets / Current Liabilites':'CA/CL'})
data.to_csv('aapl.csv')

and I get

Thing is, I have 278 other companies in same csv format (imb.csv, ase.csv, wire.csv etc.) and I would like to rename/insert columns for all of them in the same way. Finaly, I need to merge all these csv files into one csv (that is the reason why is new column "Company" needed).

My question is, how to create new column (name: Company) and populate it with name of the file.

For example: load ibm.csv -> rename columns -> add new column (Company) -> insert value ibm (filename/ticker) in column Company -> export to csv.

jezrael · Accepted Answer · 2018-09-05T10:30:09.663

2

You can read each file in dict comprehension, add key by name of file and last concat together:

import glob, os

files = glob.glob('files/*.csv')

d = {'Working Capital / Total Assets':'WC/TA', 
     'Retained Earnings / Total Assets': 'RE/TA', 
     'EBIT / Total Assets':'EB/TA',
     'Current Market Cap / Total Liabilites':'MC/TL',
     'Revenue / Total Assets':'RV/TA',
     'Net Income / Total Assets (ROA)':'NI/TA',
     'Total Liabilites / Total Assets':'TL/TA',
     'Current Assets / Current Liabilites':'CA/CL'}

dfs = {os.path.basename(fp).split('.')[0]: pd.read_csv(fp).rename(columns=d) for fp in files}
df = pd.concat(dfs).reset_index(level=1,  drop=True).rename_axis('company').reset_index()

edited Sep 05 '18 at 10:30

answered Sep 05 '18 at 10:09

jezrael

822,522
95
1,334
1,252

getting error `ValueError: Plan shapes are not aligned` EDIT: solved error – dejanmarich Sep 05 '18 at 10:17
1

@DejanMarić - Problem is with duplicated columns names - [check](https://stackoverflow.com/a/27412913). – jezrael Sep 05 '18 at 10:19
Columns that have empty values are not merged at all. I know in some files there are missing values.. – dejanmarich Sep 05 '18 at 10:33
@DejanMarić - Hard to help, are data confidental? – jezrael Sep 05 '18 at 10:36
They are from bloomgerg, I guess they are but not sure. I just noticed some typos, guess that's the problem, some columns are "currnt" instead of "current".. need to check everything again – dejanmarich Sep 05 '18 at 10:40
1

@DejanMarić - Or add this values to dict like `'Currnt Market Cap / Total Liabilites':'MC/TL',` – jezrael Sep 05 '18 at 10:41

Insert and rename columns in multiple csv files and merging into one csv

1 Answers1