Combine multiple rows into Single row using particular column in python

user_2555874 0
Viewed 636

I need to combine both records into single record. I have a Billable column value as 'Yes' and 'No'

If Value is 'Yes' then it will sum row-wise and created new columns as 'Billable'

Billing = df[Billing] * sum/168 * 100

If value is 'No' then it will be sum row-wise and created new column as 'Non-Billable'.

Non-Billing = df[Non-Billing] * sum/ 168 * 100

Data:

|Employee Name  |    Java  |  Python  | .Net  |  React |  Billable|                                  
|Priya          |    10    |          |   5   |        |  Yes     |                     
|Priya          |          |  10      |       |     5  |  No      |  
|Krithi         |          |   10     |   20  |        |  No      |

Output

Priya is in both billable and non-billable, priya name appears in two rows. I need to merge in single row with Employee Name. So expected output should be

| Employee Name |   Java    | Python    |.Net   | React | Total       |Billing      | Non-Billing|     
| Priya         |   10      | 10        | 5     | 5     | 30          |8.928571429  | 8.928571429|     
| Krithi        |   10      | 20        |       |       | 30          |             | 17.85714286|

Expected Output billable and non-billable data should be in single row.

Expected Output

But I am getting output in two rows Billable and Non-Billable

Current Output i am getting

total=df.sum(axis=1)
df.insert(len(df.columns),column='Total',value=total)

x = (Billable_cal.loc[:,:].sum(axis=1)/available*100).round(2)
df["Billing"] = np.where(df["Billable"] == "Yes", x, "")
df["Non Billing"] = np.where(df["Billable"] == "No", x, "")
pandas dataframe merge python-3.x numpy
1 Answers

First aggregate by Employee Name names to df1, then aggregate by Billable with numpy.where, for sum per both columns, sum by all columns, reshape by Series.unstack, divide and multiple and last add to df1 for final ouput:

df1 = df.groupby('Employee Name', sort=False).sum(min_count=1)

df['Billable'] = np.where(df['Billable'] == 'Yes', 'Billing','Non-Billing')

df2 = (df.groupby(['Employee Name','Billable'])
        .sum()
        .sum(axis=1)
        .unstack()
        .div(168)
        .mul(100))

df = df1.join(df2).reset_index()
print (df)
  Employee Name  Java  Python  .Net  React   Billing  Non-Billing
0         Priya  10.0    10.0   5.0    5.0  8.928571     8.928571
1        Krithi   NaN    10.0  20.0    NaN       NaN    17.857143
user_29010020
Related