0

I have a dataframe like:

id item_1 item_2 item_3 price_1 price_2 price_3 note
user1 apple orange water 2.00 1.50 0.80 nothing
user2 banana milk apple 0.50 3.50 2.00 1 bag

and I want to make it into something like:

id item price note number
user1 apple 2.00 nothing 1
user1 orange 1.50 nothing 2
user1 water 0.80 nothing 3
user2 banana 0.50 1 bag 1
user2 milk 3.50 1 bag 2
user2 apple 2.00 1 bag 3

Is it possible to do it without having to manually input those "item_"s and "price_" (since there will be quite a few of them)?

Grumpy Civet
  • 375
  • 1
  • 7

1 Answers1

2

Use wide_to_long with specified parameters:

df = pd.wide_to_long(df, 
                     stubnames=['item','price'], 
                     i=['id','note'], 
                     j='number', sep='_').reset_index()
print (df)
      id     note  number    item  price
0  user1  nothing       1   apple    2.0
1  user1  nothing       2  orange    1.5
2  user1  nothing       3   water    0.8
3  user2    1 bag       1  banana    0.5
4  user2    1 bag       2    milk    3.5
5  user2    1 bag       3   apple    2.0

Or convert all not _ columns first, split all columns with _ and reshape by DataFrame.stack:

df1 = df.set_index(['id','note'])
df1.columns = df1.columns.str.split('_', expand=True)
df1 = df1.rename_axis((None, 'number'), axis=1).stack().reset_index()
print (df1)
      id     note number    item  price
0  user1  nothing      1   apple    2.0
1  user1  nothing      2  orange    1.5
2  user1  nothing      3   water    0.8
3  user2    1 bag      1  banana    0.5
4  user2    1 bag      2    milk    3.5
5  user2    1 bag      3   apple    2.0
jezrael
  • 822,522
  • 95
  • 1,334
  • 1,252
  • thanks so much @jezrael! the second method works perfectly! but for some reason, wide_to_long gives me an empty df... – Grumpy Civet Mar 01 '21 at 14:55