1

DataFrame1:

Device  MedDescription  Quantity

RWCLD   Acetaminophen (TYLENOL) 325 mg Tab  54
RWCLD   Ampicillin Inj (AMPICILLIN) 2 g Each    13
RWCLD   Betamethasone Inj *5mL* (CELESTONE SOLUSPAN) 30 mg (5 mL) Each  2
RWCLD   Calcium Carbonate Chew (500mg) (TUMS) 200 mg Tab    17
RWCLD   Carboprost Inj *1mL* (HEMABATE) 250 mcg (1 mL) Each 5
RWCLD   Chlorhexidine Gluc Liq *UD* (PERIDEX/PERIOGARD) 0.12 % (15 mL) Each 5

Data Frame2: 

Device  DrwSubDrwPkt    MedDescription  BrandName   MedID   PISAlternateID  CurrentQuantity Min Max StandardStock   ActiveOrders    DaysUnused

RWC-LD  RWC-LD_MAIN Drw 1-Pkt 12    Mag/AlOH/Smc 200-200-20/5 *UD* (MYLANTA/MAALOX) (30 mL) Each    MYLANTA/MAALOX  A03518  27593   7   4   10  N   Y   3
RWC-LD  RWC-LD_MAIN Drw 1-Pkt 20    ceFAZolin in Dextrose(ISO-OS) (ANCEF/KEFZOL) 1 g (50 mL) Each   ANCEF/KEFZOL    A00984  17124   6   5   8   N   N   2
RWC-LD  RWC-LD_MAIN Drw 1-Pkt 22    Clindamycin Phosphate/D5W (CLEOCIN) 900 mg (50 mL) IV Premix    CLEOCIN A02419  19050   7   6   8   N   N   2

What I want to do is append DataFrame2 values to Data Frame 1 ONLY if the 'MedDescription' matches. When it find the match, I would like to add only certain columns from dataFrame2[Min,Max,Days Unused] which are all integers

I had an iterative solution where I access the dataframe 1 object 1 row at a time and then check for a match with dataframe 2, once found I append the column numbers from there to the original dataFrame.

Is there a better way? It is making my computer slow to a crawl as I have thousands upon thousands of rows.

Improve this question

3 Answers 3

Reset to default
1

It sounds like you want to merge the target columns ('MedDescription', 'Min', 'Max', 'Days Unused') to df1 based on a matching 'MedDescription'.

I believe the best way to do this is as follows:

target_cols = ['MedDescription', 'Min', 'Max', 'Days Unused']
df1.merge(df2[target_cols], on='MedDescription', how='left')

how='left' ensures that all the data in df1 is returned, and only the target columns in df2 are appended if MedDescription matches.

Note: It is easier for others if you copy the results of df1/df2.to_dict(). The data above is difficult to parse.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

i tried this, it worked, but it changed the index from the UNIT itself to numbers
0

This sounds like an opportunity to use Pandas' built-in functions for joining datasets - you should be able to join on MedDescription with a the desired columns from DataFrame2. The join function in Pandas is very efficient, and should far outperform your method of looping through.

Pandas has documentation on merging datasets that includes some good examples, and you can find ample literature on the concepts of joins in SQL tutorials.

Improve this answer

2 Comments

yeah I looked at that doc, but that had values being merged based on index, unfortunately for me, my dataframe has a constant index(the device name), is it possible to have 2 indexes?
got it to work pd.merge(ld,ldAc,on='MedDescription',how='outer')
0 
pd.merge(ld,ldAc,on='MedDescription',how='outer')

This is the way I used to join the 2 DataFrames, it seems to work, although it deleted one of the Indexes that contained the devices.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.