Python pandas dataframe, column names appear as strings and cannot be involved-CodePudding

i m using the an csv file of the following format:

"LatD", "LatM", "LatS", "NS", "LonD", "LonM", "LonS", "EW", "City", "State"
   41,    5,   59, "N",     80,   39,    0, "W", "Youngstown", OH
   42,   52,   48, "N",     97,   23,   23, "W", "Yankton", SD
   46,   35,   59, "N",    120,   30,   36, "W", "Yakima", WA
   42,   16,   12, "N",     71,   48,    0, "W", "Worcester", MA
   43,   37,   48, "N",     89,   46,   11, "W", "Wisconsin Dells", WI

When it with:

cities = pd.read_csv("cities.csv")

And try to invoke a column with:

print(cities[cities.City.str.contains("Y")])

I get this error:

AttributeError: 'DataFrame' object has no attribute 'City'

I tried using to fix it but the problem remains:

cities.columns = cities.columns.str.strip()

Is this related to the quotes on the first row? And if so is there a way to convert them programmatically?

Thank you in advance.

CodePudding user response：

Try this:

cities["City"].str.contains('Y').any()

reference to learn more: https://www.statology.org/pandas-check-if-column-contains-string/

CodePudding user response：

You can try to replace " with empty string (as long as columns doesn't contain other " as data, it will work):

from io import StringIO

with open("cities.csv", "r") as f_in:
    df = pd.read_csv(
        StringIO(f_in.read().replace('"', "")), sep=r"\s*,\s*", engine="python"
    )

print(df[df.City.str.contains("Y")])

Prints:

   LatD  LatM  LatS NS  LonD  LonM  LonS EW        City State
0    41     5    59  N    80    39     0  W  Youngstown    OH
1    42    52    48  N    97    23    23  W     Yankton    SD
2    46    35    59  N   120    30    36  W      Yakima    WA