Merge columns and create a new column with pandas

Question

I have a pandas framework with the following columns:

• The product's name
• Product number sold in New York (say 100)
• California product number (say 50)

It looks like:

Product     New York    California
Widget01    100         50

I want to change the frame using two location columns to create a new column like this:

Product     Location      Total Sold
Widget01    New York      100
Widget01    California    50

How to achieve this with pandas?

Answer 1

You can use pandas.melt()-

pd.melt(df,id_vars='Product', var_name='Location',value_name='Total Sold')

Demo -

In [72]: df
Out[72]:
    Product  New York  California
0  Widget01       100          50

In [73]: pd.melt(df,id_vars='Product', var_name='Location',value_name='Total Sold')
Out[73]:
    Product    Location  Total Sold
0  Widget01    New York         100
1  Widget01  California          50