Answers.Body: <pre><code>data = pd.read_excel ("2019-02-27_161601 AtWAKL8 different version expressions.xls", sheet_name='Results').fillna(0)
  
  data.to_csv('df1' + '.csv', index=True)
  
  df1 = pd.read_csv ("df1.csv")
  </code></pre>
  
  <p>This doesn't make much sense ... you read in Excel to a dataframe, you then read out to csv - the dataframe doesn't have an index, but you include an index and then read it in again. This could do more harm than good. </p>
  
  <p>Anyway your supervisor is right it is very easy to parse this data using pandas, although there will be more expertise doing this via R. </p>
  
  <p>I don't want to peer through the gloom of a CSV output, but I would like to highlight how easy it is to parse this stuff into a dataframe. The dataframe '<code>data</code>' is fine. </p>
  
  <ol>
  <li><strong>Parse</strong> You can simply remove the top rows of bumf <code>data = data.iloc[6:]</code> (I might have miss counted, plus see below),</li>
  <li><strong>Headers</strong> You will probably have to remove the "headers" row to a list and
  then introduce back onto the dataframe using <code>data.columns =
  ['header1', 'header2' ...]</code> ... rather this is the list (array in Perl) which you can automatically generate using a magic <code>to_list</code> type pandas command which I forget, its not <code>list</code> - its cuter</li>
  <li><strong>Columns</strong> You next might use <code>drop</code> to remove the columns you don't want (that might be alot of columns) or else make a subset of
  the dataframe into a new dataframe, you can also use <code>iloc</code> for
  columns too.</li>
  <li><strong>Index</strong> You then assign A1, A2, ... etc  as an index <code>data.index = ['A1',
  'A2', ]</code>...etc..  you can reshape column 3 to automate this</li>
  <li><strong>to_numeric</strong> You might have to assign the CT values as floats
      or better <code>to_numeric</code></li>
  <li><strong>Extrapolation</strong> You would then define a subroutine (or whatever they call them in
  Python) so perform the extrapolation and use the <code>apply</code> command on
  your dataframe and assign it to a new column, where the input is your CT values.</li>
  <li><strong>Output</strong> Finally, issue your <code>to_csv</code> command <code>to_pickle</code> is also a good idea because you have a df that is worth importing at a future date.</li>
  <li><strong>Plotting</strong> this is easy using <code>data.plot</code> which appears to be some sort of inbuilt matplotlib, keep in mind seaborn DONT try using R's <code>ggplot2</code> within Python using rpy2 .... its a NIGHTMARE to get working and doesn't support the latest <code>ggplot2</code> (!!!). R
  s <code>ggplot2</code> is stronger  than seaborn or matplotlib (providing you are working within R), but these two are good for quick graphs.</li>
  </ol>
  
  <p>If I had time I'd write the code for you, but I'm afraid I'll have to leave it there. I'm still in debt with a Perl efetch script I promised a previous OP.</p>
  
  <p>In summary, Python pandas is fabulous stuff for this purpose, I love it. Doing this via Perl and "subprocessing" inside R... its doable, well in this particular case I suspect its just as easy, but generally its alot more code and often fiddly.</p>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2019-07-11T01:00:51.303Z
Answers.Text: Really appreciate your help! Thanks. When I get a chance I will post my updated code.
Answers.UserDisplayName: Luke Stafford
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2019-07-09T14:16:50.747Z
Answers.LastActivityDate: 2019-07-09T15:35:34.727Z
Answers.LastEditDate: 2019-07-09T15:35:34.727Z
Answers.UserDisplayName: M__
Body: <p>Using python 3 I need to process qPCR sequencing raw data outputs by searching for the first occurrence of a user defined string and then making a new data frame using all lines after that string. I am trying to find solutions in the pandas doc but so far unsuccessful.</p>
  
  <p>This is a raw output .csv file that I need to process. (couldn't paste complete csv as exceeds character limit, this is lines 40-50 and am hoping this text is useful?). I need to tell pandas to create a new data frame that 1. starts at the line containg the first occurance of str("Sample Name") with that line as header and containing all lines following. And then 2., only including columns ("Sample Name"), ("Target Name"), ("CT"). </p>
  
  <p>Could someone please help me so that I can use python to help me analyze biological data?
  Many thanks,
  Luke </p>
  
  <pre><code>40,Quantification Cycle Method,Ct,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
  
  41,Signal Smoothing On,true,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
  
  42,Stage where Melt Analysis is performed,Stage3,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
  
  43,Stage/ Cycle where Ct Analysis is performed,"Stage2, Step2",,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
  
  44,User Name,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
  
  45,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
  
  46,Well,Well Position,Omit,Sample Name,Target Name,Task,Reporter,Quencher,Quantity,Quantity Mean,SE,RQ,RQ Min,RQ Max,CT,Ct Mean,Ct SD,Delta Ct,Delta Ct Mean,Delta Ct SD,Delta Ct SE,Delta Delta Ct,Automatic Ct Threshold,Ct Threshold,Automatic Baseline,Baseline Start,Baseline End,Amp Status,Comments,Cq Conf,CQCONF,HIGHSD,OUTLIERRG,Tm1,Tm2,Tm3,Tm4
  
  47,1,A1,False,WT1,AtTubulin,UNKNOWN,SYBR,None,,,,,,,23.357698440551758,23.4766845703125,0.5336655378341675,,,,,,True,20959.612776965325,True,3,17,Amp,,0.9588544573203085,N,Y,N,81.40960693359375,,,
  
  48,2,A2,False,WT1,AtTubulin,UNKNOWN,SYBR,None,,,,,,,24.05980110168457,23.4766845703125,0.5336655378341675,,,,,,True,20959.612776965325,True,3,15,Amp,,0.9592687354496955,N,Y,N,81.40960693359375,,,
  
  49,3,A3,False,WT1,AtTubulin,UNKNOWN,SYBR,None,,,,,,,23.012556076049805,23.4766845703125,0.5336655378341675,,,,,,True,20959.612776965325,True,3,16,Amp,,0.9592714462250367,N,Y,N,81.40960693359375,,,
  
  50,4,A4,False,fla11fla12-1,AtTubulin,UNKNOWN,SYBR,None,,,,,,,23.803699493408203,24.419523239135742,0.5669151544570923,,,,,,True,20959.612776965325,True,3,17,Amp,,0.9671570584141241,N,Y,N,81.40960693359375,,,
  </code></pre>
  
  <p>This is the code that I have so far:</p>
  
  <pre><code>import pandas as pd
  
  import numpy as np
  
  import matplotlib.pyplot as plt
  
  data = pd.read_excel ("2019-02-27_161601 AtWAKL8 different version expressions.xls", sheet_name='Results').fillna(0)
  
  data.to_csv('df1' + '.csv', index=True)
  
  df1 = pd.read_csv ("df1.csv")
  </code></pre>
  
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2019-07-06T12:31:11.067Z
Comments.Text: Please [edit] your question and add i) an example of your input data, ii) an explanation of the data frame you want to build from that data and iii) the code you have so far so we don't reinvent the wheel.
Comments.UserDisplayName: terdon
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2019-07-07T09:16:08.180Z
Comments.Text: Don't use grep using subprocess to filter dataframes. That's crazy :-D
  Follow a pandas tutorial: the filtering that you want to do on your dataframe can be done with pandas commands, no need to use grep. If you are using python, avoid mixing your code with shell commands.
Comments.UserDisplayName: Wouter De Coster
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2019-07-07T11:56:10.057Z
Comments.Text: Please don't give us photographs of your data! We need your _actual_ data so we can test any solutions we come up with. Just save the excel file as csv or tsv or something and post the first few lines here. And paste the errors as well. You can use the [formatting tools](https://bioinformatics.stackexchange.com/help/formatting) to make all this look like code.
Comments.UserDisplayName: terdon
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2019-07-07T21:06:18.570Z
Comments.Text: What is that header about in your csv file? I suppose that a csv file should be just a comma-separated table. What is the first column? line numbers? Why do they start at 32? I understand that starting with python might be difficult, have you tried some tutorials?
Comments.UserDisplayName: Kamil S Jaron
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2019-07-08T04:23:56.477Z
Comments.Text: Thanks for all resposes. @terdon that formatting link helped,
Comments.UserDisplayName: Luke Stafford
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2019-07-08T04:24:34.993Z
Comments.Text: @Kamil S Jaron I have tried to explain my csv example and what I need to acheive a bit better, hope that's helpful?
Comments.UserDisplayName: Luke Stafford
Tags: <python><data-retrieval><jupyter>
Title: Raw Data frame manipulation in python
ThreadText: # Raw Data frame manipulation in python
  
  (Asked by: username_0 on 2019-07-06)
  
  Using python 3 I need to process qPCR sequencing raw data outputs by searching for the first occurrence of a user defined string and then making a new data frame using all lines after that string. I am trying to find solutions in the pandas doc but so far unsuccessful.
  
  This is a raw output .csv file that I need to process. (couldn't paste complete csv as exceeds character limit, this is lines 40-50 and am hoping this text is useful?). I need to tell pandas to create a new data frame that 1. starts at the line containg the first occurance of str("Sample Name") with that line as header and containing all lines following. And then 2., only including columns ("Sample Name"), ("Target Name"), ("CT").
  
  Could someone please help me so that I can use python to help me analyze biological data?
  Many thanks,
  username_0
  
  ```
  40,Quantification Cycle Method,Ct,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
  
  41,Signal Smoothing On,true,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
  
  42,Stage where Melt Analysis is performed,Stage3,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
  
  43,Stage/ Cycle where Ct Analysis is performed,"Stage2, Step2",,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
  
  44,User Name,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
  
  45,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
  
  46,Well,Well Position,Omit,Sample Name,Target Name,Task,Reporter,Quencher,Quantity,Quantity Mean,SE,RQ,RQ Min,RQ Max,CT,Ct Mean,Ct SD,Delta Ct,Delta Ct Mean,Delta Ct SD,Delta Ct SE,Delta Delta Ct,Automatic Ct Threshold,Ct Threshold,Automatic Baseline,Baseline Start,Baseline End,Amp Status,Comments,Cq Conf,CQCONF,HIGHSD,OUTLIERRG,Tm1,Tm2,Tm3,Tm4
  
  47,1,A1,False,WT1,AtTubulin,UNKNOWN,SYBR,None,,,,,,,23.357698440551758,23.4766845703125,0.5336655378341675,,,,,,True,20959.612776965325,True,3,17,Amp,,0.9588544573203085,N,Y,N,81.40960693359375,,,
  
  48,2,A2,False,WT1,AtTubulin,UNKNOWN,SYBR,None,,,,,,,24.05980110168457,23.4766845703125,0.5336655378341675,,,,,,True,20959.612776965325,True,3,15,Amp,,0.9592687354496955,N,Y,N,81.40960693359375,,,
  
  49,3,A3,False,WT1,AtTubulin,UNKNOWN,SYBR,None,,,,,,,23.012556076049805,23.4766845703125,0.5336655378341675,,,,,,True,20959.612776965325,True,3,16,Amp,,0.9592714462250367,N,Y,N,81.40960693359375,,,
  
  50,4,A4,False,fla11fla12-1,AtTubulin,UNKNOWN,SYBR,None,,,,,,,23.803699493408203,24.419523239135742,0.5669151544570923,,,,,,True,20959.612776965325,True,3,17,Amp,,0.9671570584141241,N,Y,N,81.40960693359375,,,
  
  ```
  
  This is the code that I have so far:
  
  ```
  import pandas as pd
  
  import numpy as np
  
  import matplotlib.pyplot as plt
  
  data = pd.read_excel ("2019-02-27_161601 AtWAKL8 different version expressions.xls", sheet_name='Results').fillna(0)
  
  data.to_csv('df1' + '.csv', index=True)
  
  df1 = pd.read_csv ("df1.csv")
  
  ```
  
  **Tags:** python, data-retrieval, jupyter
  
  > **Comments:**
  > **username_1**: Please [edit] your question and add i) an example of your input data, ii) an explanation of the data frame you want to build from that data and iii) the code you have so far so we don't reinvent the wheel.  
  > **username_2**: Don't use grep using subprocess to filter dataframes. That's crazy :-D Follow a pandas tutorial: the filtering that you want to do on your dataframe can be done with pandas commands, no need to use grep. If you are using python, avoid mixing your code with shell commands.  
  > **username_1**: Please don't give us photographs of your data! We need your _actual_ data so we can test any solutions we come up with. Just save the excel file as csv or tsv or something and post the first few lines here. And paste the errors as well. You can use the [formatting tools](https://bioinformatics.stackexchange.com/help/formatting) to make all this look like code.  
  > **username_3**: What is that header about in your csv file? I suppose that a csv file should be just a comma-separated table. What is the first column? line numbers? Why do they start at 32? I understand that starting with python might be difficult, have you tried some tutorials?  
  > **username_0**: Thanks for all resposes. @username_1 that formatting link helped,  
  > **username_0**: @username_3 I have tried to explain my csv example and what I need to acheive a bit better, hope that's helpful?  
  
  ---
  
  ## 1 answers
  
  ### Answer by username_4 on 2019-07-09. Score: 2
  
  ```
  data = pd.read_excel ("2019-02-27_161601 AtWAKL8 different version expressions.xls", sheet_name='Results').fillna(0)
  
  data.to_csv('df1' + '.csv', index=True)
  
  df1 = pd.read_csv ("df1.csv")
  
  ```
  
  This doesn't make much sense ... you read in Excel to a dataframe, you then read out to csv - the dataframe doesn't have an index, but you include an index and then read it in again. This could do more harm than good.
  
  Anyway your supervisor is right it is very easy to parse this data using pandas, although there will be more expertise doing this via R.
  
  I don't want to peer through the gloom of a CSV output, but I would like to highlight how easy it is to parse this stuff into a dataframe. The dataframe '`data`' is fine.
  
  1. **Parse** You can simply remove the top rows of bumf `data = data.iloc[6:]` (I might have miss counted, plus see below),
  2. **Headers** You will probably have to remove the "headers" row to a list and
     then introduce back onto the dataframe using `data.columns =
     ['header1', 'header2' ...]` ... rather this is the list (array in Perl) which you can automatically generate using a magic `to_list` type pandas command which I forget, its not `list` - its cuter
  3. **Columns** You next might use `drop` to remove the columns you don't want (that might be alot of columns) or else make a subset of
     the dataframe into a new dataframe, you can also use `iloc` for
     columns too.
  4. **Index** You then assign A1, A2, ... etc as an index `data.index = ['A1',
     'A2', ]`...etc.. you can reshape column 3 to automate this
  5. **to\_numeric** You might have to assign the CT values as floats
     or better `to_numeric`
  6. **Extrapolation** You would then define a subroutine (or whatever they call them in
     Python) so perform the extrapolation and use the `apply` command on
     your dataframe and assign it to a new column, where the input is your CT values.
  7. **Output** Finally, issue your `to_csv` command `to_pickle` is also a good idea because you have a df that is worth importing at a future date.
  8. **Plotting** this is easy using `data.plot` which appears to be some sort of inbuilt matplotlib, keep in mind seaborn DONT try using R's `ggplot2` within Python using rpy2 .... its a NIGHTMARE to get working and doesn't support the latest `ggplot2` (!!!). R
     s `ggplot2` is stronger than seaborn or matplotlib (providing you are working within R), but these two are good for quick graphs.
  
  If I had time I'd write the code for you, but I'm afraid I'll have to leave it there. I'm still in debt with a Perl efetch script I promised a previous OP.
  
  In summary, Python pandas is fabulous stuff for this purpose, I love it. Doing this via Perl and "subprocessing" inside R... its doable, well in this particular case I suspect its just as easy, but generally its alot more code and often fiddly.
  
  > **Comments:**
  > **username_0**: Really appreciate your help! Thanks. When I get a chance I will post my updated code.  
  
  ---
  
  