Pandas: how to unpivot df correctly?

Question

I have the following dataframe df:

  A  B  Var    Value
0 A1 B1 T1name T1
1 A2 B2 T1name T1
2 A1 B1 T2name T2
3 A2 B2 T2name T2
4 A1 B1 T1res  1
5 A2 B2 T1res  1
6 A1 B1 T2res  2
7 A2 B2 T2res  2

I now want to 'half' my dataframe because Var contains variables that should not go under the same column. My intended outcome is:

  A  B  Name   Value
0 A1 B1 T1     1
1 A2 B2 T1     1
2 A1 B1 T2     2
3 A2 B2 T2     2

What should I use to unpivot this correctly?

so you don't want rows with Var = T1name or T2name? – eshirvana Jan 19 '22 at 16:52 — eshirvana, Jan 19 '22 at 16:52

score 1 · Answer 1 · answered Jan 19 '22 at 16:59

1

then :

df = df[~df['Var'].isin(['T1name','T2name'])]

output :

    A   B    Var Value
4  A1  B1  T1res     1
5  A2  B2  T1res     1
6  A1  B1  T2res     2
7  A2  B2  T2res     2

answered Jan 19 '22 at 16:59

eshirvana

23,227
3
22
38

score 1 · Accepted Answer · answered Jan 19 '22 at 17:06

Just filter where the string contains res and assign a new column with the first two characters of the var columns

df[df['Var'].str.contains('res')].assign(Name=df['Var'].str[:2]).drop(columns='Var')

    A   B Value Name
4  A1  B1     1   T1
5  A2  B2     1   T1
6  A1  B1     2   T2
7  A2  B2     2   T2

Note that this creates a slice of the original DataFrame and not a copy

score 1 · Answer 3 · answered Jan 19 '22 at 22:15

There are different options available looking at the df. Regex seems to be on top of the list. If regex doesn't work, maybe think of redefining your problem:

Filter Value by dtype, replace unwanted characters in df and rename columns. Code below

df[df['Value'].str.isnumeric()].replace(regex=r'res$', value='').rename(columns={'Var':'Name'})

    A   B Name Value
4  A1  B1   T1     1
5  A2  B2   T1     1
6  A1  B1   T2     2
7  A2  B2   T2     2

Pandas: how to unpivot df correctly?

3 Answers3