I have a dataframe like the one below, with 3 columns and 12 rows. The 12 rows are 4 repeated classes (for three time). I know that I have never values for 1A, 1D, 2B and 2D cells, and that I have always cell values for 1B, 1C, 2A and 2C cells.
I would like to transform it in something like the one you see below, in wich I combine column and row names to extract all the cells that I know will have always data inside.
In this way I will avoid unnecessary repetition or unnecessary empty cells.
I have tried to read the manual http://pandas.pydata.org/pandas-docs/stable/merging.html, but I have some difficult to take the right way. Some advice for me?
Thank you very much
You can use:
#get index to MultiIndex in column df = df.set_index(['class','date']).unstack(level=0) #remove columns with all NaN, sort index df = df.dropna(axis=1, how='all').sort_index(ascending=False) #reset MultiIndex in columns, cast int to str in first level (1,2 values) df.columns = [''.join((str(col),col)) for col in df.columns] #index to column df.reset_index(inplace=True) #reorder columns df = df[df.columns[1:].union(df.columns[:1])] print (df) 1B 1C 2A 2C date 0 1.462543 4.920529 4.496126 3.362060e+08 2016-10-31 1 3.931170 3.439862 2.453640 1.488948e+00 2016-10-30 2 3.550311 3.504713 3.224958 1.804881e+00 2016-10-29