title: Concatenate text from multiple rows and columns into a single text string in pandas dataframe?
body: <p><a href="https://i.stack.imgur.com/ovKI0.png" rel="nofollow noreferrer">Picture of raw excel file format</a></p>
  
  <p><a href="https://i.stack.imgur.com/8y7im.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/8y7im.png" alt="enter image description here"></a></p>
  
  <p>Attached picture reference for how my raw excel data looks like. Loaded into pandas dataframe. Each message is spread across multiple columns with occasional blank cells in between each column. Also, the separator for each message is 2 consecutive empty rows. To combine the message, I merged all 5 columns :</p>
  
  <pre><code>df['New Column'] = df['Column1'].astype(str) + df['Column2'].astype(str) + df['Column3'].astype(str) + df['Column4'].astype(str) + df['Column5'].astype(str)
  
  </code></pre>
  
  <p>The problem now is : - </p>
  
  <ol>
  <li><p>How do I cluster each message into a single cell / pandas row because right now, the one message (E.g Message 1) can and may spread over a few rows</p></li>
  <li><p>How do I format the messages using 2 empty rows as a seperator ? </p></li>
  <li><p>After merging 5 columns into 1, there are many NaN values which I'm not sure what's the best way to handle given by very complicated and messy data structure. </p></li>
  </ol>
  
  <p>Essentially, this is the desired output <a href="https://i.stack.imgur.com/9dtWT.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/9dtWT.png" alt="Desired output"></a></p>
  
  <p>Hope I'm clear enough, thank you for your help in advance. </p>