0

For context, I have a master file that was manually compiled to look like the master table. In regards to the table 1 and 2 examples shown, they're all Excel filed xlsx data tables I've opened from a folder that contains scatter information relevant to the clothing item. That's why the sizing detail columns for table 1 and 2 differs but I intend to standardize it to look like the master table.

I have two Dataframes like:

Table 1:

| No    | Item      | Style     | Color     | Description   | ONE SIZE  | 34    | 36    | 38    | 40    | QTY   | Weight    |
|----   |-------    |-------    |-------    |-------------  |---------- |----   |----   |----   |----   |-----  |--------   |
| 1     | 11168     | Plaid     | Brown     | Shoes         | 1         | 1     |       | 1     |       | 3     | 10        |

Table 2:

| No    | Item      | Style     | Color     | Description   | XS    | S     | M     | L     | XL    | QTY   | Weight    |
|----   |-------    |--------   |-------    |-------------  |----   |---    |---    |---    |----   |-----  |--------   |
| 1     | 11169     | Velvet    | Red       | Jacket        | 10    | 5     | 6     | 8     | 3     | 32    | 40        |

I want to merge them, to get a result like:

| No    | Item      | Style     | Color     | Description   | XS    | S     | M     | L     | XL    | ONE SIZE  | 34    | 36    | 38    | 40    | QTY   | Weight    |
|----   |-------    |--------   |-------    |-------------  |----   |---    |---    |---    |----   |---------- |----   |----   |----   |----   |-----  |--------   |
| 1     | 11168     | Plaid     | Brown     | Shoes         |       |       |       |       |       | 1         | 1     |       | 1     |       | 3     | 10        |
| 2     | 11169     | Velvet    | Red       | Jacket        | 10    | 5     | 6     | 8     | 3     |           |       |       |       |       | 32    | 40        |

I tried using merged_data = table1.merge(table2, on=['No', 'Item', 'Style', 'Color', 'Description', 'QTY', 'Weight']) with the idea of merging on the common columns, but I get a result like this instead:

| XS_x  | S_x   | M_x   | L_x   | ...   | BRAND     | XS_y  | S_y   | M_y   | L_y   | XL_y  | ONE SIZE  | Unnamed: 14_y     | Unnamed: 15_y     | Unnamed: 16_y     |
|-----: |----:  |----:  |----:  |----:  |------:    |-----: |----:  |----:  |----:  |-----: |---------: |--------------:    |--------------:    |--------------:    |

I am primarily using Python pandas to work with these tables. Should I be using join() or merge() for this sort of task? What is wrong with the code, and how do I fix it?

PythonLearner
  • 13
  • 4
  • 1
    Welcome to Stack Overflow. Please read [ask] and clarify the context of the question. How do you have "tables" in the first place? Where does the `.merge` method come from? If you are using a specific library such as Pandas, please [edit] to add the relevant tags to the question. I already edited to fix the formatting of the tables; please read the [formatting help](https://stackoverflow.com/help/formatting) and make sure you understand how to post tabular data in a fixed-width font. Finally, please try to make a [mre]. – Karl Knechtel Jun 28 '22 at 02:34
  • Hi Karl, sorry I'm still rusty from formatting the tables. I'll edit the question to add in more information as well that you've helped me think of and thanks again for helping me format the tables – PythonLearner Jun 28 '22 at 02:36
  • I also edited to remove noise (conversational language) and cut straight to the point of the questions. Oh, one more thing: *did you try* using `join`? If so, how did you try, and what happened as a result? – Karl Knechtel Jun 28 '22 at 02:37
  • I googled up a lot of stackoverflow posts and other table merging methods. I tried using join via online guide by `merged_data=table1.join(table2, lsuffix="_left", rsuffix="_right")` I get the joined table with columns: `XS_left` `S_left` and `QTY_right` and `Weight_right` as output – PythonLearner Jun 28 '22 at 02:47

0 Answers0