How to delete specific data from single line in CSV FILE and last line?

Question

I have a CSV file that I have successfully removed lines from using the following code:

    myfiles = glob.glob('myfile.csv')
    
    for file in myfiles:
        lines = open(file).readlines()
        open(file, 'w').writelines(lines[27:])

Now what remains in the CSV file is the following:

"Football names", "F1", "F2", "F3", "F4", "F5", "F6", "F7", "F8", "F9", "F10"

"Football weights", "8", "10", "11", "120", "10", "21", "20", "1000", "2000", "3000"

"Copy of Football weights", "8", "10", "11", "120", "10", "21", "20", "1000", "2000", "3000"

What I want to do:

I have been trying to edit the CSV file using the code above to completely delete line 6 but don't know how to add it to the code above and also edit Line 2 and line 4 to remove the last 3 three contents of the line (F8,F9,F10 and 1000,2000,3000 respectively) --> so the CSV should look like the following below:

"Football names", "F1", "F2", "F3", "F4", "F5", "F6", "F7"

"Football weights", "8", "10", "11", "120", "10", "21", "20"

Thank you in advance if anyone can give me some pointers or tips.

I suggest adding the data used (or at least some dummy data) other times you post. Should be easier for people to help. — A.M. Ducu, Jul 14 '21 at 10:22
yes, but you need to put it in code styling. I submitted an edit to your post so you don't have to worry about it. But remember for next time you ask a question. :) — A.M. Ducu, Jul 14 '21 at 10:41

score 2 · Accepted Answer · edited Jul 14 '21 at 11:21

Use the csv module to read and rewrite back without the last 3 columns

for file in myfiles:
    rows = []

    with io.open(file,"r",encoding="utf-8") as f:
        reader = csv.reader(f, delimiter=",", quotechar='"')

        for row in reader:
            rows.append(row[:-3])

    with io.open(file,"w",encoding="utf-8") as f:
        writer = csv.writer(f)
        
        for row in rows:
            writer.writerow(row)

score 1 · Answer 2 · answered Jul 14 '21 at 10:02

You can create a dataframe from the csv file and use drop function to delete the columns and rows in it.

# Convert the dictionary into DataFrame 
df = pd.DataFrame(data)
  
# Remove last 3 columns.
new_df=df.drop(df.iloc[:, -3:], axis = 1)

# Remove last row.
new_df=df.drop(df.iloc[-1,:],axis=1)

A.M. Ducu · Answer 3 · 2021-07-14T10:56:35.483

1

Here's a simple solution using the csv library.

import csv

# myfiles = ['f1.csv', 'f2.csv']  # this is for my testing
myfiles = glob.glob('myfile.csv')

for file in myfiles:
    with open(file, 'r') as csvfile:
        lines = list(csv.reader(csvfile, quotechar='"'))  # Read input file
    with open(file, 'w') as csvfile:
        writer = csv.writer(csvfile)  # Write to the same file
        # Remove the last line (I don't know exactly if you need to)
        for line in lines[27: -1]:  # Remove -1 to keep the last line
            # Removing last three columns
            writer.writerow(line[: -3])

Let me know if there's anything else I can help with.

edited Jul 14 '21 at 10:56

answered Jul 14 '21 at 10:20

A.M. Ducu

892
7
19

Hi, I am getting an error "argument 1 must have a "write" method" For the line "writer - csv.writer(file)" – ThomasGXS Jul 14 '21 at 10:31
@ThomasGXS sorry, I forgot two lines when rewriting the code... I just updated it. – A.M. Ducu Jul 14 '21 at 10:40
@ThomasGXS also forgot to remove one useless line of code... Sorry about that. – A.M. Ducu Jul 14 '21 at 10:44
Ducu Thanks for the update in code however I am getting the following error: "'_csv.reader' object is not subscriptable" This is for line - "for line in lines[27: -1] – ThomasGXS Jul 14 '21 at 10:50
@ThomasGXS this time I tested it on a new dataset with more rows and it seems to work. Forgot to convert the reader object to a list. I guess that's what happens when you want to deliver help fast :) – A.M. Ducu Jul 14 '21 at 10:57
Thank you - Always appreciated – ThomasGXS Jul 14 '21 at 11:01
@ThomasGXS let me know if it works on your side too. Upvotes are also much appreciated ;) – A.M. Ducu Jul 14 '21 at 11:03
Hey yeah sure - the last part did not work for me unfortunately which was removing the lines but I managed to do it using a similar solution above so thank you – ThomasGXS Jul 14 '21 at 12:35

How to delete specific data from single line in CSV FILE and last line?

3 Answers3