CODEWITHSUNDEEP
pythonpandasdataframepandas-melt
Zizzipupp
Zizzipupp

Reputation: 1361

Pandas: how to unpivot df correctly?

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?

Upvotes: 0

Views: 98

Answers (3)

wwnde
wwnde

Reputation: 26676

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

Upvotes: 1

It_is_Chris
It_is_Chris

Reputation: 14093

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

Upvotes: 1

eshirvana
eshirvana

Reputation: 24568

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

Upvotes: 1

Related Questions