pandas dataframe: make new dataframe from lookup and computation of existing dataframes

Question

Im trying to use data from two dateframes to create a new dataframe

lookup_data = [
{   'item': 'apple',
    'attribute_1':3,
    'attribute_2':2,
    'attribute_3':10,
    'attribute_4':0,
},
{   'item': 'orange',
    'attribute_1':0.4,
    'attribute_2':20,
    'attribute_3':1,
    'attribute_4':9,
},
{   'item': 'pear',
    'attribute_1':0,
    'attribute_2':0,
    'attribute_3':30,
    'attribute_4':0,
},
{   'item': 'peach',
    'attribute_1':2,
    'attribute_2':2,
    'attribute_3':3,
    'attribute_4':6,
},]

df_lookup_data = pd.DataFrame(lookup_data,dtype=float)
df_lookup_data.set_index('item', inplace=True, drop=True)

collected_data = [
{   'item':'apple',
    'qnt': 4},
{   'item':'orange',
    'qnt': 2},
{   'item':'pear',
    'qnt': 7},
]

df_collected_data = pd.DataFrame(collected_data,dtype=float)
df_collected_data.set_index('item', inplace=True, drop=True)

df_result = pd.DataFrame(
    .... first column is item type
    .... second column is qnt*attribute_1
    .... second column is qnt*attribute_2
    .... second column is qnt*attribute_3
    .... second column is qnt*attribute_4
)
df_result.columns = ['item', 'attribute_1', 'attribute_2', 'attribute_3', 'attribute_4']
print(result)

the result should print

   item    attribute_1  attribute_2  attribute_3  attribute_4
0  apple   14           8            40           0
1  orange  0.8          40           2            18
2  pear    0            0            210           0

but im really not sure how i get date from these two dataframes and make this new one

rafaelc · Accepted Answer · 2019-08-26 03:27:25Z

4

No need to merge or concat here. Since indexes do match, simply mul across axis=0

>>> df_lookup_data.mul(df_collected_data.qnt, axis=0)

        attribute_1  attribute_2  attribute_3  attribute_4
item                                                      
apple          12.0          8.0         40.0          0.0
orange          0.8         40.0          2.0         18.0
peach           NaN          NaN          NaN          NaN
pear            0.0          0.0        210.0          0.0

answered Aug 26, 2019 at 3:27

rafaelc

59.4k15 gold badges64 silver badges87 bronze badges

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

Comments

U13-Forward · Accepted Answer · 2019-08-26 03:27:07Z

1

Or use:

df_lookup_data = pd.DataFrame(lookup_data,dtype=float)
items = [i['item'] for i in collected_data]
qnts = [i['qnt'] for i in collected_data]
print(df_lookup_data[df_lookup_data['item'].isin(items)].set_index('item').mul(qnts, axis=0))

Output:

        attribute_1  attribute_2  attribute_3  attribute_4
item                                                      
apple          12.0          8.0         40.0          0.0
orange          0.8         40.0          2.0         18.0
pear            0.0          0.0        210.0          0.0

answered Aug 26, 2019 at 3:27

U13-Forward

71.8k15 gold badges100 silver badges125 bronze badges

Collectives™ on Stack Overflow

pandas dataframe: make new dataframe from lookup and computation of existing dataframes

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