CODEWITHSUNDEEP
pythonpandasdataframenested-lists
jtam
jtam

Reputation: 902

Take a list of lists, split a list, and then convert into dataframe

I am trying to get a list of lists that looks like this (let's call this 'All'):

[['Albany County, Wyoming', '56', '001'],
 ['Big Horn County, Wyoming', '56', '003'],
 ['Campbell County, Wyoming', '56', '005'],...

and convert it into a dataframe that will look like this:

COUNTY            STATE     FIPS1 FIPS2
'Albany County'   'Wyoming'  '56'  '001'
'Big Horn County' 'Wyoming'  '56'  '003'
'Campbell County' 'Wyoming'  '56'  '005'

This is what I have thus far for the list of lists :

for index, line in enumerate(All):
    All[index] = line[0].split(','), line[1:]

pd.DataFrame(All)

But this is returning:

                            0          1
0        [Albany County,  Wyoming]  [56, 001]
1      [Big Horn County,  Wyoming]  [56, 003]
2      [Campbell County,  Wyoming]  [56, 005]

Thanks in advance for your help!

Upvotes: 1

Views: 60

Answers (2)

Joe T. Boka
Joe T. Boka

Reputation: 6581

You can also try this:

lst = [['Albany County, Wyoming', '56', '001'],
 ['Big Horn County, Wyoming', '56', '003'],
 ['Campbell County, Wyoming', '56', '005']]

df = pd.DataFrame(lst)
df.columns = ['COUNTY_STATE', 'FIPS1', 'FIPS2']
print(df)
               COUNTY_STATE FIPS1 FIPS2
0    Albany County, Wyoming    56   001
1  Big Horn County, Wyoming    56   003
2  Campbell County, Wyoming    56   005

ALL = pd.DataFrame(df['COUNTY_STATE'].str.split(',').tolist(), columns = ['COUNTY','STATE'])
ALL[['FIPS1', 'FIPS2']] = df[['FIPS1', 'FIPS2']]
print(ALL)
            COUNTY     STATE FIPS1 FIPS2
0    Albany County   Wyoming    56   001
1  Big Horn County   Wyoming    56   003
2  Campbell County   Wyoming    56   005

Upvotes: 0

piRSquared
piRSquared

Reputation: 294526

All = [
    ['Albany County, Wyoming', '56', '001'],
    ['Big Horn County, Wyoming', '56', '003'],
    ['Campbell County, Wyoming', '56', '005']
]

pd.DataFrame(
    [line[0].split(', ') + line[1:] for line in All],
    columns=['COUNTY', 'STATE', 'FIPS1', 'FIPS2']
)

            COUNTY    STATE FIPS1 FIPS2
0    Albany County  Wyoming    56   001
1  Big Horn County  Wyoming    56   003
2  Campbell County  Wyoming    56   005

Or

df = pd.DataFrame(All, columns=['COUNTY, STATE', 'FIPS1', 'FIPS2'])

col = 'COUNTY, STATE'
df[col].str.extract(
    '(?P<COUNTY>.*), (?P<STATE>.*)', expand=True
).join(df.drop(col, 1))

            COUNTY    STATE FIPS1 FIPS2
0    Albany County  Wyoming    56   001
1  Big Horn County  Wyoming    56   003
2  Campbell County  Wyoming    56   005

Upvotes: 2

Related Questions