Insert row into dataframe based on value in a specified column

Question

Not sure if this is even possible, but i am trying to insert the header of my dataframe into a new row when the value in the 'Strategy' column changes.

Current Output:

Amount Code Strategy
1,000 Fund_1 A
2,000 Fund_2 A
3,000 Fund_1 B
4,000 Fund_2 B
5,000 Fund_1 C
6,000 Fund_2 C

Desired Output:

Amount Code Strategy
1,000 Fund_1 A
2,000 Fund_2 A
Amount Code Strategy
3,000 Fund_1 B
4,000 Fund_2 B
Amount Code Strategy
5,000 Fund_1 C
6,000 Fund_2 C

Is there an easy way to do this?

Adding headers doesn't make sense here, it will also impact the numeric type of Amount. If you looking at splitting the dataframe by strategy, that would be a different problem — Vaishali, Apr 12 '19 at 20:42
The dataframe is going to be output to an excel file where someone is going to copy and paste the data into a template. The issue is that for each strategy, the data needs to be copied into a new template (so the headers are needed). The fact that the numeric type of Amount will be impacted shouldn't be a big deal since i won't be performing any other data manipulation on this dataframe once i output to excel. Is there a better way to go about this? — jrass, Apr 12 '19 at 20:50
You can create a dictionary or list of dataframes based on Strategy and read the list to excel. See [this](https://stackoverflow.com/questions/54046707/pandas-split-one-dataframe-into-multiple-dataframes) and [this](https://stackoverflow.com/questions/14225676/save-list-of-dataframes-to-multisheet-excel-spreadsheet) — Vaishali, Apr 12 '19 at 20:54
You can easily write different strategies to different excel files or to different worksheets of the same excel file. In excel, Even in the same worksheet, it is possible to filter to only one strategy if needed. — Quang Hoang, Apr 12 '19 at 20:56

score 1 · Accepted Answer · answered Apr 12 '19 at 22:11

Here's one way you could achieve this effect using reindexing and pandas.concat:

d = {x:x for x in df.columns}

header_rows = df.drop_duplicates('Strategy').drop(0).assign(**d)
header_rows.index -= 0.5

df_new = pd.concat([df, header_rows]).sort_index().reset_index(drop=True)
print(df_new)

   Amount    Code  Strategy
0   1,000  Fund_1         A
1   2,000  Fund_2         A
2  Amount    Code  Strategy
3   3,000  Fund_1         B
4   4,000  Fund_2         B
5  Amount    Code  Strategy
6   5,000  Fund_1         C
7   6,000  Fund_2         C

Insert row into dataframe based on value in a specified column

1 Answers1