Writing multi-indexed data-frame to csv without changing its format

Question

I created a multi-indexed data-frame by concatenating 3 4-column data-frames:

df = pd.concat([df_RCB, df_SECB, df_UBP], axis=1, sort=True)

headers = (['RCB']*4) + (['SECB']*4) + (['UBP']*4)
sub-headers = df.columns.tolist()

data = np.array(df)
data = pd.DataFrame(data=data, columns=pd.MultiIndex.from_tuples(list(zip(headers, sub-headers))), index=df.index)

data.head()

Output:

             RCB                     SECB                        UBP
            open  high   low close   open   high    low  close  open  high    low close   
1999-02-04  25.0  25.0  25.0  25.0  14.25  14.50  13.75  13.75  15.5  15.5  15.25  15.5
1999-02-05  25.0  25.0  25.0  25.0  13.75  13.75  13.75  13.75  15.5  15.5  15.50  15.5
1999-02-08  25.0  25.0  25.0  25.0  13.75  13.75  13.75  13.75  15.5  15.5  15.50  15.5
1999-02-09  25.0  25.0  25.0  25.0  13.75  13.75  13.75  13.75  15.5  15.5  15.50  15.5
1999-02-10  25.0  25.0  25.0  25.0  13.75  13.75  13.75  13.75  15.5  15.5  15.50  15.5

I wrote it to a csv file then read it but the format changed:

data.to_csv('fin.csv', index_label=False)
fin = pd.read_csv('fin.csv')

fin.head()

Output:

   Unnamed: 0   RCB RCB.1 RCB.2  RCB.3   SECB SECB.1 SECB.2 SECB.3   UBP UBP.1  UBP.2  UBP.3
0         NaN  open  high   low  close   open   high    low  close  open  high    low  close
1  1999-02-04  25.0  25.0  25.0   25.0  14.25   14.5  13.75  13.75  15.5  15.5  15.25   15.5
2  1999-02-05  25.0  25.0  25.0   25.0  13.75  13.75  13.75  13.75  15.5  15.5   15.5   15.5
3  1999-02-08  25.0  25.0  25.0   25.0  13.75  13.75  13.75  13.75  15.5  15.5   15.5   15.5
4  1999-02-09  25.0  25.0  25.0   25.0  13.75  13.75  13.75  13.75  15.5  15.5   15.5   15.5

How can I retain the format of the data-frame from before writing to csv?

score 0 · Accepted Answer · answered May 06 '19 at 01:06

0

You should write to excel rather than csv if you want to keep the multiple index

data.to_excel('fin.xlsx')

Also you can create the data by

df = pd.concat([df_RCB, df_SECB, df_UBP],keys=['RCB','SECB','UBP'] axis=1, sort=True)

answered May 06 '19 at 01:06

BENY

317,841
20
164
234

Sir, if I read the excel file, can I convert it back to pandas dataframe with the same format? – Yoyong May 06 '19 at 01:18
@Yoyong yes you can – BENY May 06 '19 at 01:21
@Yoyong https://stackoverflow.com/questions/35300099/reading-excel-sheet-as-multiindex-dataframe-through-pd-read-excel – BENY May 06 '19 at 01:21

Writing multi-indexed data-frame to csv without changing its format

1 Answers1