have the data of rows on columns in pandas

user_18985569 0
Viewed 20

I have a dataset like the first table that has a gender value for each row.

enter image description here

I want an output table like the second screen shot that gender is not a column. And all the data for the rest of the columns are separated between "Female" and "male" with two main column with an aggregated total column. how can I get this in pandas?

enter image description here

pandas
1 Answers

You need to use pandas.pivot which is similar to how it is in Excel. The code below will give you what is required. I have renamed blanks (NaN) to 0 here.

table = pd.pivot_table(
    df,
    values='score',
    index=['job'],
    columns=['gender'],
    aggfunc=np.sum)

table.columns.name = None  # remove categories
table = table.reset_index()
table = table.fillna(0)
table ['Total Score'] = table.female + table.male
table

Output

    job       female    male    Total Score
0   Accountant  358.0   0.0     358.0
1   Engineer    123.0   230.0   353.0
2   Lawyer     320.0    103.0   423.0
3   Manager    294.0    490.0   784.0
4   Sales        0.0    82.0    82.0
5   Waiter     524.0    436.0   960.0
user_164048720
Related