-1

I have below content in my csv file, which I am trying to read last column from csv using pandas. And after successful fetching of last column x2. I am unable to access the column from the output. Instead if I try to index the x2 column, I am getting rows. But I want columns.

CSV File:

symbol,close,low,high,x0,x1,x2
ACC,-1.41,1241.5,1270.0,-1.41,"[1221241.5, 1270, -1.41]","[1241.5, 1270, -1.41]"
ADANIPORTS,-1.61,336.85,346.85,-1.61,"[336.85, 346.85, -1.61]","[336.85, 346.85, -1.61]"
ADANITRANS,3.45,202.8,211.2,3.45,"[202.8, 211.2, 3.45]","[202.8, 211.2, 3.45]"

Code

import pandas as pd
df = pd.read_csv("tickerdb.csv", index_col=0)
print((df.iloc[:, -1]))

Output

symbol
ACC           [1241.5, 1270, -1.41]
ADANIPORTS       [336.85, 346.85, -1.61]
ADANITRANS          [202.8, 211.2, 3.45]

I tried accessing the column from the list, but I am getting rows instead.

print((df.iloc[:, -1][1]))

New Output:

[336.85, 346.85, -1.61]

But expected output is a column from the list, not row:

1270
346.85
211.2

Second solution which I am also fine with would be if somehow I can get 

#Current output from last column using df.iloc
ACC           [1241.5, 1270, -1.41]
ADANIPORTS       [336.85, 346.85, -1.61]
ADANITRANS          [202.8, 211.2, 3.45]

#If I can get like below for x2 column is also fine for me.
symbol        low     high    change
ACC           1241.5  1270    -1.41
ADANIPORTS    336.85  346.85  -1.61
ADANITRANS    202.8   211.2    3.45

Any of above two solutions would be good for me. Thanks in advance for the help.

Improve this question
7
  • Are u looking for df.high or df['high'] Commented Jun 15, 2020 at 17:47
  • No, I want full column output to be printed from my csv column x2. i.e. I am looking for column of x2[1] Commented Jun 15, 2020 at 17:51
  • 1
    Check this post, stackoverflow.com/a/38088525/4985099 Commented Jun 15, 2020 at 18:04
  • Using @Sushanth tip, this resolves with df.x2.apply(lambda row: row[1]) Commented Jun 15, 2020 at 18:13
  • do you want output as new dataframe for second solution? Commented Jun 15, 2020 at 18:19

2 Answers 2

Reset to default
0

If you call df.head() you will see that you have 6 columns of data, but the last 2 columns (x1 and x2) consist of rows of python lists that have been turned into strings through the use of quotes:

"[336.85, 346.85, -1.61]"

is the value of column x2 on the second row.

From your desired output I think what you want is to get the value at index 1 on each row of column x2 (if that row were a list, not a string that looks like a list).

I think the best thing would be to consider reformating your data, but in the mean time here's hacky workaround for you:

df = pd.read_csv("tickerdb.csv", index_col=0)

def get_middle_value(series):
    middle = [row.split(', ')[1] for row in series]
    return middle


print(get_middle_value(df.x2))
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Yes it is giving me the list of x[2] correctly. Thanks a lot. It helped.
No problem Vishal glad I could be of help!
Just one thing, I would like to know as I am getting list now of x2[1], so to convert list to new dataframe as my desired solution. how can I do it..?
Do you mean you would like to add that column to the data frame? If so you could write: df['middle_value'] = get_middle_value(df.x2) But if you mean a whole new dataframe you could write: new_df = pd.DataFrame(get_middle_value(df.x2))
0

Try this:

from ast import literal_eval

df2 = pd.DataFrame(df.x2.apply(lambda x: literal_eval(x)).tolist(), columns=['low', 'high', 'change'])
df2.insert(0, column='symbol', value=df.symbol)

Output:

       symbol      low     high  change
0         ACC  1241.50  1270.00   -1.41
1  ADANIPORTS   336.85   346.85   -1.61
2  ADANITRANS   202.80   211.20    3.45
Improve this answer

1 Comment

This is a quick solution. Working great.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.