python pandas simple append-CodePudding

My pandas dataframe is something like this table/image bellow:

product_id  product_name  package_mod  package_50g  price_50g  package_100g  price_100g
2017210     potato        1KG          50g          0.1695     100g          0.339
2017081     onion         1KG          50g          0.1795     100g          0.359
2017217     lemon         1KG          50g          0.1395     100g          0.279

I want this output, an append through "product_id" series.

I tried almost anything, like pd.merge, pd.melt, pd.append etc. There is something that I'm missing, someone can help me with this matter?

I know that I can do it manually, by merging each product_id one by one, but I have several columns like these, so I think its not an option =/

Regards :)

CodePudding user response：

You are looking to pd.wide_to_long:

out = pd.wide_to_long(df.rename(columns={'price': 'price_mod'}),
                      stubnames=['package', 'price'],
                      i=['product_id', 'product_name'],
                      j='dummy', sep='_', suffix=r'\w ') \
        .droplevel(level='dummy').reset_index()

Output:

>>> out
   product_id product_name package   price
0     2017210       potato     1KG  3.3900
1     2017210       potato     50g  0.1695
2     2017210       potato    100g  0.3390
3     2017081        onion     1KG  3.5900
4     2017081        onion     50g  0.1795
5     2017081        onion    100g  0.3590
6     2017217        lemon     1KG  2.7900
7     2017217        lemon     50g  0.1395
8     2017217        lemon    100g  0.2790