-1

EDIT: Question answered see my code below for the answer. I forgot to add

df = df.melt(id_vars=['Gender', 'Length of service', 'Team'],
        value_vars=Questions,
        var_name='Question',
        value_name='Result')

I have been trying to use the melt function within Pandas with limited effect.

I'm trying to get 36 questions which are separated out into columns into rows 36 for each ID.

import pandas as pd

Questions = ['How likely are you to you recommend the company as a place to work to a friend or colleague? ',
 'How satisfied are you in working at the company?','My work gives me a feeling of personal accomplishment',
 'I know what is expected of me and I have clearly defined goals','I have the tools and resources to do my job well',
 'My job makes good use of my skills and abilities',
 'I receive the training I need to do my job well',
 'There are adequate opportunities for career growth in this organisation',
 'I understand the business priorities and how my job can help',
 'I get the right level of support from my Manager',
 'I receive useful and constructive feedback from my Manager',
 'I receive the praise and recognition I deserve from my Manager',
 'My Manager is interested in my professional development and advancement',
 'My Manager treats everyone in the team fairly',
 'My Manager listens to what I\'m saying',
 'I feel comfortable voicing my concerns to my Manager',
 'My Manager keeps me well informed of what is happening',
 'I have confidence in the Executive Team to lead the company',
 'The Executive Team listens to and responds to the needs of employees',
 'I feel I can easily approach and communicate with members of the Executive Team',
 'The Executive Team at the company leads by example',
 'The Executive Team keeps the company well informed of what is happening',
 'This Company supports a good work life balance',
 'The pace of the work in this Company enables me to do a good job',
 'The amount of work I am asked to do is reasonable',
 'My job does not cause unreasonable amounts of stress in my life',
 'My team work well together and support each other when needed',
 'Everybody is treated fairly in this Company',
 'Poor performance is effectively addressed throughout this Company',
 'I can disagree with my manager without fear of getting in trouble',
 'I am comfortable sharing my opinions at work','Diversity is valued at the company',
 'I am proud to work for the company','I am paid fairly for the work I do',
 'My salary is competitive with similar jobs I might find elsewhere',
 'My benefits are comparable to those offered by other Companies']

df = pd.read_excel(r'Survey.xlsx')

df.melt(id_vars=['Gender', 'Length of service', 'Team'],
        value_vars=Questions,
        var_name='Question',
        value_name='Result')

df.to_csv(r'FINAL_OUTPUT.csv', index=False)

However my above code doesn't seem to have any effect. I have attached a .zip file with the Survey.xlsx and associated code. I've randomized the data so nothing sensitive is in there.

Any ideas?

Thanks.

Ryan Davies
  • 446
  • 1
  • 4
  • 13
  • 1
    Please provide a **[mcve]**. That means *just text*, no images or links. – jpp Oct 15 '18 at 10:38
  • Hi jpp I have amended the post to make it more informative and to the guidelines. – Ryan Davies Oct 15 '18 at 10:46
  • Your dataframe needs to include some example data (a few rows would suffice, but enough to exhibit your issue). As you can see, `df = pd.read_csv(r'RAW.csv')` will fail for us. – jpp Oct 15 '18 at 10:49
  • I have included a hyperlink to some sample data within the post, will this be enough? https://megaupload.nz/z7Ydebjebb/RAW_csv – Ryan Davies Oct 15 '18 at 10:51
  • Moved it further up for visibility – Ryan Davies Oct 15 '18 at 10:52
  • No, you shouldn't include links. See [How to make good reproducible pandas examples](https://stackoverflow.com/questions/20109391/how-to-make-good-reproducible-pandas-examples) for advice on how to include data without images/links. – jpp Oct 15 '18 at 10:55
  • 1
    I think need `df1 = df.melt(['Gender', 'Length of service', 'Team'])` – jezrael Oct 15 '18 at 10:55
  • Ok I've completely changed the question and title. I'm not sure how I can make a sample DataFrame as the data is quite big and varied! – Ryan Davies Oct 15 '18 at 13:24

1 Answers1

1

Use melt:

df.melt(id_vars=['Category A', 'Category B', 'Category C'],
        value_vars=['Q1', 'Q2', 'Q3'],
        var_name='Question',
        value_name='Result')

And, you can manually add all to Qs or use:

df.melt(id_vars=['Category A', 'Category B', 'Category C'],
        value_vars=df.columns[df.columns.str.startswith('Q')],
        var_name='Question',
        value_name='Result')

If only desired columns start with Q.

zipa
  • 27,316
  • 6
  • 40
  • 58
  • Thanks for this Zipa, I think Melt is what I need to use. I have edited my question to better reflect my problem. – Ryan Davies Oct 15 '18 at 13:14