Say my data looks like this:
df = pd.DataFrame({'color': ['red', 'blue', 'green', 'red', 'blue', 'blue'], 'line': ['sunday', 'sunday', 'monday', 'monday', 'monday', 'tuesday'],
'group': ['1', '1', '2', '1', '1', '1'], 'value': ['a', 'b', 'a', 'c', 'a', 'b']})
color group line value
0 red 1 sunday a
1 blue 1 sunday b
2 green 2 monday a
3 red 1 monday c
4 blue 1 monday a
5 blue 1 tuesday b
Essentially, I want to get a list of strings for each color. For example, I want the color red to display each row and the value associated with it in its column. The trick is that I also want to show other lines related to colors from the same group. Corresponding values will be "invalid" for them. Thus, I want my output to look like this:
color line_1 line_1_value line_2 line_2_value line_3 line_3_value
0 red sunday a monday c tuesday not eligible
1 blue sunday b monday a tuesday b
2 green monday c
There are about ~ 50,000 unique “colors” that I need to make for this. I am sure that this is something relatively simple, but I do not yet have the knowledge or skills to understand this. Any help would be appreciated!