[Python] duplicate removal from grouped and merged data frame fails generating duplicates in...

I have two dataframes as below:

DataFrame 1: df1

UniqueId​

VendorId​

Fname​

LName​

VendorAccNo​

001​

12​

ABC​

XYZ​

8787888​

002​

13​

XYZ​

FFF​

8787888​

003​

14​

PQR​

ZZZ​

8787888​

005​

16​

MMM​

TTT​

5432100​

006​

17​

BBB​

XXX​

5432100​

007​

18​

CCC​

PPP​

8787888​

DataFrame 2: df2

VendorXNo​

PhNo​

Active​

Type​

ActiveTime​

8787888​

45789​

Y​

W​

2019-05-22 09:36:04​

8787888​

45789​

Y​

W​

2019-05-22 09:36:04​

8787888​

12345​

N​

M​

2019-05-23 09:36:04​

8787888​

45789​

Y​

W​

2019-05-24 09:36:04​

8787888​

56742​

Y​

W​

2019-05-25 09:36:04​

8787888​

45789​

Y​

H​

2019-05-26 09:36:04​

5432100​

11111​

Y​

W​

2019-05-25 09:37:04​

5432100​

22222​

Y​

H​

2019-05-26 09:38:04​

I am using python to generate JSON form this. Want to avoid duplicates from the second data frame when joining with first data frame on VendorAccNo to VendorXNo

My code is as below to group by convert the second datset to records and give a new name to my subdocument for the JSON

df_merged = pd.merge(df1, df2, left_on='VendorAccNo', right_on='VendorXNo', how='left')

result = df_merged.groupby(['VendorXNo']).apply(lambda x: x[[
'PhNo',
'Active',
'Type',
'ActiveTime']].to_dict('records')).reset_index(name='Phone Info')

uniquedf = ~df_merged['Phone Info'].map(lambda x: tuple(sorted(x.items()))).duplicated()


The above line of code fails with error:

list object has no attribute items
​

If I do not add the code to remove the duplicates the json that I am creating has duplicates for each json as below.

The json is generated for each "UniqueId" from the dataframe 1

{
"UniqueId" : "001",
"VendorId" : "12",
"Fname" : "ABC",
"Lname" : "XYZ",
"VendorAccNo" : "8787888",
"Phone Info" : [
{
"PhNo": "45789",
"Do Not Contact": "Y",
"Change Date Time": "2019-05-22 09:36:04",
"Phone Type": "WORK"
},
> ------ This repeats fifteen times in the josn for each UniqueId, where as it should have 5 records each in each json.
]
}


What's wrong in this code? why it fails for duplicate removal?

Continue reading...