Transpose dataframe except one column of timestamp

user_10506347 0
Viewed 111

I have a pretty complicated Dataframe like this

da = [['tom',"Mar '20" ,10,1,10,67], ['nick',"Apr '20", 15,7687,665,988], \
    ['juli',"Jun '20", 14,90,73,23]\
    ,['juli',"Jul '20", 14,990,273,623]] 
df = pd.DataFrame(da, columns = ['var', 'date','x','y','z','a'])

I have tried melt function but as i have duplicate values in "var" I am not able to use it What I want is transpose with "var" but with no duplicated columns and keeping date as it is

This is expected Output

Output

pandas data-manipulation dataframe python-3.x python
1 Answers

For correct ordering is converted to datetimes by to_datetime, then reshape by DataFrame.set_index with DataFrame.stack and Series.unstack, last convert date to column ad convert to original format:

df['date'] = pd.to_datetime(df['date'], format="%b '%y")
df = (df.set_index(['var','date'])
        .stack()
        .unstack(0)
        .reset_index(level=0)
        .rename_axis(None, axis=1))
df['date'] = df['date'].dt.strftime("%b '%y")
print (df)
      date   juli    nick   tom
x  Mar '20    NaN     NaN  10.0
y  Mar '20    NaN     NaN   1.0
z  Mar '20    NaN     NaN  10.0
a  Mar '20    NaN     NaN  67.0
x  Apr '20    NaN    15.0   NaN
y  Apr '20    NaN  7687.0   NaN
z  Apr '20    NaN   665.0   NaN
a  Apr '20    NaN   988.0   NaN
x  Jun '20   14.0     NaN   NaN
y  Jun '20   90.0     NaN   NaN
z  Jun '20   73.0     NaN   NaN
a  Jun '20   23.0     NaN   NaN
x  Jul '20   14.0     NaN   NaN
y  Jul '20  990.0     NaN   NaN
z  Jul '20  273.0     NaN   NaN
a  Jul '20  623.0     NaN   NaN
user_29010020
Related