pandas writing to excel .. help

[anna]

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')