Populate dataframe dynamically based on 2 different columns

Question

I have a dataframe with the following columns in it.

df_levels = ["No","window1_level","window2_level","window3_level","window4_level", "window5_level"]

I am trying to populate the dataframe based on the values of the below dataframe. For example, for no "111111", if value1 is "window1", then the corresponding level1 value(i.e."123") has to be populated in "window1_level" in df_result. Similarly for value2, the level2 value (103) has to be populated in window2_level in df_result.

Code for generating the below dataframe:

df = pd.DataFrame([[111111,123,103,"window1","window2"], [333333,104,123,"window3","window4"], [678698,111,144,"window1","window5"]], columns=['No','level1','level2','value1','value2'])


 No       level1        level2      value1      value2
111111      123          103        window1     window2
333333      104          123        window3     window4
678698      111          144        window1     window5

df_result:

No   window1_level window2_level window3_level window4_level window5_level 
111111      123       103                 
333333                               104          123
678698      111                                              144

Currently, I am looping through the dataframe and trying to populate df_result. Would appreciate any leads on doing this efficiently.

Can you provide a reproducible example? https://stackoverflow.com/questions/20109391/how-to-make-good-reproducible-pandas-examples — william3031, Nov 23 '20 at 21:45
@william3031: Thank you, I have added the code snippet for generating the dataframe — hydesingh, Nov 23 '20 at 21:54

score 1 · Accepted Answer · answered Nov 24 '20 at 03:16

Please see notes within the code.

# create main dataframe
df = pd.DataFrame([[111111,123,103,"window1","window2"], [333333,104,123,"window3","window4"], [678698,111,144,"window1","window5"]], columns=['No','level1','level2','value1','value2'])
# create levels dataframe
cols= ["No","window1_level","window2_level","window3_level","window4_level", "window5_level"]
df_levels = pd.DataFrame(columns=cols)
# copy over No column
df_levels['No'] = df['No']
# remove nan
df_levels.fillna('', inplace=True)

# change all the window fields to desired column names for use later
map = {'window1':'window1_level','window2':'window2_level','window3':'window3_level','window4':'window4_level','window5':'window5_level',}
df['value1'] = df['value1'].map(map)
df['value2'] = df['value2'].map(map)

# row wise evaluation
def transfer_data(n, v1, l1, v2, l2 ):
    # locate No and write level information
    df_levels.loc[df_levels['No'] == n, v1] = l1
    df_levels.loc[df_levels['No'] == n, v2] = l2

# df_temp is throway df just so the apply function doesn't print to the screen; it's not needed or used
df_temp = df.apply(lambda x: transfer_data(x['No'], x['value1'], x['level1'], x['value2'], x['level2']), axis=1)

Output:

In [126]: df_levels
Out[126]:
       No window1_level window2_level window3_level window4_level window5_level
0  111111           123           103
1  333333                                       104           123
2  678698           111                                                     144

Populate dataframe dynamically based on 2 different columns

1 Answers1