I have calculated a bunch of individual Pandas Series which have a common index. I would like to construct a Pandas multiindex frame from them. Below is my desired structure.
X Y Z
DATE
2018-01-01 A NaN NaN NaN
B NaN NaN NaN
C NaN NaN NaN
2018-01-02 A NaN NaN NaN
B NaN NaN NaN
C NaN NaN NaN
So (:, A, X) would be one Series. (:, A, Y) another and so on. How do I go about it?
Below is what I have
import pandas as pd
import numpy as np
idx = pd.date_range("20180101", periods=10)
s_1 = pd.Series(np.random.randint(0,10,size=10), index=idx)
s_2 = pd.Series(np.random.randint(0,10,size=10), index=idx)
s_3 = .... all the way to s9
EDIT: Say I want to map s_1 to (A,X), s_2 to (A,Y), s_3 to (A,Z), s_4 to (B,X), etc.
You need to first add some information to the Series
you provided, namely, the column and multiindex level they belong to:
def add_idx_and_name(s, idx_name, col_name):
#Create multi-index DataFrames from s
s = s.reset_index()
s['idx'] = idx_name
s = s.set_index(['index', 'idx'])
s.rename(columns={0: col_name}, inplace=True)
return s
Then add this information to your series (they are now DataFrame
s):
s_1 = add_idx_and_name(s_1, 'A', 'X')
s_2 = add_idx_and_name(s_2, 'A', 'Y')
s_3 = add_idx_and_name(s_3, 'A', 'Z')
s_4 = add_idx_and_name(s_4, 'B', 'X')
....
Then concatenate
pd.concat([pd.concat([s_1, s_2, s_3], axis=1),
pd.concat([s_4, s_5, s_6], axis=1),
pd.concat([s_7, s_8, s_9], axis=1)]).sort_index()
Outputs (I used np.random.seed(123)
)
X Y Z
index idx
2018-01-01 A 2 9 7
B 9 3 0
C 2 0 2
2018-01-02 A 2 0 3
B 3 5 6
C 4 8 3
2018-01-03 A 6 0 2
B 4 0 4
C 8 1 3
...