CODEWITHSUNDEEP
pythonpandas
Andrew
Andrew

Reputation: 49

Merging a dataframe with a dictionary

I have created a dictionary (tax_dict) in a Python script that I want to use to create a new column in a dataframe (tax_cols) I have uploaded from a csv file.

Both the dataframe and the dictionary have columns called 'Tax Head'.

DataFrame from CSV imported via pandas and the dictionary I created in the script:

Tax Head Amount
Stamps 554354
Customs 342425 
tax_dict = {'Tax Head': ['Customs','Excise Duty','Capital Gains Tax','Stamps','Income Tax','Corporation Tax','Valued Added Tax','Training and Employment Levy','Motor Vehicle Duties','Unallocated Tax Receipts','Capital Acquisitions Tax','Local Property Tax'],
    'Tax Heads Short': ['CUST','EXD','CGT','SD','IT','CORP','VAT','TEL','MOT','UNA','CAT','LPT']}

I'm trying to create another column using a merge that has the shorter tax heads:

tax_dict_pd = pd.DataFrame(data=tax_dict)
tax_cols['Tax Head Short'] = tax_cols.merge(tax_dict_pd, on='Tax Head', how='left')

I thought this simple line would work but keeps pulling through errors KeyError: 'Short' & 'ValueError: Wrong number of items passed 7, placement implies 1'

Any idea what I'm doing wrong?

Upvotes: 1

Views: 223

Answers (1)

Quang Hoang
Quang Hoang

Reputation: 150735

merge returns a data-frame, and generally you can't assign a dataframe (with more than 1 column) to a column. Try:

tax_dict_pd = pd.DataFrame(data=tax_dict)
tax_cols['Tax Head Short'] = tax_cols.merge(tax_dict_pd, on='Tax Head', how='left')['Tax Head Short']

Or use map:

tax_dict_pair = dict(**zip(tax_dict['Tax Head'], tax_dict['Tax Heads Short']))
tax_cols['Tax Head Short'] = tax_cols['Tax Head'].map(tax_dict_pair)

Upvotes: 1

Related Questions