Bottom Page

Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
 pandas writing to excel .. help
#1
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')
Quote

Top Page

Possibly Related Threads...
Thread Author Replies Views Last Post
  Need Help With Filtering Data For Excel Files Using Pandas eddywinch82 9 596 Aug-06-2019, 03:44 PM
Last Post: eddywinch82
  How to use the excel filename as a value to populate new column, using Pandas? Dequanharrison 5 543 Jun-26-2019, 11:11 PM
Last Post: Dequanharrison
  Python pandas remove default format in excel indra 0 425 Feb-06-2019, 04:48 AM
Last Post: indra
  Python read Password protected excel and convert to Pandas DataFrame FORTITUDE 2 5,124 Aug-30-2018, 01:08 PM
Last Post: FORTITUDE
  Pandas - Write to Exisitng Excel File - Sorted List dj99 4 3,110 Jul-29-2018, 07:56 AM
Last Post: dj99
  Updating the Pandas dataframe to existing excel workbook in existing worksheet. sanmaya 1 3,824 Jul-01-2018, 06:23 PM
Last Post: volcano63
  Pandas/Excel, reading from one column writing back to another... williamlombard 1 711 Mar-01-2018, 06:02 AM
Last Post: williamlombard
  Looping script and writing to Excel in Python Rolflund 2 1,055 Jan-21-2018, 10:31 PM
Last Post: j.crater

Forum Jump:


Users browsing this thread: 1 Guest(s)