在Python中的Excel中选择正确的值 [英] Choosing the correct values in excel in Python

问题描述

概述:

我正在创建一个大数据集的图形，但是我创建了一个示例文本文档，以便更轻松地解决问题. 数据来自将另存为CSV的Excel文档.

I am creating a graph of a large data set, however i have created a sample text document so that it is easier to overcome the problems. The Data is from an excel document that will be saved as a CSV.

问题:

我能够编译将要绘制图形的数据(见下文)，但是我如何提取数据对于我要提取的所有不同的excel工作表都无效.

I am able to compile the data a it will graph (see below) However how i pull the data will not work for all of the different excel sheet i am going to pull off of.

更多问题的详细信息:

excel表格的Y值(标记为"Value"和"Value1")已从数字26和31中拉出(请参见图片和代码). 这是一个问题，因为每个图形的值26和31都不相同.

The Y-Values (Labeled 'Value' and 'Value1') are being pulled for the excel sheet from the numbers 26 and 31 (See picture and Code). This is a problem because the Values 26 and 31 will not be the same for each graph.

让我们来看一下这有更多的道理. 这是我的代码

Lets take a look for this to make more sense. Here is my code

import pandas as pd
import matplotlib.pyplot as plt


pd.read_csv('CSV_GM_NB_Test.csv').T.to_csv('GM_NB_Transpose_Test.csv,header=False)

df = pd.read_csv('GM_NB_Transpose_Test.csv', skiprows = 2)

DID = df['SN']
Value = df['26']

Value1 = df['31']


x= (DID[16:25])
y= (Value[16:25])
y1= (Value1[16:25])

"""
print(x,y)
print(x,y1)
"""

plt.plot(x.astype(int), y.astype(int))
plt.plot(x.astype(int), y1.astype(int))
plt.show()

输出:

数据集:

在注释下方，您会找到我的数据集的0bin，这是因为我没有足够的声誉来发布两个链接.

Below in the comments you will find the 0bin to my Data Set this is because i do not have enough reputation to post two links.

从数据集中可以看到

 X- DID   = Blue 
 Y-Value  = Green 
 Y-Value1 = Grey 
Troublesome Values = Red

问题再次出在Y值的数据是从SN下的值26,31的行10和11中提取的.

The problem again is that the data for the Y-Values are pulled from Row 10&11 from values 26,31 under SN

让我知道是否需要更多信息. 谢谢

Let me know if more information is needed. Thank you

推荐答案

不确定为什么要创建转置的CSV版本.也可以直接从原始数据进行操作.例如:

Not sure why you are creating the transposed CSV version. It is also possible to work directly from your original data. For example:

import pandas as pd
import numpy as np
import matplotlib.pyplot as plt

df = pd.read_csv('CSV_GM_NB_Test.csv', skiprows=8)

data = df.ix[:,19:].T
data.columns = df['SN']
data.plot()
plt.show()

这会给你:

您可以使用 pandas.DataFrame.ix() 使用整数位置为您提供数据的切片版本. [:,19:]表示要向后提供19列.最后的.T对其进行转置.然后，您可以使用.columns指定名称，将SN列的值用作列标题.

You can use pandas.DataFrame.ix() to give you a sliced version of your data using integer positions. The [:,19:] says to give you columns 19 onwards. The final .T transposes it. You can then apply the values for the SN column as column headings using .columns to specify the names.

这篇关于在Python中的Excel中选择正确的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！