🔒 How to read columns

[half jolt]

Hi! I need some help to read certain columns. I need to read the columns Lo 30, Med 40, and Hi 30 in the CSV file for my homework, but I can't seem to do it. The first picture is my code, the second one is the CSV file that I need to read, and the third one is my homework that I need to do.

[silent trellis]

@half jolt

Python help channel opened

Remember to:

• Ask your Python question, not if you can ask or if there's an expert who can help.
• Show a code sample as text (rather than a screenshot) and the error message, if you've got one.
• Explain what you expect to happen and what actually happens.

:warning: Do not pip install anything that isn't related to your question, especially if asked to over DMs.

Closes after a period of inactivity, or when you send !close.

[uneven shore]

The first picture has a syntax error in the line na_values= with an extra ' at the end. When you say you can't seem to do it, what are the issues you've run into?

[half jolt]

It seems that it cant find the columns

even after I remove an extra '

[uneven shore]

The first thing you want to do is ensure that the first 12 lines of the input file are not polluting your dataframe, since there is no data. You can use skiprows= in read_csv to skip those lines and properly read in the data into a dataframe

do this instead of header=13

There are no dates in your data, so I don't think parse_dates=True is useful

[half jolt]

I tried to inplant skiprow= but same error

my teacher said that this part of my homework is the hardest part. Now I know why

[uneven shore]

can you put the csv into this chat?

[uneven shore]

did you write the first bit of code yourself? where you read in the data

@half jolt

[half jolt]

No

It was already there

My teacher said that I have to modify it

[uneven shore]

try using

df = pd.read_csv(
    Path('data/Portfolios_Formed_on_ME_daily.csv.gz'),
    header=7,
    index_col=0,
    parse_dates=True,
    na_values=['-99.99', '-999']
)
``` it seems that the header index is wrong, so your columns are not the correct names. When you load the data in, you should always check it's correct by printing it and looking at it

alternatively,

df = pd.read_csv(
    Path('data/Portfolios_Formed_on_ME_daily.csv.gz'),
    skiprows=12,
    index_col=0,
    parse_dates=True,
    na_values=['-99.99', '-999']
)

gives me the same dataframe

[uneven shore]

that means not all values in your columns are just numbers (floats), so you need to look through the data (using code, there are too many lines to sift through manually), find where the bad data is, replace it with good values and then do the calculations

I'll give you a hint, this is the data half-way down the csv (rows 25140-25143)

  Equal Weighted Returns -- Daily
,<= 0,Lo 30,Med 40,Hi 30,Lo 20,Qnt 2,Qnt 3,Qnt 4,Hi 20,Lo 10,Dec 2,Dec 3,Dec 4,Dec 5,Dec 6,Dec 7,Dec 8,Dec 9,Hi 10

[half jolt]

ahhh so row 13 is not the only row that's not a float

[uneven shore]

🙂

these are 2 csvs in one basically

[half jolt]

do I need to read the csv twice then?

or just try to code something to find where the data is

[uneven shore]

if you print(df.iloc[25124:25131]) you will see where the second csv begins

I'll leave the solution up to you. You can use python to read through the file line by line and then split it when you get to the Equal Weighted Returns -- Daily line, or split the existing dataframe into two, assigning the correct column names to the second df

[half jolt]

Perfect thank you so much!

[uneven shore]

just to be clear, the second option is 3 lines of code 🙂

[half jolt]

Got it!

[silent trellis]

Python help channel closed using Discord native close action

This help channel has been closed. Feel free to create a new post in #1035199133436354600. To maximize your chances of getting a response, check out this guide on asking good questions.