Pandas: Creating DataFrame from Series

Question

My current code is shown below - I'm importing a MAT file and trying to create a DataFrame from variables within it:

mat = loadmat(file_path)  # load mat-file
Variables = mat.keys()    # identify variable names

df = pd.DataFrame         # Initialise DataFrame

for name in Variables:

    B = mat[name]
    s = pd.Series (B[:,1])

So within the loop, I can create a series of each variable (they're arrays with two columns - so the values I need are in column 2)

My question is how do I append the series to the dataframe? I've looked through the documentation and none of the examples seem to fit what I'm trying to do.

Jaan · Accepted Answer · 2015-06-28 09:38:02Z

92

Here is how to create a DataFrame where each series is a row.

For a single Series (resulting in a single-row DataFrame):

series = pd.Series([1,2], index=['a','b'])
df = pd.DataFrame([series])

For multiple series with identical indices:

cols = ['a','b']
list_of_series = [pd.Series([1,2],index=cols), pd.Series([3,4],index=cols)]
df = pd.DataFrame(list_of_series, columns=cols)

For multiple series with possibly different indices:

list_of_series = [pd.Series([1,2],index=['a','b']), pd.Series([3,4],index=['a','c'])]
df = pd.concat(list_of_series, axis=1).transpose()

To create a DataFrame where each series is a column, see the answers by others. Alternatively, one can create a DataFrame where each series is a row, as above, and then use df.transpose(). However, the latter approach is inefficient if the columns have different data types.

edited Jun 28, 2015 at 9:38

answered Jun 28, 2015 at 8:57

Jaan

2,35021 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Jean-François Corbett · Accepted Answer · 2018-06-13 17:00:18Z

45

No need to initialize an empty DataFrame (you weren't even doing that, you'd need pd.DataFrame() with the parens).

Instead, to create a DataFrame where each series is a column,

make a list of Series, series, and
concatenate them horizontally with df = pd.concat(series, axis=1)

Something like:

series = [pd.Series(mat[name][:, 1]) for name in Variables]
df = pd.concat(series, axis=1)

edited Jun 13, 2018 at 17:00

Jean-François Corbett

38.7k30 gold badges145 silver badges192 bronze badges

answered May 7, 2014 at 15:28

TomAugspurger

29k8 gold badges89 silver badges71 bronze badges

2 Comments

BMichell Over a year ago

Tom, that works great - only issue is the columns in the resulting dataframe are named numerically. How would I go about using "name" as the column name in the resulting dataframe?

BMichell Over a year ago

Sorry, answered my own question... df.columns = Variables

Wtower · Accepted Answer · 2022-02-08 14:37:05Z

15

Nowadays there is a pandas.Series.to_frame method:

Series.to_frame(name=NoDefault.no_default)

Convert Series to DataFrame.

Parameters

nameobject, optional: The passed name should substitute for the series name (if it has one).

Returns

DataFrame: DataFrame representation of Series.

Examples
s = pd.Series(["a", "b", "c"], name="vals")
s.to_frame()

answered Feb 8, 2022 at 14:37

Wtower

20.1k12 gold badges110 silver badges86 bronze badges

Comments

Happy001 · Accepted Answer · 2014-05-08 01:34:12Z

2

I guess anther way, possibly faster, to achieve this is 1) Use dict comprehension to get desired dict (i.e., taking 2nd col of each array) 2) Then use pd.DataFrame to create an instance directly from the dict without loop over each col and concat.

Assuming your mat looks like this (you can ignore this since your mat is loaded from file):

In [135]: mat = {'a': np.random.randint(5, size=(4,2)),
   .....: 'b': np.random.randint(5, size=(4,2))}

In [136]: mat
Out[136]: 
{'a': array([[2, 0],
        [3, 4],
        [0, 1],
        [4, 2]]), 'b': array([[1, 0],
        [1, 1],
        [1, 0],
        [2, 1]])}

Then you can do:

In [137]: df = pd.DataFrame ({name:mat[name][:,1] for name in mat})

In [138]: df
Out[138]: 
   a  b
0  0  0
1  4  1
2  1  0
3  2  1

[4 rows x 2 columns]

answered May 8, 2014 at 1:34

Happy001

6,4032 gold badges26 silver badges17 bronze badges

3 Comments

BMichell Over a year ago

That's a nice solution thanks! Is it possible to add an if statement within the dict (or list) comprehension to ignore arrays that are a different size? The dict I end up with from my MAT fiel has a few hundred 2x4000 arrays and a handful of random arrays of different sizes.

Happy001 Over a year ago

You can add if statement after for ... {name:mat[name][:,1] for name in mat if ... }

Jaan Over a year ago

Thu use of dict comprehension has the drawback that the keys in standard python dicts are unordered, so the order of columns is not preserved.

Collectives™ on Stack Overflow

Pandas: Creating DataFrame from Series

4 Answers 4

Comments

2 Comments

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

2 Comments

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related