[FIXED] Pandas : Creating Dataframe from Dictionary of Tuples

Issue

I have some example data. Each tuple is represented as (project_name, target_version) and has a list of associated issues.

Ideally, I want to create a Dataframe where I can group the project names so you can easily visualize a roadmap for each product. This is what I currently have.

import pandas as pd

data_dict = {
    ('XBOX360', '1.0'): ['Fix bug', 'Improve this', 'Improve that'],
    ('PS5', '1.0'): ['Status Report'],
    ('PC', '1.5'): ['Fix this', 'Fix that'],
    ('XBOX360', '2.3'): ['Update this'],
    ('PC', '3.5'): ['Progress report']
}

# Create DataFrame using pd.DataFrame.from_dict with orient='index'
df = pd.DataFrame.from_dict(data_dict, orient='index')

# Display the DataFrame
print(df.transpose())

It will display 5 columns, but ideally I want 3 columns to be displayed (XBOX360, PS5, PC).

Each column will then be broken down by their respective target versions. So XBOX360 will have target version 1.0 and 2.3 stacked on top of one another to visually enhance the Dataframe.

Example output: output

Is this possible? Or something similar?

I want this visually nice since I export the Dataframe to an excel sheet. However, if this solution isn't feasible is there any excel commands that can do this?

Initially, I tried using concat() and other pandas methods, but had no luck. I understand these pandas methods will create another copy of the Dataframe. It's too expensive, which is why I opted into reorganizing my dict.

Solution

Sometimes it's easier to reshape your data before creating a dataframe:

remap = {}
for (target, version), issues in data_dict.items():
    l = remap.setdefault(target, [])
    l += [version, *issues]

df = pd.DataFrame.from_dict(remap, orient='index').T

Output:

>>> df
        XBOX360            PS5               PC
0           1.0            1.0              1.5
1       Fix bug  Status Report         Fix this
2  Improve this           None         Fix that
3  Improve that           None              3.5
4           2.3           None  Progress report
5   Update this           None             None

Edit

m = df.fillna('').replace('[^\d]', '', regex=True).ne('')

def highlight(df):
    m = df.fillna('').replace('[^\d]', '', regex=True).ne('')
    return np.where(m, 'background-color:lightblue', '')

df.style.apply(highlight).to_excel('output.xlsx', index=False)

Answered By - Corralien