0

I have two Python Pandas DataFrames:

Inventory_Info (20 rows):

Group, Facility, Unit, Room, HardwareAddress
'Generic Hospital Group', 'Generic Hospital', 'Generic Hospital Ward', 'Generic Hospital Room', 5715

Data (47 rows):

Serial_Number, Last_Server_Check, Num_Check_Ins, HardwareAddress
00BA0F42, 23, 1/18/17 20:38, 3906

All 20 values of Inventory_Info.HardwareAddress appear in Data.HardwareAddress.

I've tried this, but it doesn't merge the data. I get column names from both DataFrames, but only the contents of Data are included.

finalDataSet = pandas.DataFrame.merge(Data, Inventory_Info, how='left', on='HardwareAddress')

Similarly, I've tried flipping the DataFrame order:

finalDataSet = pandas.DataFrame.merge(Inventory_Info, Data, how='left', on='HardwareAddress')

I get the merged column names, but now only the Inventory_Info data is displayed.

I've tried using this merge syntax:

finalDataSet = webData.merge(rawData, left_index='HardwareAddress', right_on='HardwareAddress')

While this syntax gets the data into the correct format and populates all columns, the data is not matched on the HardwareAddress index and its arrangement is wrong.

OnlyDean
  • 1,025
  • 1
  • 13
  • 25
  • 1
    First, you need to post the head of each DataFrame. Next, alias pandas as `pd`. Finally, do not use pandas.DataFrame.merge. Use `pd.merge`. – Ted Petrou Jan 18 '17 at 21:38
  • I tried pd.merge just now and got the same results as pd.DataFrame.merge. – OnlyDean Jan 18 '17 at 21:52
  • 2
    please edit your question and paste the results of `print(Data.head())` and `print(Inventory_Info.head())` – Ted Petrou Jan 18 '17 at 21:55
  • *All 20 values of Inventory_Info.HardwareAddress appearin Data.HardwareAddress*...without data we can't reproduce and can only take your word for it. Also *I get column names from both DataFrames, but only the contents of Data are included.*...do you mean NaNs return? Do note you are using `left` join so non-matches do return NaNs. – Parfait Jan 19 '17 at 01:31

1 Answers1

3

I finally solved this one.

Inventory_Info.dtypes and Data.dtypes each revealed that HardwareAddress was type object. I used Data['HardwareAddress'].astype(int) and Inventory_Info['HardwareAddress'].astype(int), and then the DataFrames merged exactly as expected.

OnlyDean
  • 1,025
  • 1
  • 13
  • 25