Create column from another dataframe, using existing columns

Question

I have a pandas DataFrame with columns "Quantity" and "Unit", e.g.:

    Quantity    Unit
0   Current     A
1   Current     mA
2   Voltage     V
3   Length      m
4   Length      km

and I have a pandas DataFrame with ratios of units, e.g.:

      Current  Voltage  Length           
A       1        NaN      NaN
mA     1000      NaN      NaN
V      NaN       1        NaN
m      NaN       NaN      1000
km     NaN       NaN       1

Is it possible to create in first dataframe column with values of ratios?:

    Quantity    Unit   Ratio
0   Current     A        1
1   Current     mA      1000
2   Voltage     V        1
3   Length      m        1
4   Length      km      1000

I have tried to get every value by iterrows, but I have really big dataframe and it's not working correctly in case of time. Maybe there is another, more expedient way to solve my issue, but I have to work with dataframes of ratios.

cottontail · Accepted Answer · 2022-06-23 23:47:11Z

3

Pandas' merge() efficiently works as mapping data from one df to another

# stack the second df and merge it with the first on Unit and Quantity
df1.merge(df2.stack().rename('Ratio'), left_on=['Unit', 'Quantity'], right_index=True)

answered Jun 23, 2022 at 23:47

cottontail

25.6k25 gold badges184 silver badges176 bronze badges

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

Comments

rafaelc · Accepted Answer · 2022-06-23 23:43:24Z

2

Using to_dict:

d = df2.to_dict('i')

df['Ratio'] = df.apply(lambda s: d[s['Unit']][s['Quantity']], axis=1)

  Quantity Unit   Ratio
0  Current    A     1.0
1  Current   mA  1000.0
2  Voltage    V     1.0
3   Length    m  1000.0
4   Length   km     1.0

answered Jun 23, 2022 at 23:43

rafaelc

59.4k15 gold badges64 silver badges87 bronze badges

Comments

G. Anderson · Accepted Answer · 2022-06-23 23:43:52Z

1

Given the input dataframe

df
      Current  Voltage  Length           
A       1        NaN      NaN
mA     1000      NaN      NaN
V      NaN       1        NaN
m      NaN       NaN      1000
km     NaN       NaN       1

Then it seems like you want something as simple as

df.stack()

A   Current       1.0
mA  Current    1000.0
V   Voltage       1.0
m   Length     1000.0
km  Length        1.0
dtype: float64

answered Jun 23, 2022 at 23:43

G. Anderson

5,9652 gold badges16 silver badges22 bronze badges

Comments

sammywemmy · Accepted Answer · 2022-06-24 00:12:18Z

0

You can use map:

df.assign(Ratio = df.Unit.map(ratios.bfill(axis = 1).Current))

  Quantity Unit   Ratio
0  Current    A     1.0
1  Current   mA  1000.0
2  Voltage    V     1.0
3   Length    m  1000.0
4   Length   km     1.0

answered Jun 24, 2022 at 0:12

sammywemmy

28.9k4 gold badges21 silver badges35 bronze badges

Collectives™ on Stack Overflow

Create column from another dataframe, using existing columns

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related