Merge columns and create new column with pandas

Question

I have a pandas dataframe with the following columns:

1. Product name
2. Number of product sold in New York (let's say 100)
3. Number of product sold in California (let's say 50)

Looks like this:

Product     New York    California
Widget01    100         50

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

Product     Location      Total Sold
Widget01    New York      100
Widget01    California    50

How does one 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