I have a situation wherein sometimes when I read a csv
from df
I get an unwanted index-like column named unnamed:0
.
file.csv
,A,B,C
0,1,2,3
1,4,5,6
2,7,8,9
The CSV is read with this:
pd.read_csv('file.csv')
Unnamed: 0 A B C
0 0 1 2 3
1 1 4 5 6
2 2 7 8 9
This is very annoying! Does anyone have an idea on how to get rid of this?
You can do the following with Unnamed Columns:
file.csv
,A,B,C
0,1,2,3
1,4,5,6
2,7,8,9
#read file
df = pd.read_csv('file.csv')
Method 1: Delete Unnamed Columns
# delete one by one like column is 'Unnamed: 0' so use it's name
df.drop('Unnamed: 0', axis=1, inplace=True)
#delete all Unnamed Columns in a single code of line using regex
df.drop(df.filter(regex="Unnamed"),axis=1, inplace=True)
Method 2: Rename Unnamed Columns
df.rename(columns = {'Unnamed: 0':'Name'}, inplace = True)
If you want to write out with a blank header as in the input file, just choose 'Name' above to be ''.
This is usually caused by your CSV having been saved along with an (unnamed) index (RangeIndex
).
(The fix would actually need to be done when saving the DataFrame, but this isn't always an option.)
read_csv
with index_col=[0]
argumentIMO, the simplest solution would be to read the unnamed column as the index. Specify an index_col=[0]
argument to pd.read_csv
, this reads in the first column as the index. (Note the square brackets).
df = pd.DataFrame('x', index=range(5), columns=list('abc'))
df
a b c
0 x x x
1 x x x
2 x x x
3 x x x
4 x x x
# Save DataFrame to CSV.
df.to_csv('file.csv')
<!- ->
pd.read_csv('file.csv')
Unnamed: 0 a b c
0 0 x x x
1 1 x x x
2 2 x x x
3 3 x x x
4 4 x x x
# Now try this again, with the extra argument.
pd.read_csv('file.csv', index_col=[0])
a b c
0 x x x
1 x x x
2 x x x
3 x x x
4 x x x
Note
You could have avoided this in the first place by usingindex=False
if the output CSV was created in pandas, if your DataFrame does not have an index to begin with:df.to_csv('file.csv', index=False)
But as mentioned above, this isn't always an option.
str.match
If you cannot modify the code to read/write the CSV file, you can just remove the column by filtering with str.match
:
df
Unnamed: 0 a b c
0 0 x x x
1 1 x x x
2 2 x x x
3 3 x x x
4 4 x x x
df.columns
# Index(['Unnamed: 0', 'a', 'b', 'c'], dtype='object')
df.columns.str.match('Unnamed')
# array([ True, False, False, False])
df.loc[:, ~df.columns.str.match('Unnamed')]
a b c
0 x x x
1 x x x
2 x x x
3 x x x
4 x x x
Simply delete that column using: del df['column_name']
To get ride of all Unnamed columns, you can also use regex such as df.drop(df.filter(regex="Unname"),axis=1, inplace=True)
Simple do this:
df = df.loc[:, ~df.columns.str.contains('^Unnamed')]
Another case that this might be happening is if your data was improperly written to your csv
to have each row end with a comma. This will leave you with an unnamed column Unnamed: x
at the end of your data when you try to read it into a df
.
Source: Stackoverflow.com