Convert consecutive columns to respective rows in Pandas DataFrame

user_5647038 0
Viewed 86

Trying to convert consecutive columns to rows in pandas. Ex: Consecutive column names are sequential numbers along with some strings i.e Key1,Val1,...., KeyN,ValN in DataFrame. You can use below code to generate the dataframe.

df = pd.DataFrame({'City': ['Houston', 'Austin', 'Hoover'],'State': ['Texas', 'Texas', 'Alabama'],'Name':['Aria', 'Penelope', 'Niko'],'Key1':["test1", "test2", "test3"],'Val1':[28, 4, 7],'Key2':["test4", "test5", "test6"],
'Val2':[82, 45, 76],'Key3':["test7", "test8", "test9"],'Val3':[4, 76, 9],'Key4':["test10", "test11", "test12"],'Val4':[97, 66, 10],'Key5':["test13", "test14", "test15"],'Val5':[4, 10, '']},columns=['City', 'State', 'Name', 'Key1', 'Val1', 'Key2', 'Val2', 'Key3', 'Val3', 'Key4', 'Val4', 'Key5', 'Val5'])

enter image description here

I tried melt function as below:

df.melt(id_vars=['City', 'State'], var_name='Column', value_name='Key')

But I got the below output:

enter image description here

The problem is for every key, val column has different rows. The expected output is below:

enter image description here

pandas dataframe python
1 Answers

Use pd.wide_to_long:

pd.wide_to_long(df,['Key', 'Val'],['City', 'State', 'Name'],'No').reset_index()

Output:

       City    State      Name  No     Key Val
0   Houston    Texas      Aria   1   test1  28
1   Houston    Texas      Aria   2   test4  82
2   Houston    Texas      Aria   3   test7   4
3   Houston    Texas      Aria   4  test10  97
4   Houston    Texas      Aria   5  test13   4
5    Austin    Texas  Penelope   1   test2   4
6    Austin    Texas  Penelope   2   test5  45
7    Austin    Texas  Penelope   3   test8  76
8    Austin    Texas  Penelope   4  test11  66
9    Austin    Texas  Penelope   5  test14  10
10   Hoover  Alabama      Niko   1   test3   7
11   Hoover  Alabama      Niko   2   test6  76
12   Hoover  Alabama      Niko   3   test9   9
13   Hoover  Alabama      Niko   4  test12  10
14   Hoover  Alabama      Niko   5  test15

You are trying to simultaneously melt two columns. pd.wide_to_long handles this situation.

user_63615310
Related