How to join 2 cell in 1 cell in header?

Question

Above is my dataframe, and I wish to get this out put on header

Anyone have idea on this?

Please have a look at [How to make good reproducible pandas examples](https://stackoverflow.com/questions/20109391/how-to-make-good-reproducible-pandas-examples). It would be much easier for us to provide an answer, if there was code to produce the input data instead of having to reproduce data from an image. — languitar, Apr 25 '17 at 08:57

score 2 · Accepted Answer · answered Apr 25 '17 at 10:59

You can use:

a = ['201701', '', '201705', '', '201707']
b = ['PHI', 'Actual', 'Actual', 'PHI', 'Actual']
data = [[np.nan, np.nan, np.nan, 8, np.nan]]
df = pd.DataFrame(data, index=['ClassCold'], columns = pd.MultiIndex.from_arrays([a,b]))
print (df.columns)
MultiIndex(levels=[['', '201701', '201705', '201707'], ['Actual', 'PHI']],
           labels=[[1, 0, 2, 0, 3], [1, 0, 0, 1, 0]])

print (df)
          201701        201705     201707
             PHI Actual Actual PHI Actual
ClassCold    NaN    NaN    NaN   8    NaN

Get first level of MultiIndex by get_level_values, convert to Series by to_series, replace empty strings (or space) to NaN and forward fill NaNs by ffill.

Last create new MultiIndex from_arrays:

a = df.columns.get_level_values(0).to_series().replace('',np.nan).ffill()
df.columns = df.columns = pd.MultiIndex.from_arrays([a, df.columns.get_level_values(1)])
print (df)
          201701        201705     201707
             PHI Actual Actual PHI Actual
ClassCold    NaN    NaN    NaN   8    NaN

print (df.columns)
MultiIndex(levels=[['201701', '201705', '201707'], ['Actual', 'PHI']],
           labels=[[0, 0, 1, 1, 2], [1, 0, 0, 1, 0]])

How to join 2 cell in 1 cell in header?

1 Answers1