CODEWITHSUNDEEP
pythonpandas
Ronnie
Ronnie

Reputation: 391

Pandas melt to get multiple variable and value columns

I have a Pandas DataFrame

data = {
"SomeCol":[1,2],
"Group1":['x','y'],
"Group2":['a','b'],
"SomeAgg":[100,200]
}
df = pd.DataFrame(data)

   SomeCol Group1 Group2  SomeAgg
0        1      x      a      100
1        2      y      b      200

How can I get this:

   SomeCol    Var1    Var2 Var1_value Var2_value  SomeAgg
0        1  Group1  Group2          x          a      100
1        2  Group1  Group2          y          b      200

I have tried pd.melt function which gives only one variable and one value columns.

Any help would be appreciated

Upvotes: 1

Views: 4234

Answers (3)

heena bawa
heena bawa

Reputation: 828

The solution can also be achieved without using pd.melt:

data = {
"SomeCol":[1,2],
"Group1":['x','y'],
"Group2":['a','b'],
"SomeAgg":[100,200]
}
df = pd.DataFrame(data)
col_names = ['Group1', 'Group2']
df['Var1'], df['Var2'] = col_names[0], col_names[1]
df = df.rename(columns={col_names[0]: 'Var1_Value', col_names[1]: 'Var2_Value'})

print(df)

   SomeCol Var1_Value Var2_Value  SomeAgg    Var1    Var2
0        1          x          a      100  Group1  Group2
1        2          y          b      200  Group1  Group2

Upvotes: 1

Akhilesh_IN
Akhilesh_IN

Reputation: 1317

import pandas as pd

data = {
"SomeCol":[1,2],
"Group1":['x','y'],
"Group2":['a','b'],
"SomeAgg":[100,200]
}
df = pd.DataFrame(data)

s1 = df.melt(id_vars=['SomeCol','SomeAgg'], value_vars=['Group1'],var_name='Var1', value_name='Var1_value')
s2 = df.melt(id_vars=['SomeCol'], value_vars=['Group2'],var_name='Var2', value_name='Var2_value')
ls = ['SomeCol','Var1','Var1','Var1_value', 'Var2_value','SomeAgg']
result = s1.merge(s2,on='SomeCol')
print(result[ls])

Upvotes: 0

NoSplitSherlock
NoSplitSherlock

Reputation: 615

I think you're trying to multi index. This code comes straight from the documentation:
https://pandas.pydata.org/pandas-docs/stable/user_guide/cookbook.html#cookbook-multi-index 

   row  One_X  One_Y  Two_X  Two_Y
0    0    1.1    1.2   1.11   1.22
1    1    1.1    1.2   1.11   1.22
2    2    1.1    1.2   1.11   1.22

# As Labelled Index
In [76]: df = df.set_index('row')

In [77]: df
Out[77]: 
     One_X  One_Y  Two_X  Two_Y
row                            
0      1.1    1.2   1.11   1.22
1      1.1    1.2   1.11   1.22
2      1.1    1.2   1.11   1.22

# With Hierarchical Columns
In [78]: df.columns = pd.MultiIndex.from_tuples([tuple(c.split('_'))
   ....:                                         for c in df.columns])
   ....: 

In [79]: df
Out[79]: 
     One        Two      
       X    Y     X     Y
row                      
0    1.1  1.2  1.11  1.22
1    1.1  1.2  1.11  1.22
2    1.1  1.2  1.11  1.22

Upvotes: 0

Related Questions