Forum Discussion
jilespam
Dec 12, 2024Copper Contributor
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 d...
Kidd_Ip
Dec 13, 2024MVP
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)