How to transpose and autofill a dataframe with python?

Question

I have a dataframe like below:

NaN    1/1/2018    2/1/2018
item1    1    2
item2    3    4

I would like to transform the dataframe to make it look like this:

Date    Item    Price
1/1/2018    item1    1
1/1/2018    item2    3
2/1/2018    item1    2
2/1/2018    item2    4

I have tried df.pivot and df.t but neither works. Any suggestions or hints are very much appreciated!

Thanks!

df.stack().reset_index(), assuming that item1, item2 is currently the index — user3483203
– user3483203, Commented Sep 24, 2018 at 18:00

mVChr · Accepted Answer · 2018-09-24 18:05:33Z

4

You can use pd.melt like so:

pd.melt(DF, id_vars=['NaN'], var_name='Date', value_name='Price')

     NaN      Date  Price
0  item1  1/1/2018      1
1  item2  1/1/2018      3
2  item1  2/1/2018      2
3  item2  2/1/2018      4

answered Sep 24, 2018 at 18:05

mVChr

50.3k11 gold badges111 silver badges105 bronze badges

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

3 Comments

k2333 Over a year ago

Thanks for your help! I tried your method but I got an error as NaN is actually a blank cell. I am very new to pandas so I guess I will need to name those columns first?

mVChr Over a year ago

I assume it's because your first column is the index? You can try df.reset_index(level=0, inplace=True) and then id_vars=['index'] and that should work.

k2333 Over a year ago

I have followed your code and I got 'TypeError: argument of type 'int' is not iterable'. I guess I can get the logic of solving the task but there may exist other format issue(maybe?) to be taken care of first? Thanks in advance for your help!

Brian · Accepted Answer · 2018-09-24 18:05:26Z

2

You can do this with pd.melt() like so:

pd.melt(df, id_vars = ['NaN'])

This results in:

answered Sep 24, 2018 at 18:05

Brian

2,3261 gold badge18 silver badges29 bronze badges

1 Comment

k2333 Over a year ago

Could you share the way to name those columns? I am new to pandas and I tried df2.columns=['Date','Item','Price'] but I got NaN in the column rather than in the header. Thanks!

Collectives™ on Stack Overflow

How to transpose and autofill a dataframe with python?

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related