77
$\begingroup$

I am trying to convert a list of lists which looks like the following into a Pandas Dataframe

[['New York Yankees ', '"Acevedo Juan"  ', 900000, ' Pitcher\n'], 
['New York Yankees ', '"Anderson Jason"', 300000, ' Pitcher\n'], 
['New York Yankees ', '"Clemens Roger" ', 10100000, ' Pitcher\n'], 
['New York Yankees ', '"Contreras Jose"', 5500000, ' Pitcher\n']]

I am basically trying to convert each item in the array into a pandas data frame which has four columns. What would be the best approach to this as pd.Dataframe does not quite give me what I am looking for.

Improve this question
$\endgroup$
0

4 Answers 4

Reset to default
89
$\begingroup$ 
import pandas as pd

data = [['New York Yankees', 'Acevedo Juan', 900000, 'Pitcher'], 
        ['New York Yankees', 'Anderson Jason', 300000, 'Pitcher'], 
        ['New York Yankees', 'Clemens Roger', 10100000, 'Pitcher'], 
        ['New York Yankees', 'Contreras Jose', 5500000, 'Pitcher']]

df = pd.DataFrame.from_records(data)
Improve this answer
$\endgroup$
3
  • 17
    $\begingroup$ You could refine it a bit more with: DataFrame.from_records(data, columns=['Team', 'Player', 'whatever-stat-is-that', 'position']) $\endgroup$
    – Juan Ignacio Gil
    Jan 11, 2018 at 10:14
  • 1
    $\begingroup$ Is there a way to specify the imports more specifically? E.g. I want to specify that DataFrame["Team"] must refer to the first item of each sublist (i.e. data[i][0]) and DataFrame["Position"] to refer to the last item of each sublist (i.e. data[i][-1])? $\endgroup$
    – Ivo
    Jan 17, 2019 at 15:20
  • $\begingroup$ @Ivo: Use the columns parameter of DataFrame.from_records. $\endgroup$
    – Emre
    Jan 17, 2019 at 21:27
22
$\begingroup$

Once you have the data:

import pandas as pd

data = [['New York Yankees ', '"Acevedo Juan"  ', 900000, ' Pitcher\n'], 
        ['New York Yankees ', '"Anderson Jason"', 300000, ' Pitcher\n'], 
        ['New York Yankees ', '"Clemens Roger" ', 10100000, ' Pitcher\n'], 
        ['New York Yankees ', '"Contreras Jose"', 5500000, ' Pitcher\n']]

You can create dataframe from the transposing the data:

data_transposed = zip(data)
df = pd.DataFrame(data_transposed, columns=["Team", "Player", "Salary", "Role"])

Another way:

df = pd.DataFrame(data)
df = df.transpose()
df.columns = ["Team", "Player", "Salary", "Role"]
Improve this answer
$\endgroup$
1
  • $\begingroup$ Your transposition example fails for me with "ValueError: 4 columns passed, passed data had 1 columns". Why the transposition anyway when this works: df = pd.DataFrame(data, columns=["Team", "Player", "Salary", "Role"]). $\endgroup$
    – Timothy C. Quinn
    Sep 6, 2022 at 12:43
7
$\begingroup$

You can just directly define it as a data frame as follows:

import pandas as pd

data = [['New York Yankees', 'Acevedo Juan', 900000, 'Pitcher'], 
        ['New York Yankees', 'Anderson Jason', 300000, 'Pitcher'], 
        ['New York Yankees', 'Clemens Roger', 10100000, 'Pitcher'], 
        ['New York Yankees', 'Contreras Jose', 5500000, 'Pitcher']]

data = pd.DataFrame(data)
Improve this answer
$\endgroup$
1
$\begingroup$

This one by far was the simplest:

import pandas as pd

data = [['New York Yankees', 'Acevedo Juan', 900000, 'Pitcher'], 
        ['New York Yankees', 'Anderson Jason', 300000, 'Pitcher'], 
        ['New York Yankees', 'Clemens Roger', 10100000, 'Pitcher'], 
        ['New York Yankees', 'Contreras Jose', 5500000, 'Pitcher']]

data = pd.DataFrame(data)

now, if the keys are the first list in the list of lists (data[0]), you can assign them to column headers in the dataframe like so:

import pandas as pd

data = [['key1', 'key2', key3, 'key4'], 
    ['New York Yankees', 'Anderson Jason', 300000, 'Pitcher'], 
    ['New York Yankees', 'Clemens Roger', 10100000, 'Pitcher'], 
    ['New York Yankees', 'Contreras Jose', 5500000, 'Pitcher']]

data = pd.DataFrame(data[1:], columns=data[0])
Improve this answer
$\endgroup$

Not the answer you're looking for? Browse other questions tagged or ask your own question.