Paul Stoner Paul Stoner - 10 months ago 128
Python Question

pandas Read undelimited text file to dataframe

I am very new to pandas. Up until now I've been learning pandas using csv files and excel spreadsheets.

Now I am faced with converting a text file to a dataframe. The text files is what I call sequential data. The format of the file is:

State Name
City Name
State Name
City Name
City Name
City Name

All 50 states plus US territories are listed but the number of cities varies. I need to convert this into a dataframe like

[[State Name, City Name1],[State Name, City Name2],...]

Using pandas read_table() method, I've been able to at least read the file into a dataframe, but now I'm not certain how to get it into the correct State Name City Name format.

I also have a dictionary of State Name/State 2 letter abbreviations available. The format of the dictionary is

{'OH':'OHIO', 'KY':'Kentucky',...}

Is there a way I can use this dictionary, loop over the file and separate the state and city? or is there an easier way to accomplish this?

Thank you

EDIT - Sample of Text File
A sample of the text file is listed below. Also, please not that I am unable to alter the file.

Auburn (Auburn University)[1]
Florence (University of North Alabama)
Jacksonville (Jacksonville State University)[2]
Livingston (University of West Alabama)[2]
Montevallo (University of Montevallo)[2]
Troy (Troy University)[2]
Tuscaloosa (University of Alabama, Stillman College, Shelton State)[3][4]

Tuskegee (Tuskegee University)[5]
Fairbanks (University of Alaska Fairbanks)[2]
Flagstaff (Northern Arizona University)[6]
Tempe (Arizona State University)
Tucson (University of Arizona)

Answer Source

I would create a cities list populated with (state_name, city_name) tuples, and then turn this list of tuples into a DataFrame.

For that you'll need a precompiled list of all the states that appear in your text file, so that we can identify when the file cursor is on a state line or on a city line.

cities = []
list_of_states = ['Alaska', ..., 'Ohio', ...]

with open('file.csv') as f:
    for line in f:
        if line in list_of_states:
            state = line
            cities.append((state, line))

df = pandas.DataFrame(cities)