I have a large datasetenter image description here

I want to transform this dataset into this formatenter image description here

I have try it through transpose but i couldn't figure out

closed as off-topic by oW_, Stephen Rauch, Siong Thye Goh, Sean Owen Oct 13 at 1:59

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "This question does not appear to be about data science, within the scope defined in the help center." – oW_, Stephen Rauch, Siong Thye Goh, Sean Owen
If this question can be reworded to fit the rules in the help center, please edit the question.

up vote 2 down vote accepted

Use pandas melt function.

##init dataframe
df = pd.DataFrame({'item': ['a', 'a', 'a', 'b', 'b', 'b'],
             'class_a': [1, 1, 2, 3, 3, 1],
              class_b': [2, 1, 2, 3, 3, 1],
             'class_c': [1, 2, 2, 3, 1, 3]})
##shape it into desired format
pd.melt(df, id_vars='item', value_vars=['class_a', 'class_b', 'class_s'])
  • but i have a large number of rows and how can i do this in large number of rows? – subash poudel Oct 11 at 12:49
  • This didnt work for you? – DaFanat Oct 11 at 13:10
  • this did not work for me i got a error and error is like this – subash poudel Oct 11 at 16:32
  • C:\Users\thinkpad\Anaconda3\lib\site-packages\pandas\core\indexing.py:1472: FutureWarning: Passing list-likes to .loc or [] with any missing label will raise KeyError in the future, you can use .reindex() as an alternative. – subash poudel Oct 11 at 16:32
  • It's warning, isn't error – Aditya Oct 11 at 18:49

I see two ways:

%%timeit
import pandas as pd
import numpy as np

df = pd.DataFrame({'item': ['a', 'b', 'c', 'd', 'e', 'f'],
             'class_a': [1, 1, 2, 3, 3, 1],
             'class_b': [2, 1, 2, 3, 3, 1],
             'class_c': [1, 2, 2, 3, 1, 3]})

df_1 = pd.melt(df, id_vars='item', value_vars=['class_a']).drop('variable', axis=1).rename(columns={'value':'class_a'})
df_2 = pd.melt(df, id_vars='item', value_vars=['class_b']).drop(['variable','item'], axis=1).rename(columns={'value':'class_b'})
df_3 = pd.melt(df, id_vars='item', value_vars=['class_c']).drop(['variable','item'], axis=1).rename(columns={'value':'class_c'})

df_finish = df_1.join(df_2.join(df_3))

timeit gave:

6.11 ms ± 310 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)

And second way:

%%timeit
import pandas as pd
import numpy as np

df = pd.DataFrame({'item': ['a', 'b', 'c', 'd', 'e', 'f'],
             'class_a': [1, 1, 2, 3, 3, 1],
             'class_b': [2, 1, 2, 3, 3, 1],
             'class_c': [1, 2, 2, 3, 1, 3]})
df = df.append(df.append(df))
df.sort_values('item', inplace=True)
df['Range'] = df.groupby((df.item != df.item.shift()).cumsum()).cumcount() + 1
table = pd.pivot_table(df, values=['class_a', 'class_b', 'class_c'], index=['item'], columns=['Range'], aggfunc=np.sum)
table = table['class_a'][[1]].join(table['class_b'][[2]]).join(table['class_c'][[3]])
table.rename(columns={1:'class_a', 2:'class_b', 3:'class_c'},inplace=True)

timeit gave next:

9.81 ms ± 520 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)
  • what is the mean of this output 9.81 ms ± 520 µs per loop (mean ± std. dev. of 7 runs, 100 loops each) ? still i didnt get answer please help me i am beginner – subash poudel Oct 11 at 16:34
  • 1
    %%timeit - this is magic command. This command give you time, for which the action is execute. If you need answer, then delete this row in code %%timeit, and will use variable table. On the whole: use my code without %%timeit. – Rudolf Morkovskyi Oct 11 at 18:05

Not the answer you're looking for? Browse other questions tagged or ask your own question.