What am I doing wrong turning Pandas DF to dict?-CodePudding

I have a csv that looks like

AccountExternalID    Customer
1                    RogerInc
2                    FredLLC

I am turning that into a Pandas DF, and I want to turn that into a dict that looks like

{'RogerInc': 1, 'FredLLC': 2}

This is what I tried;

def build_custid_dict(csv_path: str=None) -> dict[str]:
    csv_path = r'\\path\CustomerIDs.csv'
    df = pd.read_csv(csv_path)
    # Strip whitespace
    df[df.columns] = df.apply(lambda x: x.str.strip())
    df_dict = df.to_dict('list')
    return df_dict

CodePudding user response：

Example

data = {'AccountExternalID': {0: 1, 1: 2}, 'Customer': {0: 'RogerInc', 1: 'FredLLC'}}
df = pd.DataFrame(data)

output(df):

    AccountExternalID   Customer
0   1                   RogerInc
1   2                   FredLLC

Code

use following code in your func:

dict(df.iloc[:, [1, 0]].values)

result:

{'RogerInc': 1, 'FredLLC': 2}

CodePudding user response：

The to_dict method will map the column headers to the column data. You don't want that for what you're trying to do.

Instead, you can do something like this:

ids = df['AccountExternalID'].values
customers = df['Customer'].values
df_dict = {customer:id for customer, id in zip(customers, ids)}

CodePudding user response：

Try this one you can select one column of the dataframe as the key and another one as the value of dict.

def build_custid_dict(csv_path: str=None) -> dict[str]:
    csv_path = r'\\path\CustomerIDs.csv'
    df = pd.read_csv(csv_path)
    # Strip whitespace
    df['AccountExternalID'].str.strip()
    df['Customer'].str.strip()
    df = df.set_index('AccountExternalID') 
    df_dict = df.to_dict('Customer')
    dict((v, k) for k, v in df_dict.items()) #reversing the keys and values
    return df_dict

Also refer to this Stack overflow question

CodePudding user response：

You could also use the to_dict method on a series, e.g.

df.set_index('Customer').AccountExternalID.to_dict()