How to merge rows in a table that has been read from a word doc?

user_13041458 0
Viewed 42

I'm currently working on a small project whereby I'm collecting all of my brokerage platform's monthly statements, reading in a specific table for each month's statement, and then later on graphing my monthly portfolio value.

I'm struggling with fixing the apparently crappy formating that the table is being processed in. I would need a df that has data for each stock in just one row with data in each column.

My dataframe currently looks as follows:

# of Stocks Name Price Total Value Position
5 Apple Inc US0378331005 200 1000
5 Microsoft 500
Corporation
US5949181045 100
10 Something US123434534545 10 100

So I was wondering how to maybe write a for-loop that can do the following:

  1. If the value in any row of the first column is empty ( np.nan(df.loc[,0]) == True )
  2. Then copy the value in each column of that row and merge/concatenate it with each respective column in the row above
  3. Delete the row that has an empty first column
  4. Next

Something along the lines of:

    for row in df.itertuples():
        if np.nan(df.iloc[[0]])==True:
            #Take each value in this row and append to the respective column above
            df.drop(df.index[row])
        else:
            next()

I'm not even sure how to start on that line of code though.

python-docx pandas dataframe python
1 Answers

Try with groupby:

# Print `blocks` to see why it represents the blocks we are interested in
blocks = df['# of Stocks'].notna().cumsum()

df.groupby(blocks).agg({
    '# of Stocks': 'first',
    'Name' : ' '.join,
    'Price': 'first',
    'Total Value Position': 'first'
})
user_42384080
Related