I want to populate values of df2['VALUE']
in a new column in df1 df1['New']
when category and location match for both dataframes.
When
df1['category'] = df2['CATEGORY']
AND df1['location'] = df2['LOCATION']
populate values of df2['VALUE']
into a new row in df1 so that using df1['v1']
I can calculate df1['calculatedfield'] = df1['v1']/df1['new']
df1
category | location | type | v1 |
---|---|---|---|
A | loc1 | 1 | 2 |
A | loc1 | 2 | 4 |
A | loc2 | 1 | 6 |
A | loc2 | 2 | 8 |
B | loc1 | 1 | 10 |
B | loc1 | 2 | 12 |
B | loc2 | 1 | 14 |
B | loc2 | 2 | 16 |
df2
CATEGORY | LOCATION | VALUE |
---|---|---|
A | loc1 | 50 |
A | loc2 | 30 |
B | loc1 | 70 |
B | loc2 | 90 |
output
category | location | type | v1 | new(df2['VALUE']) | calculatedfield(v1/new) |
---|---|---|---|---|---|
A | loc1 | 1 | 2 | 50 | 0.04 |
A | loc1 | 2 | 4 | 50 | 0.08 |
A | loc2 | 1 | 6 | 30 | 0.2 |
A | loc2 | 2 | 8 | 30 | 0.27 |
B | loc1 | 1 | 10 | 70 | 0.14 |
B | loc1 | 2 | 12 | 70 | 0.17 |
B | loc2 | 1 | 14 | 90 | 0.16 |
B | loc2 | 2 | 16 | 90 | 0.18 |
df1
has more rows than df2
, that is why I didn't go with joining the two dataframes. I need to populate df2['new']
with the values from df2['VALUE']
wherever the combination of category and location come up and regardless of the value of df2['type']
. I can't drop rows.
I tried
df1['New'] = np.where((df1['category'] == df2['CATEGORY']) & (df1['location'] == df2['location']), df2['VALUE'], None)
and this came up:
ValueError: Can only compare identically-labeled Series objects
You can use pandas.set_index()
function to create a multiIndex dataframe for obtaining values from df2
and use the pandas.reset.index()
to reset the dataframe. Please see below my sample code. I have replicated your dataframes df1
and df2
.
import pandas as pd
data1 = {'category': ['A', 'A', 'A', 'A','B', 'B', 'B', 'B'],
'location': ['loc1', 'loc1', 'loc2', 'loc2','loc1', 'loc1', 'loc2', 'loc2'],
'type': [1,2,1,2,1,2,1,2,],
'v1': [2,4,6,8,10,12,14,16]}
data2 = {'category': ['A', 'A','B', 'B'],
'location': ['loc1', 'loc2', 'loc1','loc2'],
'VALUE': [50,30,70,90]}
df1 = pd.DataFrame(data1)
df2 = pd.DataFrame(data2)
df_temp = df1.set_index(['category','location'])
df_temp['New'] = df2.set_index(['category','location'])['VALUE']
df1 = df_temp.reset_index()
df1['Calculated_Field'] = df1['v1']/df1['New']
df1
https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.set_index.html