Re-shaping excel data

Question

I am trying to re-shape the current excelsheet into a more proper "database" readable format. How can i do that?

I have a read a few sample: reshape a pandas dataframe with multiple columns

But the result is not working for me because of course my datasets is different. I read about melt function as well but this too is not working - probably I code it wrongly (since I am not a programmer)

Please edit the question to show what you've tried, and show a specific roadblock you're running into with [Minimal, Complete, and Verifiable example](https://stackoverflow.com/help/mcve). For more information, please see [How to Ask](https://stackoverflow.com/help/how-to-ask). — Andreas, Oct 31 '19 at 02:53
Can you explain in your result how 2nd record Oranges,Jan-19,23 line up in the first dataframe? I suspect you want melt, but you need to reset index then use that new column as the id in melt. — Scott Boston, Oct 31 '19 at 03:13
Hi Sir, the expected result is just copy paste...my code is not exactly working...i tried modify the code in the sample: c = np.array(['volume']) df.index = [df.index, c[np.arange(len(df.index)) % 1]] df = df.stack().unstack(1).reset_index() — Hakumaru, Oct 31 '19 at 03:18

score 0 · Accepted Answer · answered Oct 31 '19 at 03:20

IIUC, you need this:

import pandas as pd

df = pd.DataFrame({'Jan-19':[200,23,13]
                  ,'Feb-19':[100,200,45]
                  ,'Mar-19':[45,45,56]
                  ,'Apr-19':[487,0,56]
                  ,'May-19':[455,44,45]}
                 ,index=['Oranges','Apples','Bananas'])

print(df)



         Jan-19  Feb-19  Mar-19  Apr-19  May-19
Oranges     200     100      45     487     455
Apples       23     200      45       0      44
Bananas      13      45      56      56      45

df_out = df.reset_index().melt('index')
df_out

Output:

      index variable  value
0   Oranges   Jan-19    200
1    Apples   Jan-19     23
2   Bananas   Jan-19     13
3   Oranges   Feb-19    100
4    Apples   Feb-19    200
5   Bananas   Feb-19     45
6   Oranges   Mar-19     45
7    Apples   Mar-19     45
8   Bananas   Mar-19     56
9   Oranges   Apr-19    487
10   Apples   Apr-19      0
11  Bananas   Apr-19     56
12  Oranges   May-19    455
13   Apples   May-19     44
14  Bananas   May-19     45

Re-shaping excel data

1 Answers1