CODEWITHSUNDEEP
pythonpandasdataframe
poorpractice
poorpractice

Reputation: 127

Pandas Multiple DataFrames from other DataFrames

Suppose I have a base "test" dataframe of varying length, which I am constructing based off of a slider date selector. 

            seasons
test    
2018-02-19  Winter
2018-02-20  Winter
2018-02-21  Winter
2018-02-22  Winter
... ...
2019-06-25  Summer
2019-06-26  Summer
2019-06-27  Summer
2019-06-28  Summer

suppose now that I have multiple dataframes for product prices at different supermarkets like:

Loblaws

     Summer   Winter
Milk  -7800.0  -3600.0
Salt  -9000.0  -4500.0
Pear -15300.0 -11700.0

Wal-Mart

     Summer   Winter
Milk -14700.0 -10200.0
Salt  -7500.0  -4800.0
Pear  -3000.0  -9600.0

Whole Foods

     Summer  Winter
Milk -11500.0 -7500.0
Salt  -7000.0 -8500.0
Pear  -1000.0 -6500.0

How would I go about making new dataframes with columns based on the items for each supermarket? So for example:

Loblaws     seasons Milk    Salt    Pear
2018-02-19  Winter  -3600.0 -4500.0 -11700.0
2018-02-20  Winter  -3600.0 -4500.0 -11700.0
... ...
2019-06-25  Summer  -7800.0 -9000.0 -15300.0
2019-06-26  Summer  -7800.0 -9000.0 -15300.0

and the same for Wal-Mart/Whole Foods?

I don't know if it would be better for me to convert the supermarket dataframes to dictionaries and then run in a loop based off of the dictionaries, or if I (join?) them and loop through the supermarket dataframes. Thank you for any and all help.

EDIT: I have been able to do it with a single one, I am just not sure how to do it in an iterative manner (for however many dataframes I have without directly calling them individually). To do one I used:

Loblaws = Loblaws.transpose()
Loblaws_Merged = pd.merge(test, Loblaws, left_on='seasons', right_index = True)

Upvotes: 1

Views: 79

Answers (1)

Elegant Code
Elegant Code

Reputation: 690

Building off of @Mayeul sgc's comment for a multiple case scenario and for the benefit of anyone looking for an answer to the same question: you can do the following:

merged_dataframes = []
# first put all dataframes in a list
supermarkets = [loblaws, wal_mart, whole_foods]
# then use a for loop and Mayeul sgc's clever code:
for df in supermarkets:
    df = df.transpose()
    merged_dataframe = pd.merge(test, df, left_on='seasons', right_index = True)
    merged_dataframes.append(merged_dataframe)

Hope this works for you. Let me know if you needed something else.

Upvotes: 1

Related Questions