Grouped excel data to pandas dataframe

user_7158527 0
Viewed 211

I have data like this in the excel sheet,

enter image description here

when try to read the excel

read_df_from_excel = pd.read_excel('output.xlsx', sheetname='Sheet1')

I see 7 rows, test1 and test2 are added to only 2 rows and other rows has NaN as below:

enter image description here

Is there any way to read the grouped items from excel into grouped pandas dataframe?

read_df_from_excel.to_dict(orient='r')

[{'test': 'test1', 'segment': 's1', 'c1': 23, 'c2': 7, 'c3': 78, 'c4': 1231},
 {'test': nan, 'segment': 's2', 'c1': 32, 'c2': 123, 'c3': 5, 'c4': 745},
 {'test': nan, 'segment': 's3', 'c1': 123, 'c2': 13, 'c3': 1, 'c4': 13},
 {'test': nan, 'segment': 's4', 'c1': 7, 'c2': 123, 'c3': 5, 'c4': 12},
 {'test': 'test2', 'segment': 's1', 'c1': 456, 'c2': 5, 'c3': 41, 'c4': 22},
 {'test': nan, 'segment': 's2', 'c1': 31, 'c2': 1, 'c3': 213, 'c4': 8},
 {'test': nan, 'segment': 's3', 'c1': 13, 'c2': 1, 'c3': 4, 'c4': 323},
 {'test': nan, 'segment': 's4', 'c1': 13, 'c2': 2, 'c3': 23, 'c4': 23}]

Appreciate the help!

openpyxl pandas-groupby pandas dataframe excel
1 Answers

Not sure if there is a way to read merged cells, but a simple processing step like this can solve this

read_df_from_excel['test'] = read_df_from_excel['test'].fillna(method='ffill')
user_141643740
Related