0
$\begingroup$

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?

$\endgroup$
0
$\begingroup$

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!

$\endgroup$
  • $\begingroup$ > "relational database" I was hoping to not have to do that myself. I tried pandas.concat(...), but one thing I didn't add was that there were other file types whose data needs parsing. I was trying to keep the OP simple. Another file, for example, has timing data for each test. When I concat the pass/fail DataFrames with the timing DataFrames, I end up with a big DataFrame that has two entries for the same test case and test ID: one with timing data, the other with NaN timing data. Using pandas.merge(...) got rid of that problem, but now I have duplicate columns. $\endgroup$ – John Cox Oct 20 '18 at 17:14
  • $\begingroup$ OK. I think this is sounding like the best thing to do is to figure out a relational schema for the data and then create dataframes for the separate tables in your schema (if you definitely want to stick to pandas and not go to SQLite or something). It sounds like you should have a tests table with a test ID and a separate timing table - you can pile up all the timing data in that table if you want, and just mark each timing row with the ID of the test it came from. Then you won't have big sections of NaN in a main table or tons of repeated test data. $\endgroup$ – Matthew Oct 20 '18 at 17:21
  • $\begingroup$ I missed your "I was hoping to not have to do that myself" line when I read your earlier comment. Stick to pandas if you can get away with it, but I do think you're probably best off in the long run thinking about the data in relational terms, even if you don't necessarily implement it that way (store the headers in a dictionary somewhere if you're probably never going to use them, that sort of thing.) $\endgroup$ – Matthew Oct 20 '18 at 17:26
  • $\begingroup$ Yeah, I'll have to start thinking about that. I don't come from a database background (last time I dealt with this stuff was in school), so relearning how to design and create and use a relational database is going to be a long hill. Thanks anyway though. $\endgroup$ – John Cox Oct 20 '18 at 18:38

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.