Pandas Python How do use common data from one data frame to write to a different data frame?

Question

I am trying to use df4's LineNum column to identify the GeneralDescription in df1 by matching LineNumbers and writing to the corresponding GeneralDescription's column cell in df1. I am going for a solution that is scalable to work with data frames with thousands of rows and several other inconsequential columns. I would rather not merge if it isnt absolutely necessary. I just want to write to df1's TrueDepartment column and leave the original structure of the 2 data frames the same. Thanks –

df1
    LineNum Warehouse           GeneralDescription
0   2       Empty               Empty
1   3       Empty               Empty
2   4       PBS                 Empty
3   5       Empty               Empty
4   6       Empty               Empty
5   7       General Liability   Empty
6   8       Empty               Empty
7   9       Empty               Empty    

df4
    LineNum GeneralDescription
0   4       TRUCKING
1   6       TRUCKING-GREENVILLE,TN
2   7       Human Resources 

Desired result

    LineNum Warehouse           GeneralDescription
0   2       Empty               Empty
1   3       Empty               Empty
2   4       PBS                 TRUCKING
3   5       Empty               Empty
4   6       Empty               TRUCKING-GREENVILLE,TN
5   7       General Liability   Human Resources
6   8       Empty               Empty
7   9       Empty               Empty

This is the code I have so far with packages that might be helpful. As it is I'm getting the error that says KeyError: 'the label [LineNum] is not in the [index]'

import pandas as pd
import openpyxl
import numpy as np

data= [[2,'Empty','Empty'],[3,'Empty','Empty'],[4,'PBS','Empty'],[5,'Empty','Empty'],[6,'Empty','Empty'],[7,'General Liability','Empty'],[8,'Empty','Empty'],[9,'Empty','Empty']]
df1=pd.DataFrame(data,columns=['LineNum','Warehouse','GeneralDescription'])

data4 = [[4,'TRUCKING'],[6,'TRUCKING-GREENVILLE,TN'],[7,'Human Resources']]
df4=pd.DataFrame(data4,columns=['LineNum','GeneralDescription'])


for i in range(len(df1.index)):
    if df1.loc[i,'LineNum']==df4.loc['LineNum']:
        df1.loc[i,'GeneralDescription']=df4.loc['GeneralDescription']

i would rather not merge if it isnt absolutely necessary. I just want to write to df1's TrueDepartment column and leave the structure of the 2 data frames the same. Thanks — Royale_w_cheese
– Royale_w_cheese, Commented Jul 15, 2018 at 3:57

jezrael · Accepted Answer · 2018-07-15 04:05:37Z

Use map with Series created by df4 with fillna by original column values:

s = df4.set_index('LineNum')['TrueDepartment']
df1['TrueDepartment'] = df1['LineNum'].map(s).fillna(df1['TrueDepartment'])
print (df1)
   LineNum         Department          TrueDepartment
0        2              Empty                   Empty
1        3              Empty                   Empty
2        4                GBS                TRUCKING
3        5              Empty                   Empty
4        6              Empty  TRUCKING-GREENVILLE,TN
5        7  General Liability         Human Resources
6        8              Empty                   Empty
7        9              Empty                   Empty

Solution with DataFrame.merge:

df = df1.merge(df4,how='left', on='LineNum', suffixes=('','_'))
df['TrueDepartment'] = df['TrueDepartment_'].combine_first(df['TrueDepartment'])
df = df.drop('TrueDepartment_', axis=1)
print (df)
   LineNum         Department          TrueDepartment
0        2              Empty                   Empty
1        3              Empty                   Empty
2        4                GBS                TRUCKING
3        5              Empty                   Empty
4        6              Empty  TRUCKING-GREENVILLE,TN
5        7  General Liability         Human Resources
6        8              Empty                   Empty
7        9              Empty                   Empty

Collectives™ on Stack Overflow

Pandas Python How do use common data from one data frame to write to a different data frame?

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related