+- Python Forum (https://python-forum.io)
+-- Forum: Python Coding (https://python-forum.io/forum-7.html)
+--- Forum: Data Science (https://python-forum.io/forum-44.html)
+--- Thread: pandas writing to excel .. help (/thread-19249.html)
pandas writing to excel .. help - anna - Jun-20-2019
Hi All,
below is my small script, taking all .csv files in list. taking specific columns in dataframe and writing to excel.
need small help..
First two letters of each file is circle name, I am able to split but not able to insert that into excel against each line. Also insert one more column header as 'Vendor' and add 'ECI' against each records in excel.
import pandas as pd
import os
eci_dump = [x for x in os.listdir('.') if x.endswith('.csv')]
for item in eci_dump:
filename = item
circle_name = item.split('_')[0]
df = pd.read_csv(filename, usecols = ["Link name","Rate","Endpoint1", "Endpoint2"],error_bad_lines=False, header = 0,skipinitialspace = True)
with pd.ExcelWriter('output.xlsx') as writer:
df.to_excel(writer, sheet_name='Sheet_name_1')Hi,
Modified code, only one circle name is updated,
there are 5 files, only first file's split output is getting added in excel.
Files
1) ROM_PHY_LinkList_26052019.csv
2) MUM_PHY_LinkList_26052019.csv
3) KA_PHY_LinkList_28052019.csv
4) GJ_PHY_LinkList_26052019.csv
5) DL_PHY_LinkList_26052019.csv
import pandas as pd
import os
eci_dump = [x for x in os.listdir('.') if x.endswith('.csv')]
for item in eci_dump:
filename = item
circle_name = item.split('_')[0]
df = pd.read_csv(filename, usecols = ["Link name","Rate","Endpoint1", "Endpoint2"],error_bad_lines=False, header = 0,skipinitialspace = True)
df['circle'] = circle_name
df['vendor'] = 'ECI'
with pd.ExcelWriter('output.xlsx') as writer:
df.to_excel(writer, sheet_name='eci_dump')