Create column from another dataframe, using existing columns-CodePudding

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.

CodePudding user response：

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)

CodePudding user response：

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

CodePudding user response：

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

CodePudding user response：

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