I have 2 columns with list values in my data frame as shown below:
salary.labels salary.percentages
['Not Impacted', 'Salary Not Paid', 'Salary Cut', 'Variables Impacted', 'Appraisal Delayed'] [29, 0.9, 2.2, 11.3, 56.6]
['Not Impacted', 'Salary Not Paid', 'Salary Cut', 'Variables Impacted', 'Appraisal Delayed'] [74.5, 1.1, 1.4, 12, 11]
['Not Impacted', 'Salary Not Paid', 'Salary Cut', 'Variables Impacted', 'Appraisal Delayed'] [63.4, 1.9, 2.2, 11.2, 21.3]
['Not Impacted', 'Salary Not Paid', 'Salary Cut', 'Variables Impacted', 'Appraisal Delayed'] [58.3, 0.6, 1.9, 7.9, 31.3]
['Not Impacted', 'Salary Not Paid', 'Salary Cut', 'Variables Impacted', 'Appraisal Delayed'] [80.4, 1.4, 2.2, 4.7, 11.3]
['Not Impacted', 'Salary Not Paid', 'Salary Cut', 'Variables Impacted', 'Appraisal Delayed'] [71.2, 0.9, 1.2, 6.3, 20.4]
['Not Impacted', 'Salary Not Paid', 'Salary Cut', 'Variables Impacted', 'Appraisal Delayed'] [39.9, 1.6, 5.8, 15.8, 36.9]
['Not Impacted', 'Salary Not Paid', 'Salary Cut', 'Variables Impacted', 'Appraisal Delayed'] [56.5, 0.8, 2.3, 9.8, 30.6]
['Not Impacted', 'Salary Not Paid', 'Salary Cut', 'Variables Impacted', 'Appraisal Delayed'] [42.9, 2.3, 5.1, 14.1, 35.6]
I wish to create new columns such that the column labels will take values of salary.labels column & the column values in each row will take the corresponding value from the salary.percentages column.
The anticipated output data frame looks like this:
'Not Impacted' 'Salary Not Paid' 'Salary Cut' 'Variables Impacted' 'Appraisal Delayed'
29, 0.9, 2.2, 11.3, 56.6
74.5, 1.1, 1.4, 12, 11
63.4, 1.9, 2.2, 11.2, 21.3
58.3, 0.6, 1.9, 7.9, 31.3
80.4, 1.4, 2.2, 4.7, 11.3
71.2, 0.9, 1.2, 6.3, 20.4
39.9, 1.6, 5.8, 15.8, 36.9
56.5, 0.8, 2.3, 9.8, 30.6
42.9, 2.3, 5.1, 14.1, 35.6
How to perform this with pandas operations?
If all lists in salary.labels are same, use DataFrame constructor with convert second column to lists and columns by first row of salary.labels:
df = pd.DataFrame(df['salary.percentages'].tolist(), columns=df['salary.labels'].iloc[0])
print (df)
Not Impacted Salary Not Paid Salary Cut Variables Impacted \
0 29.0 0.9 2.2 11.3
1 74.5 1.1 1.4 12.0
2 63.4 1.9 2.2 11.2
3 58.3 0.6 1.9 7.9
4 80.4 1.4 2.2 4.7
5 71.2 0.9 1.2 6.3
6 39.9 1.6 5.8 15.8
7 56.5 0.8 2.3 9.8
8 42.9 2.3 5.1 14.1
Appraisal Delayed
0 56.6
1 11.0
2 21.3
3 31.3
4 11.3
5 20.4
6 36.9
7 30.6
8 35.6