Forum Discussion

jilespam's avatar
jilespam
Copper Contributor
Dec 12, 2024

How to reorganize complex data set into list view

Hi,

I have a data set that I'm trying to reformat to a table like list view.

The data set looks like the above screenshot where one record composes of multiple rows and columns with different data points. In the screenshot, one record would have all the following info. The cells with bolded text are the header/title of its related information. The info that follows the bolded cells are the related data.

 

How do I go about transforming this data so that each record is only in one row and all the data points are split into columns? Screenshot below of what I'm trying to achieve without manually editing everything one by one.

 

Thanks in advance.

excel

1 Reply

Sort By
  • Kidd_Ip's avatar
    Kidd_Ip
    MVP
    Dec 13, 2024

    Try this:

     

    import pandas as pd

# Load your data into a DataFrame
data = pd.read_excel('your_dataset.xlsx')

# Initialize an empty list to store the transformed data
transformed_data = []

# Iterate through the DataFrame and transform the data
for index, row in data.iterrows():
    record = {}
    for col in data.columns:
        if pd.notna(row[col]):
            record[col] = row[col]
    transformed_data.append(record)

# Convert the list of dictionaries back to a DataFrame
transformed_df = pd.DataFrame(transformed_data)

# Save the transformed data to a new Excel file
transformed_df.to_excel('transformed_dataset.xlsx', index=False)

     

Resources