Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

How can I map multiple row entries in df1 (name, category, amount) to one row entry per name under the respective category columns of another df2?

A first dataframe has a column containing categories which are the same as the headers of the second. There are multiple row entries for one name in df1. df2 will have 1 row entry per name. df1 has 1 row entry per category per name. All rows for one name occur in sequence in df1.
As shown:

enter image description here

The headers of df2 are as follows:

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

enter image description here

And the desired output is below:

enter image description here

How can I map data from the df1 to df2?

More specifically, how can I map multiple rows from df1 to 1 row and the respective columns of df2 in a more efficient way than looping twice to check for each category under each name?

Any help is appreciated,
Have a great day

Code:

import pandas as pds

df1 = pds.DataFrame({'Client': ['Rick', 'Rick', 'John'], 'Category': ['Service1', 'Service2', 'Service1'], 'Amount': [250, 6, 79]})


df2 = pds.DataFrame(columns = ['Client', 'Due_Date', 'Service1', 'Service2'])

output = pds.DataFrame({'Client': ['Rick', 'John'], 'Due_Date': [None,None] , 'Service1': [250, 79], 'Service2': [6, 0]})

>Solution :

This is an alternative approach using .pivot() and .assign()

df1_pivot = (df1.pivot(index='Client', columns='Category', values='Amount')
                .reset_index().assign(Due_Date=None))
df_out = df2.assign(**df1_pivot)
print(df_out)

  Client Due_Date  Service1  Service2
0   John     None      79.0       NaN
1   Rick     None     250.0       6.0
Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading