Writing DataFrame to CSV with add header name for row names in Pandas

Question

I have the following Data frame:

In [18]: import pandas as pd
In [32]:  df = pd.DataFrame.from_items([("A\tbar", [1, 2, 3]), ("B\tfoo" , [4, 5, 6])],orient='index', columns=['one', 'two', 'three'])

In [33]: df
Out[35]: 
   one  two  three
A\tbar    1    2      3
B\tfoo    4    5      6

In [34]: df.to_csv("tmp.csv" , sep='\t', encoding='utf-8', doublequote=False)

The final written file looks like this (note the double quote in row name still exist too, we'd like to remove that):

In [34]: !cat tmp.csv
    one two three
"A  bar"    1   2   3
"B  foo"    4   5   6

What I want to do is to name the columns of the row names that looks like this in the final created file (tmp.csv):

alpha othername one two three
A   bar     1   2   3
B   foo     4   5   6

What's the way to do it?

If you want them to be separate columns, why don't you create them as separate columns instead of a single column with a tab in it? — BrenBarn
– BrenBarn, Commented Nov 21, 2014 at 5:21
In actual case the creation of row names is more complex that that. I'd like to keep it simple for the problem statement. — neversaint
– neversaint, Commented Nov 21, 2014 at 5:23
The basic answer is that you can't name the columns of your rownames, because your row names don't have columns. Your index is just a bunch of strings that happen to have tabs in them, but pandas doesn't know that they all have tabs in them. You could give the first column a name with a tab in it too (i.e., name it `"alpha\tothername"). — BrenBarn
– BrenBarn, Commented Nov 21, 2014 at 5:25
If I make the row names as another column, how do I exclude row names from being printed out? It'll be great if you can guide with example code. — neversaint
– neversaint, Commented Nov 21, 2014 at 5:28
You can exclude the index by passing index=False to to_csv. — BrenBarn
– BrenBarn, Commented Nov 21, 2014 at 5:30

Kirubaharan J · Accepted Answer · 2014-11-21 06:20:38Z

1

Thanks to BrenBarn for index=False

df = pd.DataFrame.from_items([("A\tbar", [1, 2, 3]), ("B\tfoo" , [4, 5, 6])],orient='index', columns=['one', 'two', 'three'])
df['col_a'] = df.index
lista = [item.split('\t')[0] for item in df['col_a']]
listb = [item.split('\t')[1] for item in df['col_a']]
df['col_a'] = lista
df['col_b'] = listb
cols = df.columns.tolist()
cols = cols[-2:] + cols[:-2]
df = df[cols]
df.to_csv('filename', index=False)

answered Nov 21, 2014 at 6:20

Kirubaharan J

2,3651 gold badge17 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Writing DataFrame to CSV with add header name for row names in Pandas

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related