Python Pandas - Dataframe - Add column depending on another column, which has a mathematical operation from another two columns

Question

I have a Pandas dataframe that looks something like this:

timestamp	Place	Data A	Data B	Data C
16508	France	0.03	0.06	0.15
16510	England	0.05	0.07	0.11
16515	England	0.04	0.03	0.87

What I would like to do is the following:

Add a new column for every different value in the column "Place".
In this new column, add the division between Data A and Data B in percentage (Data A / Data B * 100).

The expected output would be:

timestamp	Place	Data A	Data B	Data C	To France	To England
16508	France	0.03	0.06	0.15	50	0
16510	England	0.05	0.07	0.11	0	71.42
16515	England	0.04	0.03	0.87	0	133.33

I tried the following:

for column in data['Place'].unique():
    column_name = f'To {Place}'
    data[column_name] = data[data['Place'] == column]['Data A'].div(['Data B'])*100
    data[column_name].fillna(method='ffill', inplace=True)
    data[column_name].fillna(value=0, inplace=True)

But it's not working. I get a "'list' object has no attribute 'div'" error. I have tried other different things but they are not working either.

Could somebody give me a hand with this?

not_speshal · Accepted Answer · 2022-05-01 20:15:32Z

1

IIUC, you can try with pivot:

df["Ratio"] = df["Data A"].div(df["Data B"])

output = df.drop("Ratio", axis=1).join(df.pivot(None, "Place", "Ratio").mul(100).fillna(0).add_prefix("To "))

>>> output
     Place  Data A  Data B  Data C  To England  To France
0   France    0.03    0.06    0.15    0.000000       50.0
1  England    0.05    0.07    0.11   71.428571        0.0
2  England    0.04    0.03    0.87  133.333333        0.0

edited May 1, 2022 at 20:15

answered May 1, 2022 at 20:09

not_speshal

23.2k2 gold badges18 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Scott Boston · Accepted Answer · 2022-05-01 20:26:16Z

0

I'd do it like this:

df_ratio = ((df['Data A'].div(df['Data B'])*100).to_frame()
               .assign(col='To '+df['Place'])
               .set_index('col', append=True)[0]
               .unstack(fill_value=0))

pd.concat([df, df_ratio], axis=1))

Output:

   timestamp    Place  Data A  Data B  Data C  To England  To France
0      16508   France    0.03    0.06    0.15    0.000000       50.0
1      16510  England    0.05    0.07    0.11   71.428571        0.0
2      16515  England    0.04    0.03    0.87  133.333333        0.0

answered May 1, 2022 at 20:26

Scott Boston

154k15 gold badges160 silver badges207 bronze badges

Collectives™ on Stack Overflow

Python Pandas - Dataframe - Add column depending on another column, which has a mathematical operation from another two columns

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related