'How to delete columns in a CSV file?
I have been able to create a csv with python using the input from several users on this site and I wish to express my gratitude for your posts. I am now stumped and will post my first question.
My input.csv looks like this:
day,month,year,lat,long
01,04,2001,45.00,120.00
02,04,2003,44.00,118.00
I am trying to delete the "year" column and all of its entries. In total there is 40+ entries with a range of years from 1960-2010.
Solution 1:[1]
Use of Pandas module will be much easier.
import pandas as pd
f=pd.read_csv("test.csv")
keep_col = ['day','month','lat','long']
new_f = f[keep_col]
new_f.to_csv("newFile.csv", index=False)
And here is short explanation:
>>>f=pd.read_csv("test.csv")
>>> f
day month year lat long
0 1 4 2001 45 120
1 2 4 2003 44 118
>>> keep_col = ['day','month','lat','long']
>>> f[keep_col]
day month lat long
0 1 4 45 120
1 2 4 44 118
>>>
Solution 2:[2]
Using a dict to grab headings then looping through gets you what you need cleanly.
import csv
ct = 0
cols_i_want = {'cost' : -1, 'date' : -1}
with open("file1.csv","rb") as source:
rdr = csv.reader( source )
with open("result","wb") as result:
wtr = csv.writer( result )
for row in rdr:
if ct == 0:
cc = 0
for col in row:
for ciw in cols_i_want:
if col == ciw:
cols_i_want[ciw] = cc
cc += 1
wtr.writerow( (row[cols_i_want['cost']], row[cols_i_want['date']]) )
ct += 1
Solution 3:[3]
I would use Pandas with col number
f = pd.read_csv("test.csv", usecols=[0,1,3,4])
f.to_csv("test.csv", index=False)
Solution 4:[4]
you can use the csv package to iterate over your csv file and output the columns that you want to another csv file.
The example below is not tested and should illustrate a solution:
import csv
file_name = 'C:\Temp\my_file.csv'
output_file = 'C:\Temp\new_file.csv'
csv_file = open(file_name, 'r')
## note that the index of the year column is excluded
column_indices = [0,1,3,4]
with open(output_file, 'w') as fh:
reader = csv.reader(csv_file, delimiter=',')
for row in reader:
tmp_row = []
for col_inx in column_indices:
tmp_row.append(row[col_inx])
fh.write(','.join(tmp_row))
Solution 5:[5]
Off the top of my head, this will do it without any sort of error checking nor ability to configure anything. That is "left to the reader".
outFile = open( 'newFile', 'w' )
for line in open( 'oldFile' ):
items = line.split( ',' )
outFile.write( ','.join( items[:2] + items[ 3: ] ) )
outFile.close()
Solution 6:[6]
You can directly delete the column with just
del variable_name['year']
Solution 7:[7]
I will add yet another answer to this question. Since the OP did not say they needed to do it with Python, the fastest way to delete the column (specially when the input file has hundreds of thousands of lines), is by using awk.
This is the type of problem where awk shines:
$ awk -F, 'BEGIN {OFS=","} {print $1,$2,$4,$5}' input.csv
(feel free to append > output.csv to the command above if you need the output to be saved to a file)
Credit goes 100% to @eric-wilson who provided this awesome answer, as a comment on the original question, 10 years ago, almost without any credit.
Solution 8:[8]
Try:
result= data.drop('year', 1)
result.head(5)
Solution 9:[9]
It depends on how you store the parsed CSV, but generally you want the del operator.
If you have an array of dicts:
input = [ {'day':01, 'month':04, 'year':2001, ...}, ... ]
for E in input: del E['year']
If you have an array of arrays:
input = [ [01, 04, 2001, ...],
[...],
...
]
for E in input: del E[2]
Solution 10:[10]
Try python with pandas and exclude the column, you don't want to have:
import pandas as pd
# the ',' is the default separator, but if your file has another one, you have to define it with sep= parameter
df = pd.read_csv("input.csv", sep=',')
exclude_column = "year"
new_df = df.loc[:, df.columns != exclude_column]
# you can even save the result to the same file
new_df.to_csv("input.csv", index=False, sep=',')
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow