panda-melt is not transposing columns into a sequance-CodePudding

I read a csv it has multiples columns. I want to transpose

Date                A        B            C
   25/5/2019       25      765.3        896
   26/5/2019       98                  769 
   27/5/2019      27.6    453.2       98.6

I have tried

df2 = pd.melt(df, id_vars=['DateTime'],value_vars=['A','B','C'],
                  var_name='detail',value_name='')
df2 = df2.sort_values(['DateTime'])

it is changing columns into rows but I am having two problems.

it is also adding column name along with the value which I does not want. I just need values
there is no order of columns. I want to transpose col in a sequence.

Current output is

Date            final values        
 25/5/2019      A 25                
 25/5/2019      C 896                    
 25/5/2019       B 765.3                  
 26/5/2019       B                    
 26/5/2019       A 253  
 26/5/2019       C 769  
 27/5/2019       C 506

I want to transpose col in a sequence always start with A then B and then C. Expected output is.

Date            final values        
 25/5/2019       25                
 25/5/2019       765.3                    
 25/5/2019       896                   
 26/5/2019       98                    
 26/5/2019       null 
 26/5/2019       769
 27/5//2019      203

Any help would be appreciated. Thanks in advance

An additional point I have now required output column final value. I need to assign that each value belongs to which column for this I have code for three columns. for example A=100, B=200, C=100. Is there any way that I can print codes next to each columns value

Date            final values    code       
 25/5/2019       25              100  
 25/5/2019       765.3           200         
 25/5/2019       896             300      
 26/5/2019       98              100      
 26/5/2019       null            200
 26/5/2019       769              300
 27/5//2019      203             100

CodePudding user response：

Alternative solution with DataFrame.stack and removeinf second level from columns names by DataFrame.droplevel:

df = (df.set_index('DateTime')[['A','B','C']]
        .stack(dropna=False)
        .droplevel(1)
        .reset_index(name='final values'))
print (df)
    DateTime  final values
0  25/5/2019          25.0
1  25/5/2019         765.3
2  25/5/2019         896.0
3  26/5/2019          98.0
4  26/5/2019           NaN
5  26/5/2019         769.0
6  27/5/2019          27.6
7  27/5/2019         453.2
8  27/5/2019          98.6

CodePudding user response：

You can use the underlying array for a flexible reshaping:

pd.DataFrame({'Date': df['Date'].repeat(df.shape[1]-1),
              'final values': df.set_index('Date').to_numpy().ravel()})

or:

pd.DataFrame({'Date': df['Date'].repeat(df.shape[1]-1),
              'final values': df[['A', 'B', 'C']].to_numpy().ravel()})

output:

        Date final values
0  25/5/2019         25.0
0  25/5/2019        765.3
0  25/5/2019        896.0
1  26/5/2019         98.0
1  26/5/2019          NaN
1  26/5/2019        769.0
2  27/5/2019         27.6
2  27/5/2019        453.2
2  27/5/2019         98.6

CodePudding user response：

An hybrid version of the two previous answers with melt. You just have to transpose your dataframe before melt to keep your values ordered:

out = (
    df.set_index('Date').T.melt(value_name='final value', ignore_index=False) \
      .assign(code=lambda x: x.index.map({'A': 100, 'B': 200, 'C': 300}))
      .reset_index(drop=True)
)
print(out)

# Output
        Date final value  code
0  25/5/2019        25.0   100
1  25/5/2019       765.3   200
2  25/5/2019       896.0   300
3  26/5/2019        98.0   100
4  26/5/2019         NaN   200
5  26/5/2019       769.0   300
6  27/5/2019        27.6   100
7  27/5/2019       453.2   200
8  27/5/2019        98.6   300