<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>

CC BY-SA 4.0
2019-07-11T01:00:51.303Z
Really appreciate your help! Thanks. When I get a chance I will post my updated code.
Luke Stafford
CC BY-SA 4.0
2019-07-09T14:16:50.747Z
2019-07-09T15:35:34.727Z
2019-07-09T15:35:34.727Z
M__
<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>

CC BY-SA 4.0
2019-07-06T12:31:11.067Z
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.
terdon
CC BY-SA 4.0
2019-07-07T09:16:08.180Z
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.
Wouter De Coster
CC BY-SA 4.0
2019-07-07T11:56:10.057Z
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.
terdon
CC BY-SA 4.0
2019-07-07T21:06:18.570Z
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?
Kamil S Jaron
CC BY-SA 4.0
2019-07-08T04:23:56.477Z
Thanks for all resposes. @terdon that formatting link helped,
Luke Stafford
CC BY-SA 4.0
2019-07-08T04:24:34.993Z
@Kamil S Jaron I have tried to explain my csv example and what I need to acheive a bit better, hope that's helpful?
Luke Stafford
<python><data-retrieval><jupyter>
Raw Data frame manipulation in python
# 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,
Luke 
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. @terdon that formatting link helped,  
> **username_0**: @Kamil S Jaron 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. 

Parse You can simply remove the top rows of bumf data = data.iloc[6:] (I might have miss counted, plus see below),
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
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.
Index You then assign A1, A2, ... etc  as an index data.index = ['A1',
'A2', ]...etc..  you can reshape column 3 to automate this
to_numeric You might have to assign the CT values as floats
    or better to_numeric
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.
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.
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.  

---

