pandas data transformation long-wide-long

171 views Asked by At

how to get data from this form (long representation of data):

import pandas as pd
df = pd.DataFrame({
    'c0': ['A','A','B'],
    'c1': ['b','c','d'],
    'c2': [1, 3,4]})

print(df)

Out:

   c0 c1  c2
0  A  b   1
2  A  c   3
3  B  d   4

to this form :

   c0 c1  c2
0  A  b   1
2  A  c   3
3  A  d   NaN
4  B  b   NaN
5  B  c   NaN
6  B  d   4

Is long to wide to long transformation the only approach to doing this?

1

There are 1 answers

3
piRSquared On BEST ANSWER

method 1
unstack and stack

df.set_index(['c0', 'c1']).unstack().stack(dropna=False).reset_index()

enter image description here

method 2
reindex with product

df.set_index(['c0', 'c1']).reindex(
    pd.MultiIndex.from_product([df.c0.unique(), df.c1.unique()], names=['c0', 'c1'])
).reset_index()

enter image description here