我有这种形式的数据在XLS格式:
+--------+---------+-------------+---------------+---------+
| ID | Branch | Customer ID | Customer Name | Balance |
+--------+---------+-------------+---------------+---------+
| 111111 | Branch1 | 1 | Company A | 10 |
+--------+---------+-------------+---------------+---------+
| 222222 | Branch2 | 2 | Company B | 20 |
+--------+---------+-------------+---------------+---------+
| 111111 | Branch1 | 2 | Company B | 30 |
+--------+---------+-------------+---------------+---------+
| 222222 | Branch2 | 3 | Company C | 10 |
+--------+---------+-------------+---------------+---------+
我想用Pandas来处理它。Pandas会将其读取为单个表单,但我想在这里使用MultiIndex,如
+--------+---------+-------------+---------------+---------+
| ID | Branch | Customer ID | Customer Name | Balance |
+--------+---------+-------------+---------------+---------+
| | | 1 | Company A | 10 |
+ 111111 + Branch1 +-------------+---------------+---------+
| | | 2 | Company B | 30 |
+--------+---------+-------------+---------------+---------+
| | | 2 | Company B | 20 |
+ 222222 + Branch2 +-------------+---------------+---------+
| | | 3 | Company C | 10 |
+--------+---------+-------------+---------------+---------+
其中111111
、Branch1
为一级指标,1
、Company A
为二级指标。是否有一个内置的方法来做到这一点?
如果只需要set_index
和sort_index
,则使用:
df.set_index(['ID','Branch', 'Customer ID','Customer Name'], inplace=True)
df.sort_index(inplace=True)
print (df)
Balance
ID Branch Customer ID Customer Name
111111 Branch1 1 Company A 10
2 Company B 30
222222 Branch2 2 Company B 20
3 Company C 10
但是如果MultiIndex
中只需要两个级别(a
, b
在我的解决方案中),则必须首先与第二列连接,第三列与第四列连接:
df['a'] = df.ID.astype(str) + '_' + df.Branch
df['b'] = df['Customer ID'].astype(str) + '_' + df['Customer Name']
#delete original columns
df.drop(['ID','Branch', 'Customer ID','Customer Name'], axis=1, inplace=True)
df.set_index(['a','b'], inplace=True)
df.sort_index(inplace=True)
print (df)
Balance
a b
111111_Branch1 1_Company A 10
2_Company B 30
222222_Branch2 2_Company B 20
3_Company C 10
如果需要按前列聚合最后一列,则使用groupby
和GroupBy.mean
:
df = df.groupby(['ID','Branch', 'Customer ID','Customer Name'])['Balance'].mean().to_frame()
print (df)
Balance
ID Branch Customer ID Customer Name
111111 Branch1 1 Company A 10
2 Company B 30
222222 Branch2 2 Company B 20
3 Company C 10
如果在列中使用MultiIndex
,则set_index
需要tuples
:
df.columns = pd.MultiIndex.from_arrays([['a'] * 2 + ['b']* 2 + ['c'], df.columns])
print (df)
a b c
ID Branch Customer ID Customer Name Balance
0 111111 Branch1 1 Company A 10
1 222222 Branch2 2 Company B 20
2 111111 Branch1 2 Company B 30
3 222222 Branch2 3 Company C 10
df.set_index([('a','ID'), ('a','Branch'),
('b','Customer ID'), ('b','Customer Name')], inplace=True)
df.sort_index(inplace=True)
print (df)
c
Balance
(a, ID) (a, Branch) (b, Customer ID) (b, Customer Name)
111111 Branch1 1 Company A 10
2 Company B 30
222222 Branch2 2 Company B 20
3 Company C 10