Search code examples
pythonpython-3.xpandaspivotmelt

Reverse of melt and drop duplicates dataframe

I have a DataFrame like

event   entity  type
1       T1      Table
1       C1      Cell
1       C2      Cell
2       T2      Table
2       C3      Cell

I want to convert it to something like this

event   Table   Cell
1       T1      C1
1       T1      C2
2       T2      C3

This is the output of pd.melt and drop_duplicates() and I read that melt's opposite is pivot so tried to approach that way for a single event.

df.pivot_table(values='entity', index='event', columns='type')

but this throws error as DataError: No numeric types to aggregate which is obvious because pivot does a group by aggregate and doesn't replicate every combination. Any idea what exactly this kind of operation might be done with? Please note the groupby thing which I discarded for simplification.

Solution

  • Use Series.where for repalce non Table rows to NaNs and forward fill missing values, then filtere out these rows with inverted mask by ~ in boolean indexing, rename column and change ordering of columns:

    m = df['type'].eq('Table')
df['Table'] = df['entity'].where(m).ffill()
df = df[~m].rename(columns={'entity':'Cell'})[['event','Table','Cell']]
print (df)
   event Table Cell
1      1    T1   C1
2      1    T1   C2
4      2    T2   C3