Create multiple conditional columns from a source list

Question

How can I create multiple conditional columns using a list as source in pandas.

This is my original dataframe:

Treaty Number	Entry Code	Amount
BRI1	A500	2,00
BRI2	1100	3,00
BRI3	1101	5,00
BRI1	A500	2,00
BRI4	A500	6,00
BRI5	1113	1,00
BRI3	1101	1,00
BRI2	1100	5,00

And this is the list of all the existing entry codes in my dataframe:

Entry Code
A500
1100
1101
1113

I want to create a column for every single Entry Code with their respective amounts, therefore generating the following output (after aggregating/grouping my data):

Treaty Number	1100	1101	1113	A500
BRI1	0,00	0,00	0,00	4,00
BRI2	8,00	0,00	0,00	0,00
BRI3	0,00	6,00	0,00	0,00
BRI4	0,00	0,00	0,00	6,00
BRI5	0,00	0,00	1,00	0,00

I managed to achieve this by creating each column individually using np.where():

df['1100'] = np.where(df['Entry Number'] == '1100', df['Amount'], 0)
df['1101'] = np.where(df['Entry Number'] == '1101', df['Amount'], 0)
df['1113'] = np.where(df['Entry Number'] == '1113', df['Amount'], 0)
df['A500'] = np.where(df['Entry Number'] == 'A500', df['Amount'], 0)

The problem is I will soon be working with dataframes that contain over 30+ Entry Codes, making it annoying and ineficient to create 30+ individual columns for every single code.

What would be the best way to approach this?

score 1 · Accepted Answer · answered Aug 25 '23 at 18:04

This is just ordinary pivot operation:

# if the values are strings (with ","), convert it to float:
df["Amount"] = df["Amount"].str.replace(",", ".").astype(float)

out = df.pivot_table(
    index="Treaty Number",
    columns="Entry Code",
    values="Amount",
    aggfunc="sum",
    fill_value=0,
)
print(out)

# Note: If you're missing some columns (there aren't any values in the dataframe), you can reindex the columns.

Prints:

Entry Code     1100  1101  1113  A500
Treaty Number                        
BRI1              0     0     0     4
BRI2              8     0     0     0
BRI3              0     6     0     0
BRI4              0     0     0     6
BRI5              0     0     1     0

Create multiple conditional columns from a source list

1 Answers1