Python Pandas- how to merge rows?

Question

I am working with data frame (which I read using pd.read_excel()) looking like this:


                            NAME     Month1   Month2
1                         Mean all  1386.16  1350.02
2                         Mean       NaN      NaN
3                     Subcategory1  1369.18  1288.48
4                    Subcategory2  1271.26  1270.59
5                    Subcategory3  2940.74  3042.16
6                    Subcategory1    NaN      NaN
7                           item1  1326.99  1280.72
8                           item2  1060.62  1053.34
9                           item3  4189.45  4280.25
10                          item4  1214.41  1213.15
11                  Subactegory2      NaN      NaN
12                         item1  1326.99  1280.72
13                          item2  1060.62  1053.34
14                          item3  4189.45  4280.25
15                          item4  1214.41  1213.15
16                   Subcategory3    NaN      NaN
17                          item1  1326.99  1280.72
18                          item2  1060.62  1053.34
19                          item3  4189.45  4280.25

I would like obtain somthing like this:

                            NAME        Month1   Month2
1                         Mean all      1386.16  1350.02
2                    Mean Subcategory1  1369.18  1288.48
3                    Mean Subcategory2  1271.26  1270.59
4                    Mean Subcategory3  2940.74  3042.16
5                   Subcategory1 item1  1326.99  1280.72
6                   Subcategory1 item2  1060.62  1053.34
7                   Subcategory1 item3  4189.45  4280.25
8                   Subcategory1 item4  1214.41  1213.15
9                   Subcategory2 item1  1326.99  1280.72
10                  Subcategory2 item2  1060.62  1053.34
11                  Subcategory2 item3  4189.45  4280.25
12                  Subcategory2 item4  1214.41  1213.15
13                  Subcategory3 item1  1326.99  1280.72
14                  Subcategory3 item2  1060.62  1053.34
15                  Subcategory3 item3  4189.45  4280.25

Any idea how can I add this Subcategory string to its items below?

user_10035985 · Answer

Try:

all_df = []
for _, g in df.groupby(df[["Month1", "Month2"]].isna().all(axis=1).cumsum()):
    if g[["Month1", "Month2"]].head(1).isna().all(axis=1).all():
        g["NAME"] = g["NAME"].iat[0] + " " + g["NAME"]
        all_df.append(g[1:])
    else:
        all_df.append(g)

print(pd.concat(all_df).reset_index(drop=True))

Prints:

                  NAME   Month1   Month2
0             Mean all  1386.16  1350.02
1    Mean Subcategory1  1369.18  1288.48
2    Mean Subcategory2  1271.26  1270.59
3    Mean Subcategory3  2940.74  3042.16
4   Subcategory1 item1  1326.99  1280.72
5   Subcategory1 item2  1060.62  1053.34
6   Subcategory1 item3  4189.45  4280.25
7   Subcategory1 item4  1214.41  1213.15
8   Subactegory2 item1  1326.99  1280.72
9   Subactegory2 item2  1060.62  1053.34
10  Subactegory2 item3  4189.45  4280.25
11  Subactegory2 item4  1214.41  1213.15
12  Subcategory3 item1  1326.99  1280.72
13  Subcategory3 item2  1060.62  1053.34
14  Subcategory3 item3  4189.45  4280.25

Python Pandas- how to merge rows?

1 Answers