How can I merge 2+ DataFrame objects without duplicating column names?

Question

This is for work. TLDR: Bottom-line question at the bottom.

I am gathering and parsing test results produced by an old test setup whose output formatting is not likely to change anytime soon. I've made good progress on parsing the output data into lists of strings, booleans, etc., but I'm having trouble pulling the data together into an easily searchable and retrievable whole. The data looks something like this:

- test_case_A
    - pass/fail file
        - big header info with test case metadata
        - test 1    pass/fail   supporting data
        - test 2    pass/fail   supporting data
        - ...
        - test 100  pass/fail   supporting data
- test_case_B
    - pass/fail file
        - big header info with test case metadata
        - test 1    pass/fail   supporting data
        - test 2    pass/fail   supporting data
        - ...
        - test 70   pass/fail   supporting data
- test_case_C
    - pass/fail file
        - big header info with test case metadata
        - test 1    pass/fail   supporting data
        - test 2    pass/fail   supporting data
        - ...
        - test 10   pass/fail   supporting data
- test_case_D
    - pass/fail file
        - big header info with test case metadata
        - test 1    pass/fail   supporting data
        - test 2    pass/fail   supporting data
        - ...
        - test 30   pass/fail   supporting data

I parse them into individual DataFrame objects like so:

df_case_A_pass_fail = pd.DataFrame({
    "case" : "test case A",
    "header" : *big header string*,
    "test ID" : [*list of IDs*],
    "test passed" : [*list of bool*],
    "test data" : [*list of strings*],
    })
*repeat for test case B, C, and D*

Now I try to merge them together.

big_df = fancy_merge_step_probably_involving_reduce_and_a_lambda(...)

Problem 1: The "case" and "header" strings appear to be duplicated all the way down their DataFrame. Like so:

             case        test ID  test passed               header      supporting data
0   "test case A"    "test 1 ID"         True  "big header string"  "all kinds of stuff"
1   "test case A"    "test 2 ID"         False "big header string"  "all kinds of stuff"
...
99  "test case A"  "test 100 ID"         True  "big header string"  "all kinds of stuff"

I checked the DataFrame size according to this get_real_size(...) algorithm and the size explodes exponentially as I merge in more results (~80KB for 1 test case, ~800KB for 2 test cases), so the big header string is definitely getting duplicated. I want to establish one->one relationship between test case and header and one->many between test case and test data, but all I'm seeing is duplication until there is a unique permutation of every line. Am I making the DataFrame wrong for what I need?

Problem 2: (Possibly related to problem 1.) I attempt to merge the DataFrames with an outer join and get the NaN results for places where the column sizes don't match up (expected), but also get duplicated columns "case", "header", "test passed", "test data" (any column that wasn't merged on), appendend with automatic suffixes ("_x", "_y"). I know that pandas does this automatically when there is a column name class, but it is now a problem. Result: searching on column "case" fails because the merged DataFrame has no column "case". All columns formerly named "case" are now "case_x" or "case_y".

I want to query like this:

match1 = (big_df["case"] == "test case D")
match2 = (big_df["test ID"] == "test 3")
single_test_df = big_df.loc[match1 & match2]

match1 = (big_df["case"] == "test case A")
match2 = (big_df["header"] == "header")
header_str = big_df.loc[match1 & match2].values[0]

Question: How do I set up these DataFrame objects and merge them so that I can query the test data as mentioned earlier?

Answer 1

Welcome to the site!

Problem 1 seems to be an issue that could be solved by treating your dataframes like tables in a relational database. By this I mean: you have your main data table and secondary tables called 'headers' and 'supporting_data', and you store each header in the 'headers' table once with an ID (a key, maybe just an integer) assigned to it, and in the main table you just store the integer corresponding to the header data. If you ever need to refer to the header data, you look up the integer and go over to the 'headers' table to retrieve it. You can do something similar with the supporting data, but not knowing what that data is it's up to you to figure out if you should parse out parts of that data or store it as whole text blocks or whatever. But if you do this, you'll just have two integers on each row of the main table instead of all that superfluous repeated data.

Problem 2 I'm less sure about, but I'd look into the pandas concat function. I think you should probably just have a case column containing 'A', 'B', etc, and be appending the rows from those files into the main table; concat should be able to do that if all the data types work out, etc, etc.

Good Luck!