CODEWITHSUNDEEP
pythonpandas
Shi Jie Tio
Shi Jie Tio

Reputation: 2529

How to join 2 cell in 1 cell in header?

enter image description here

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

enter image description here

Anyone have idea on this?

Upvotes: 0

Views: 69

Answers (1)

jezrael
jezrael

Reputation: 863216

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]])

Upvotes: 2

Related Questions