Get row values as column values [closed]

Question

I have a single row data-frame like below

Num     TP1(USD)    TP2(USD)    TP3(USD)    VReal1(USD)     VReal2(USD)     VReal3(USD)     TiV1 (EUR)  TiV2 (EUR)  TiV3 (EUR)  TR  TR-Tag
AA-24   0       700     2100    300     1159    2877    30       30     47      10  5

I want to get a dataframe like the one below

ID  Price   Net     Range
1   0       300     30
2   700     1159    30
3   2100    2877    47

The logic here is that a. there will be 3 columns names that contain TP/VR/TV. So in the ID, we have 1, 2 & 3 (these can be generated by extracting the value from the column names or just by using a range to fill) b. TP1 value goes into first row of column 'Price',TP2 value goes into second row of column 'Price' & so on c. Same for VR & TV. The values go into 'Net' & 'Range columns d. Columns 'Num', 'TR' & 'TR=Tag' are not relevant for the result.

I tried df.filter(regex='TP').stack(). I get all the 'TP' column & I can access individual values be index ([0],[1],[2]). I could not get all of them into a column directly.

I also wondered if there may be a easier way of doing this.

Answer 1

Assuming 'Num' is a unique identifier, you can use pandas.wide_to_long:

pd.wide_to_long(df, stubnames=['TP', 'VR', 'TV'], i='Num', j='ID')

or, for an output closer to yours:

out = (pd
 .wide_to_long(df, stubnames=['TP', 'VR', 'TV'], i='Num', j='ID')
 .reset_index('ID')
 .drop(columns=['TR', 'TR-Tag'])
 .rename(columns={'TP': 'Price', 'VR': 'Net', 'TV': 'Range'})
 )

output:

       ID  Price   Net  Range
Num                          
AA-24   1      0   300     30
AA-24   2    700  1159     30
AA-24   3   2100  2877     47

updated answer

out = (pd
 .wide_to_long(df.set_axis(df.columns.str.replace(r'\(USD\)$', '', regex=True),
                           axis=1),
               stubnames=['TP', 'VReal', 'TiV'], i='Num', j='ID')
 .reset_index('ID')
 .drop(columns=['TR', 'TR-Tag'])
 .rename(columns={'TP': 'Price', 'VReal': 'Net', 'TiV': 'Range'})
 )

output:

       ID  Price   Net  Range
Num                          
AA-24   1      0   300     30
AA-24   2    700  1159     30
AA-24   3   2100  2877     47