Replacing one row with another row at certain index of dataframe and changing cell value

Viewed 37

I have a sample csv like this :

                 keys                       key_regex    datatype detailed_datatype precedence  val_regex     val_regex_2  val_regex_3  max_words  alpha_char_check
0      billingAddress      original_billing_key_regex  alphabetic           address    primary        NaN             NaN          NaN        NaN               NaN
1     deliveryAddress     original_delivery_key_regex  alphabetic           address    primary        NaN             NaN          NaN        NaN               NaN
2         notifyParty     original_notify_party_regex  alphabetic        alphabetic    primary        NaN             NaN          NaN        NaN               NaN
3       originAddress   original_seller_address_regex  alphabetic           address    primary        NaN             NaN          NaN        NaN               NaN
4   billingAddressAlt   alternative_billing_key_regex  alphabetic           address   tertiary        NaN             NaN          NaN        NaN               NaN
5  deliveryAddressAlt  alternative_delivery_key_regex  alphabetic           address   tertiary        NaN             NaN          NaN        5.0               1.0
6    originAddressAlt    alternative_seller_key_regex  alphabetic           address   tertiary        NaN  sample_val_re1          NaN        NaN               0.0

I'm trying to replace the rows for which keys column have value as the key in the tertiary_row_replacement_dict with the rows with the keys column value as the corresponding value and then rename the precendence column value from 'tertiary' to 'primary' - while keeping the index position same as before.

The expected output is like this :

              keys                       key_regex    datatype detailed_datatype precedence  val_regex     val_regex_2  val_regex_3  max_words  alpha_char_check
0   billingAddress   alternative_billing_key_regex  alphabetic           address    primary        NaN             NaN          NaN        NaN               NaN
1  deliveryAddress  alternative_delivery_key_regex  alphabetic           address    primary        NaN             NaN          NaN        5.0               1.0
2      notifyParty     original_notify_party_regex  alphabetic        alphabetic    primary        NaN             NaN          NaN        NaN               NaN
3    originAddress    alternative_seller_key_regex  alphabetic           address    primary        NaN  sample_val_re1          NaN        NaN               0.0

There are 3 original csvs - each of them are huge with lots of similar cases i.e. keys with primary precedence and their alternative keys with tertiary precedence. I've dictionary with the keys' alter like this :

tertiary_row_replacement_dict = {
    "originAddress": "originAddressAlt",
    "deliveryAddress": "deliveryAddressAlt",
    # "totalAmount": "totalAmountAlt",
    "billingAddress": "billingAddressAlt"
    ....
}

Provided that the keys and the corresponding values of this dictionary would be always present in the csv, I have this code :

for k, new_k in row_replacement_dict.items():
    t2 = df.loc[df['keys']==new_k].index[0]
    df.loc[df.loc[df['keys']==k].index[0]] = [i if i!='tertiary' else 'primary' for i in df.loc[t2]]
    df = df.replace([new_k, 'tertiary'], [k, 'primary']).drop([t2])

It accomplishes what I am trying to do. It takes around 0.034 seconds to do this just on the test csv and might not be best or optimized way to handle this case of replacing just the rows and replace the cell values. Is there any faster alternative way with the prerequisite knowledge which rows to be replaced with which one (i.e. it's not mandatory to use that dictionary, we can use it as list of tuples of list of lists for speed trade off).

1 Answers

You can use replace to replace the tertiary keys with the primary keys and groupby().first() to fill in the information:

inverse_dict = {v:k for k,v in tertiary_row_replacement_dict.items()}
(df.groupby(df['keys'].replace(inverse_dict))
   .first()
   .reset_index(drop=True)
)

Output:

    keys             key_regex                      datatype    detailed_datatype    precedence      val_regex  val_regex_2       val_regex_3    max_words    alpha_char_check
--  ---------------  -----------------------------  ----------  -------------------  ------------  -----------  --------------  -------------  -----------  ------------------
 0  billingAddress   original_billing_key_regex     alphabetic  address              primary               nan  nan                       nan          nan                 nan
 1  deliveryAddress  original_delivery_key_regex    alphabetic  address              primary               nan  nan                       nan            5                   1
 2  notifyParty      original_notify_party_regex    alphabetic  alphabetic           primary               nan  nan                       nan          nan                 nan
 3  originAddress    original_seller_address_regex  alphabetic  address              primary               nan  sample_val_re1            nan          nan                   0
Related