Python For loop to update a data frame

Question

I am trying to re-create a VBA macro that I have using Python. Could someone please tell me the FOR statement I should use, in order to get below result? Thank you very much.

file 1:

Product Colour  Price
Book    NaN 5
Table   NaN 10
Chair   NaN 7

file 2:

Colour 
Blue 
Red 
Green

Expected result (file 1) after the loop:

Product Colour Price    
Book    Blue    5
Table   Blue    10
Chair   Blue    7
Book    Red 5
Table   Red 10
Chair   Red 7
Book    Green   5
Table   Green   10
Chair   Green   7

Could you please provide a Minimal, Complete, and Verifiable example. Images do not help!. — Space Impact
– Space Impact, Commented Oct 6, 2018 at 17:45
I have updated the question. I hope you can see the example now. — Rafael
– Rafael, Commented Oct 6, 2018 at 17:59
Thank you for your help. But, I am getting some errors when trying to use the code you suggested: — Rafael
– Rafael, Commented Oct 8, 2018 at 12:49

Space Impact · Accepted Answer · 2018-10-08 13:04:56Z

1

First duplicate values of df1 by length of df2 and then use list comprehesion and chain for Colour as:

from itertools import chain


df = pd.DataFrame({'Product': df1['Product'].values.tolist()*len(df2),
                   'Price'  : df1['Price'].values.tolist()*len(df2),
                   'Colour' : list(chain.from_iterable([[v]*len(df1) for v in df2['Colour'].values.tolist()]))})

print(df)
  Product  Price Colour
0    Book      5   Blue
1   Table     10   Blue
2   Chair      7   Blue
3    Book      5    Red
4   Table     10    Red
5   Chair      7    Red
6    Book      5  Green
7   Table     10  Green
8   Chair      7  Green

edited Oct 8, 2018 at 13:04

answered Oct 6, 2018 at 18:23

Space Impact

13.3k26 silver badges51 bronze badges

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

1 Comment

Rafael Over a year ago

Thank you for your help Sandeep!

jpp · Accepted Answer · 2018-10-08 13:19:28Z

0

You can use a list comprehension, analogous to a nested for loop:

df = pd.DataFrame([[product, colour, price] for colour in df2['Colour'] \
                   for product, price in zip(df1['Product'], df1['Price'])],
                  columns=['Product', 'Colour', 'Price'])

print(df)

  Product Colour  Price
0    Book   Blue      5
1   Table   Blue     10
2   Chair   Blue      7
3    Book    Red      5
4   Table    Red     10
5   Chair    Red      7
6    Book  Green      5
7   Table  Green     10
8   Chair  Green      7

answered Oct 8, 2018 at 13:19

jpp

166k37 gold badges301 silver badges363 bronze badges

1 Comment

Rafael Over a year ago

When trying to read the data from a file, it does not work. Could you pleaase help me to check ? x = pd.read_excel('C:/Desktop/file1.xlsx') df1 = pd.DataFrame(data=x) df1 y = pd.read_excel('C:/Desktop/file2.xlsx') df2 = pd.DataFrame(data=y) df2 df = pd.DataFrame([[Product, Colour, Price] for Colour in df2['Colour'] for Product, Price in zip(df1['Product'], df1['Price'])], columns=['Product', 'Colour', 'Price']) df

Collectives™ on Stack Overflow

Python For loop to update a data frame

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related