Turn multiple columns into two new columns in a dataframe using Pandas

Question

I am working in a python pandas environment :D

Currently, I have a dataframe that looks like this :

 0   1   2   3   4   5   6   7   8   9   10   11   12   13   14   15   16   17   18
ex0 ex1 ex2 ex3 ex4 ex5 ex6 ex7 ex8 ex9 ex10 ex11 ex12 ex13 ex14 ex15 ex16 ex17 ex18

My goal is to make the dataframe look like this :

 0   1   2   3   4   5  6    7   8   category   amount   
ex0 ex1 ex2 ex3 ex4 ex5 ex6 ex7 ex8     9        ex9
ex0 ex1 ex2 ex3 ex4 ex5 ex6 ex7 ex8     10       ex10
ex0 ex1 ex2 ex3 ex4 ex5 ex6 ex7 ex8     11       ex11
ex0 ex1 ex2 ex3 ex4 ex5 ex6 ex7 ex8     12       ex12
ex0 ex1 ex2 ex3 ex4 ex5 ex6 ex7 ex8     13       ex13
ex0 ex1 ex2 ex3 ex4 ex5 ex6 ex7 ex8     14       ex14
ex0 ex1 ex2 ex3 ex4 ex5 ex6 ex7 ex8     15       ex15
ex0 ex1 ex2 ex3 ex4 ex5 ex6 ex7 ex8     16       ex16
ex0 ex1 ex2 ex3 ex4 ex5 ex6 ex7 ex8     17       ex17
ex0 ex1 ex2 ex3 ex4 ex5 ex6 ex7 ex8     18       ex18

Basically, I want the last 9 column titles and values to become their own rows on 2 new columns while keeping the first 8 columns and rows the same. I am aware that this means the data will be duplicated.

I saw some other answers on stackoverflow use the following code for smaller dataframes but it hasn't worked for me :

df.melt(['Type', 'Class'], var_name='Date', value_name='Value')

(df.set_index(['Type', 'Class'])
   .stack()
   .rename_axis(['Type', 'Class', 'Date'])
   .reset_index(name='Value')
)

Any and all help is appreciated ! Thank you

Andy L. · Accepted Answer · 2020-08-14 18:54:53Z

4

You are almost there with melt

df.melt(id_vars=df.columns[:9], var_name='category', value_name='amount')

Out[469]:
     0    1    2    3    4    5    6    7    8 category amount
0  ex0  ex1  ex2  ex3  ex4  ex5  ex6  ex7  ex8        9    ex9
1  ex0  ex1  ex2  ex3  ex4  ex5  ex6  ex7  ex8       10   ex10
2  ex0  ex1  ex2  ex3  ex4  ex5  ex6  ex7  ex8       11   ex11
3  ex0  ex1  ex2  ex3  ex4  ex5  ex6  ex7  ex8       12   ex12
4  ex0  ex1  ex2  ex3  ex4  ex5  ex6  ex7  ex8       13   ex13
5  ex0  ex1  ex2  ex3  ex4  ex5  ex6  ex7  ex8       14   ex14
6  ex0  ex1  ex2  ex3  ex4  ex5  ex6  ex7  ex8       15   ex15
7  ex0  ex1  ex2  ex3  ex4  ex5  ex6  ex7  ex8       16   ex16
8  ex0  ex1  ex2  ex3  ex4  ex5  ex6  ex7  ex8       17   ex17
9  ex0  ex1  ex2  ex3  ex4  ex5  ex6  ex7  ex8       18   ex18

answered Aug 14, 2020 at 18:54

Andy L.

25.3k4 gold badges20 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

V1cst3r Over a year ago

Hello ! I realized that the melt I was doing was correct, but it was my column names that were wrong and thus i was misunderstanding my problem. I am embarassed 😭. Thank you for the confirmation though !

Andy L. Over a year ago

@V1cst3r: you are welcome. There is nothing to embarrass about. All programmers got hit with the same issue, myself included. Cheers :)

Henry Yik · Accepted Answer · 2020-08-14 18:53:52Z

1

Just melt it:

print (df.melt([i for i in df.columns if int(i)<9], var_name="category", value_name="amount"))

     0    1    2    3    4    5    6    7    8 category amount
0  ex0  ex1  ex2  ex3  ex4  ex5  ex6  ex7  ex8        9    ex9
1  ex0  ex1  ex2  ex3  ex4  ex5  ex6  ex7  ex8       10   ex10
2  ex0  ex1  ex2  ex3  ex4  ex5  ex6  ex7  ex8       11   ex11
3  ex0  ex1  ex2  ex3  ex4  ex5  ex6  ex7  ex8       12   ex12
4  ex0  ex1  ex2  ex3  ex4  ex5  ex6  ex7  ex8       13   ex13
5  ex0  ex1  ex2  ex3  ex4  ex5  ex6  ex7  ex8       14   ex14
6  ex0  ex1  ex2  ex3  ex4  ex5  ex6  ex7  ex8       15   ex15
7  ex0  ex1  ex2  ex3  ex4  ex5  ex6  ex7  ex8       16   ex16
8  ex0  ex1  ex2  ex3  ex4  ex5  ex6  ex7  ex8       17   ex17
9  ex0  ex1  ex2  ex3  ex4  ex5  ex6  ex7  ex8       18   ex18

answered Aug 14, 2020 at 18:53

Henry Yik

22.6k5 gold badges21 silver badges44 bronze badges

1 Comment

V1cst3r Over a year ago

Thank you for this answer ! I will be accepting Andy's answer though because it accomplishes the same task without a for loop. Cheers !

maxbear123 · Accepted Answer · 2020-08-14 19:10:19Z

0

1st, I would make the second dataframe (under a different name) minus the last two columns, so that the dimensions are correct. You could do this by looping through with this command, after having declared an empty df of the correct size (minus the last two columns):

dataFrame.set_value(index, col, value, takeable=False)

Or you could just make lists with the data you want in the first few columns and make a dictionary to declare the new dataframe and then use that.

Then I would run this to copy the other two columns over.

cats=[cat for cat in df1.columns][-10:] 
row1_section=df1.loc[0][-10:]
df2['category'] = [cat for cat in cats]
df2['amount']=[example for example in row1_section]

answered Aug 14, 2020 at 19:10

maxbear123

2022 silver badges11 bronze badges

Collectives™ on Stack Overflow

Turn multiple columns into two new columns in a dataframe using Pandas

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related