aborruso aborruso - 11 months ago 79
Python Question

pandas: first steps with merge, join, and concatenate

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.

enter image description here

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.

enter image description here

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

Answer Source

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[0]),col[1])) for col in df.columns]
#index to column
#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