Editing headers in pandas ( python)

Question

I have a data as shown below,

x = [1,2,3,4,5]
y = [6,7,8,9,0]
z = [11,12,12,56,6]

Now, I want to export this data to an excel file. For that I have used the following code

import numpy as np
import pandas as pd

data = pd.DataFrame({"x":[1,2,3,4,5],
                     " ":np.nan,
                     "y":[6,7,8,9,0],
                     " ":np.nan,
                     "z":[11,12,12,56,6]})
data.to_excel('test_file.xlsx', index= False)

Here I used the np.nan to create an empty column.

The new excel file is as shown

but, I want to show the data as shown in this picture

Please help. Thank you.

jezrael · Accepted Answer · 2017-11-24 12:21:28Z

1

There is problem your dictionary of lists contain duplicate column name ' ' and it is per design not allowed. So is possible create DataFrame by column_stack and define columns names:

empty =  [np.nan] * len(x)
data = pd.DataFrame(np.column_stack([x,empty,z,empty,y]), columns= ['x','','y','','z'])

Or a little hack - create all unique columns first in dict and then rename:

data = pd.DataFrame({"x":x,
                     "empty1":np.nan,
                     "y":y,
                     "empty2":np.nan,
                     "z":z}, columns=['x','empty1','y','empty2','z'])
data = data.rename(columns={'empty1':'', 'empty2':''})         

print (data)
     x         y        z
0  1.0 NaN  11.0 NaN  6.0
1  2.0 NaN  12.0 NaN  7.0
2  3.0 NaN  12.0 NaN  8.0
3  4.0 NaN  56.0 NaN  9.0
4  5.0 NaN   6.0 NaN  0.0


data.to_excel('test_file.xlsx', index= False)

edited Nov 24, 2017 at 12:21

answered Nov 24, 2017 at 12:15

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

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

Comments

Renjith Thankachan · Accepted Answer · 2017-11-24 13:03:31Z

0

you can try this way also ( no cost hack :) )

data = pd.DataFrame({"x": [1,2,3,4,5],
                     "": np.nan,
                     "y": [6,7,8,9,0],
                     None: np.nan,
                     "z": [11,12,12,56,6]})

answered Nov 24, 2017 at 13:03

Renjith Thankachan

4,3571 gold badge33 silver badges47 bronze badges

Collectives™ on Stack Overflow

Editing headers in pandas ( python)

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related