Open csv file with Pandas and delete if has only 1 row

Question

I have a task to create a script to ssh to list of 10 cisco routers weekly and check for config changes and send notification. So i have in place the script that logs and run the command and send it to csv. I have modified so if there is not changes all I have in the csv will be for example: rtr0003# -which is the router name only. If there will be conf change the excel will have inside for example:

My question is how to run pandas to open each file and if it sees only one line/row to delete the excel file and if more lines to skip it.

This is how i write the files:

files = glob.glob('*.csv')
for file in files:
    df=pd.read_csv(file)
    df=df.dropna()
    df.to_csv(file,index=False)
    df1=pd.read_csv(file,skiprows = 2)
    #df1=df1.drop(df1.tail(1))
    df1.to_csv(file,index=False)

Why use python / pandas for this at all? You could just do it in bash [source](https://stackoverflow.com/questions/3137094/how-to-count-lines-in-a-document) — forgetso, Nov 14 '18 at 10:52
all at once-using paramiko to ssh and run the command , then pandas to create and filter the csv and then send an email.Seems all easier then mixing bash , python etc. — Ivan Madolev, Nov 14 '18 at 14:02

Karl · Answer 1 · 2018-11-14T11:11:20.523

1

import os    
import glob
import csv

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

for file in files:
    with open(file,"r") as f:
        reader = csv.reader(f,delimiter = ",")
        data = list(reader)
        row_count = len(data)

    if row_count == 1:
        os.remove(file)

edited Nov 14 '18 at 11:11

answered Nov 14 '18 at 11:05

Karl

5,573
8
50
73

score 1 · Answer 2 · answered Nov 14 '18 at 11:27

Here is a solution using pandas:

import pandas as pd
import glob
import os

csv_files = glob.glob('*.csv')
for file in csv_files:
    df_file = pd.read_csv(file, low_memory = False)
    if len(df_file) == 1:
        os.remove(file)

If you are using excel files, change

glob.glob('*.csv')

to

glob.glob('*.xlsx')

and

pd.read_csv(file, low_memory = False)

to

pd.read_excel(file)

Open csv file with Pandas and delete if has only 1 row

2 Answers2