Splitting data form multiple rows into a new column in Pandas

user_13583883 0
Viewed 177

I have a Pandas dataframe which contains mixed values on a single column in the following format :

      EmpNo          Amount

      01-A           22150
      Salary         22000
      Bonus          150

      02-B           20500
      Salary         20000
      Bonus          500

      03-C           15150
      Salary         15000
      Bonus          150

How do I separate the data into different columns in the format below?


      EmpNo            Salary          Bonus          Total
      01-A             22000           150            22150
      02-B             20000           500            20500
      03-C             15000           150            15150
pandas data-analysis dataframe python
4 Answers

not the best solution but you can go with it until you find something better.

#data
index = ['01-A', 'Salary', 'Bonus', '02-B', 'Salary', 'Bonus']
amount = [22150, 22000, 150, 20500, 20000, 500]
df = pd.DataFrame(amount, index)

#extract data per variable
salary = df[df.index == 'Salary '].reset_index()
bonus = df[df.index == 'Bonus'].reset_index()
total = df[(df.index != 'Salary ') & (df.index != 'Bonus')].reset_index()

dfnew = pd.concat([salary[0], bonus[0], total[0]], axis=1)
dfnew.columns = ['salary', 'bonus', 'total']
dfnew.index = total['index']

result

user_107631190

Try this :

Move the rows that are not Salary or Bonus to another column, then you can reshape and aggregate

df["index"] = np.where(df.EmpNo.str.contains("-"), df.EmpNo, np.nan)
df["index"] = df["index"].ffill()
df = df.query("EmpNo != index").pivot("index", "EmpNo", "Amount")
df["total"] = df.sum(1)
df


EmpNo   Bonus   Salary  total
index           
01-A    150 22000   22150
02-B    500 20000   20500
03-C    150 15000   15150
user_71757130

@sammywemmy has given excellent answer

checkout below code too. Considering EmpNo values to be in sequence

new_df = pd.DataFrame({
    'EmpNo': df[df.index % 3 == 0]['EmpNo'].values,
    'Salary': df[df.index % 3 == 1]['Amount'].values,
    'Bonus': df[df.index % 3 == 2]['Amount'].values
})
new_df['Total'] = new_df.loc[:, ['Salary', 'Bonus']].sum(axis=1)
user_82301460

One more approach

a = df.unstack().reset_index(drop=True).to_frame(name='EmpNo')
new_df = a.iloc[0:7:3,:].copy()
new_df ['total'] = a.iloc[9::3,:].to_numpy().copy()
new_df ['Bonus'] = a.iloc[10::3,:].to_numpy().copy()
new_df ['Salary'] = a.iloc[11::3,:].to_numpy().copy()

output

EmpNo   total   Bonus   Salary
01-A    22150   22000   150
02-B    20500   20000   500
03-C    15150   15000   150
user_112320910
Related