Python Forum
pandas writing to excel .. help
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')
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  Pandas keep existing format of Excel AlphaInc 2 883 Jan-11-2024, 03:44 AM
Last Post: plonkarchivist
  [Pandas] Write data to Excel with dot decimals manonB 1 5,774 May-05-2021, 05:28 PM
Last Post: ibreeden
  Python - Pandas writing blank files to file tdunphy 0 1,972 Jan-14-2021, 12:11 AM
Last Post: tdunphy
  Python PDF merging from an excel pandas for loop siraero 0 2,164 Aug-16-2020, 09:34 AM
Last Post: siraero
  Need Help With Filtering Data For Excel Files Using Pandas eddywinch82 9 5,988 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 6,421 Jun-26-2019, 11:11 PM
Last Post: Dequanharrison
  Python pandas remove default format in excel indra 0 2,658 Feb-06-2019, 04:48 AM
Last Post: indra
  Python read Password protected excel and convert to Pandas DataFrame FORTITUDE 2 17,023 Aug-30-2018, 01:08 PM
Last Post: FORTITUDE
  Pandas - Write to Exisitng Excel File - Sorted List dj99 4 14,755 Jul-29-2018, 07:56 AM
Last Post: dj99
  Updating the Pandas dataframe to existing excel workbook in existing worksheet. sanmaya 1 9,725 Jul-01-2018, 06:23 PM
Last Post: volcano63

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020