<ul>
<li>给定以下数据帧,其中一列(<code>infectedByRegion</code>)是字典列表</li>
</ul>
<h2><code>infectedByRegion</code>的目录列表</h2>
<pre class="lang-py prettyprint-override"><code>data = [{'region': 'Canada', 'infectedCount': '6258', 'deceasedCount': '61'},
{'region': 'Newfoundland and Labrador', 'infectedCount': '135', 'deceasedCount': '0'},
{'region': 'Prince Edward Island', 'infectedCount': '11', 'deceasedCount': '0'},
{'region': 'Nova Scotia', 'infectedCount': '122', 'deceasedCount': '0'},
{'region': 'New Brunswick', 'infectedCount': '66', 'deceasedCount': '0'},
{'region': 'Quebec', 'infectedCount': '2840', 'deceasedCount': '22'},
{'region': 'Ontario', 'infectedCount': '1355', 'deceasedCount': '19'},
{'region': 'Manitoba', 'infectedCount': '72', 'deceasedCount': '1'},
{'region': 'Saskatchewan', 'infectedCount': '134', 'deceasedCount': '0'},
{'region': 'Alberta', 'infectedCount': '621', 'deceasedCount': '2'},
{'region': 'British Columbia', 'infectedCount': '884', 'deceasedCount': '17'},
{'region': 'Yukon', 'infectedCount': '4', 'deceasedCount': '0'},
{'region': 'Northwest Territories', 'infectedCount': '1', 'deceasedCount': '0'},
{'region': 'Nunavut', 'infectedCount': '0', 'deceasedCount': '0'},
{'region': 'Repatriated travellers', 'infectedCount': '13', 'deceasedCount': '0'}]
</code></pre>
<h2>代表性数据帧</h2>
<pre class="lang-py prettyprint-override"><code>import pandas as pd
from ast import literal_eval
df = pd.DataFrame({'measureDate': ['2020-03-29', '2020-03-30', '2020-03-31'], 'measureTime': ['22:30:15', '21:30:16', '20:56:29'],
'infectedByRegion': [data, data, data], 'infected': [12516, 13000, 14000], 'deceased': [122, 133, 143]})
measureDate measureTime infected deceased infectedByRegion
0 2020-03-29 22:30:15 12516 122 [{'region': 'Canada', 'infectedCount': '6258', 'deceasedCount': '61'}, {'region': 'Newfoundland and Labrador', 'infectedCount': '135', 'deceasedCount': '0'}, {'region': 'Prince Edward Island', 'infectedCount': '11', 'deceasedCount': '0'}, {'region': 'Nova Scotia', 'infectedCount': '122', 'deceasedCount': '0'}, {'region': 'New Brunswick', 'infectedCount': '66', 'deceasedCount': '0'}, {'region': 'Quebec', 'infectedCount': '2840', 'deceasedCount': '22'}, {'region': 'Ontario', 'infectedCount': '1355', 'deceasedCount': '19'}, {'region': 'Manitoba', 'infectedCount': '72', 'deceasedCount': '1'}, {'region': 'Saskatchewan', 'infectedCount': '134', 'deceasedCount': '0'}, {'region': 'Alberta', 'infectedCount': '621', 'deceasedCount': '2'}, {'region': 'British Columbia', 'infectedCount': '884', 'deceasedCount': '17'}, {'region': 'Yukon', 'infectedCount': '4', 'deceasedCount': '0'}, {'region': 'Northwest Territories', 'infectedCount': '1', 'deceasedCount': '0'}, {'region': 'Nunavut', 'infectedCount': '0', 'deceasedCount': '0'}, {'region': 'Repatriated travellers', 'infectedCount': '13', 'deceasedCount': '0'}]
1 2020-03-30 21:30:16 13000 133 [{'region': 'Canada', 'infectedCount': '6258', 'deceasedCount': '61'}, {'region': 'Newfoundland and Labrador', 'infectedCount': '135', 'deceasedCount': '0'}, {'region': 'Prince Edward Island', 'infectedCount': '11', 'deceasedCount': '0'}, {'region': 'Nova Scotia', 'infectedCount': '122', 'deceasedCount': '0'}, {'region': 'New Brunswick', 'infectedCount': '66', 'deceasedCount': '0'}, {'region': 'Quebec', 'infectedCount': '2840', 'deceasedCount': '22'}, {'region': 'Ontario', 'infectedCount': '1355', 'deceasedCount': '19'}, {'region': 'Manitoba', 'infectedCount': '72', 'deceasedCount': '1'}, {'region': 'Saskatchewan', 'infectedCount': '134', 'deceasedCount': '0'}, {'region': 'Alberta', 'infectedCount': '621', 'deceasedCount': '2'}, {'region': 'British Columbia', 'infectedCount': '884', 'deceasedCount': '17'}, {'region': 'Yukon', 'infectedCount': '4', 'deceasedCount': '0'}, {'region': 'Northwest Territories', 'infectedCount': '1', 'deceasedCount': '0'}, {'region': 'Nunavut', 'infectedCount': '0', 'deceasedCount': '0'}, {'region': 'Repatriated travellers', 'infectedCount': '13', 'deceasedCount': '0'}]
2 2020-03-31 20:56:29 14000 143 [{'region': 'Canada', 'infectedCount': '6258', 'deceasedCount': '61'}, {'region': 'Newfoundland and Labrador', 'infectedCount': '135', 'deceasedCount': '0'}, {'region': 'Prince Edward Island', 'infectedCount': '11', 'deceasedCount': '0'}, {'region': 'Nova Scotia', 'infectedCount': '122', 'deceasedCount': '0'}, {'region': 'New Brunswick', 'infectedCount': '66', 'deceasedCount': '0'}, {'region': 'Quebec', 'infectedCount': '2840', 'deceasedCount': '22'}, {'region': 'Ontario', 'infectedCount': '1355', 'deceasedCount': '19'}, {'region': 'Manitoba', 'infectedCount': '72', 'deceasedCount': '1'}, {'region': 'Saskatchewan', 'infectedCount': '134', 'deceasedCount': '0'}, {'region': 'Alberta', 'infectedCount': '621', 'deceasedCount': '2'}, {'region': 'British Columbia', 'infectedCount': '884', 'deceasedCount': '17'}, {'region': 'Yukon', 'infectedCount': '4', 'deceasedCount': '0'}, {'region': 'Northwest Territories', 'infectedCount': '1', 'deceasedCount': '0'}, {'region': 'Nunavut', 'infectedCount': '0', 'deceasedCount': '0'}, {'region': 'Repatriated travellers', 'infectedCount': '13', 'deceasedCount': '0'}]
</code></pre>
<h2><code>explode</code>将目录列表分成单独的行</h2>
<ul>
<li>不清楚<code>infectedByRegion</code>列在数据帧中是类型<code>list</code>还是<code>str</code>,因此可能需要修复</li>
</ul>
<pre class="lang-py prettyprint-override"><code># convert str to list; may not be required
df.infectedByRegion = df.infectedByRegion.apply(literal_eval)
# combine columns to datetime the drop them
df['DateTime'] = pd.to_datetime(df.measureDate + ' ' + df.measureTime)
df.drop(columns=['measureDate', 'measureTime'], inplace=True)
# explode infectedByRedion; pandas >= 0.25
df = df.explode('infectedByRegion')
| | infectedByRegion | infected | deceased | DateTime |
| -:|: | -:| -:|: |
| 0 | {'region': 'Canada', 'infectedCount': '6258', 'deceasedCount': '61'} | 12516 | 122 | 2020-03-29 22:30:15 |
| 0 | {'region': 'Newfoundland and Labrador', 'infectedCount': '135', 'deceasedCount': '0'} | 12516 | 122 | 2020-03-29 22:30:15 |
| 0 | {'region': 'Prince Edward Island', 'infectedCount': '11', 'deceasedCount': '0'} | 12516 | 122 | 2020-03-29 22:30:15 |
| 0 | {'region': 'Nova Scotia', 'infectedCount': '122', 'deceasedCount': '0'} | 12516 | 122 | 2020-03-29 22:30:15 |
| 0 | {'region': 'New Brunswick', 'infectedCount': '66', 'deceasedCount': '0'} | 12516 | 122 | 2020-03-29 22:30:15 |
| 0 | {'region': 'Quebec', 'infectedCount': '2840', 'deceasedCount': '22'} | 12516 | 122 | 2020-03-29 22:30:15 |
| 0 | {'region': 'Ontario', 'infectedCount': '1355', 'deceasedCount': '19'} | 12516 | 122 | 2020-03-29 22:30:15 |
| 0 | {'region': 'Manitoba', 'infectedCount': '72', 'deceasedCount': '1'} | 12516 | 122 | 2020-03-29 22:30:15 |
| 0 | {'region': 'Saskatchewan', 'infectedCount': '134', 'deceasedCount': '0'} | 12516 | 122 | 2020-03-29 22:30:15 |
| 0 | {'region': 'Alberta', 'infectedCount': '621', 'deceasedCount': '2'} | 12516 | 122 | 2020-03-29 22:30:15 |
| 0 | {'region': 'British Columbia', 'infectedCount': '884', 'deceasedCount': '17'} | 12516 | 122 | 2020-03-29 22:30:15 |
| 0 | {'region': 'Yukon', 'infectedCount': '4', 'deceasedCount': '0'} | 12516 | 122 | 2020-03-29 22:30:15 |
| 0 | {'region': 'Northwest Territories', 'infectedCount': '1', 'deceasedCount': '0'} | 12516 | 122 | 2020-03-29 22:30:15 |
| 0 | {'region': 'Nunavut', 'infectedCount': '0', 'deceasedCount': '0'} | 12516 | 122 | 2020-03-29 22:30:15 |
| 0 | {'region': 'Repatriated travellers', 'infectedCount': '13', 'deceasedCount': '0'} | 12516 | 122 | 2020-03-29 22:30:15 |
| 1 | {'region': 'Canada', 'infectedCount': '6258', 'deceasedCount': '61'} | 13000 | 133 | 2020-03-30 21:30:16 |
| 1 | {'region': 'Newfoundland and Labrador', 'infectedCount': '135', 'deceasedCount': '0'} | 13000 | 133 | 2020-03-30 21:30:16 |
| 1 | {'region': 'Prince Edward Island', 'infectedCount': '11', 'deceasedCount': '0'} | 13000 | 133 | 2020-03-30 21:30:16 |
| 1 | {'region': 'Nova Scotia', 'infectedCount': '122', 'deceasedCount': '0'} | 13000 | 133 | 2020-03-30 21:30:16 |
| 1 | {'region': 'New Brunswick', 'infectedCount': '66', 'deceasedCount': '0'} | 13000 | 133 | 2020-03-30 21:30:16 |
| 1 | {'region': 'Quebec', 'infectedCount': '2840', 'deceasedCount': '22'} | 13000 | 133 | 2020-03-30 21:30:16 |
| 1 | {'region': 'Ontario', 'infectedCount': '1355', 'deceasedCount': '19'} | 13000 | 133 | 2020-03-30 21:30:16 |
| 1 | {'region': 'Manitoba', 'infectedCount': '72', 'deceasedCount': '1'} | 13000 | 133 | 2020-03-30 21:30:16 |
| 1 | {'region': 'Saskatchewan', 'infectedCount': '134', 'deceasedCount': '0'} | 13000 | 133 | 2020-03-30 21:30:16 |
| 1 | {'region': 'Alberta', 'infectedCount': '621', 'deceasedCount': '2'} | 13000 | 133 | 2020-03-30 21:30:16 |
| 1 | {'region': 'British Columbia', 'infectedCount': '884', 'deceasedCount': '17'} | 13000 | 133 | 2020-03-30 21:30:16 |
| 1 | {'region': 'Yukon', 'infectedCount': '4', 'deceasedCount': '0'} | 13000 | 133 | 2020-03-30 21:30:16 |
| 1 | {'region': 'Northwest Territories', 'infectedCount': '1', 'deceasedCount': '0'} | 13000 | 133 | 2020-03-30 21:30:16 |
| 1 | {'region': 'Nunavut', 'infectedCount': '0', 'deceasedCount': '0'} | 13000 | 133 | 2020-03-30 21:30:16 |
| 1 | {'region': 'Repatriated travellers', 'infectedCount': '13', 'deceasedCount': '0'} | 13000 | 133 | 2020-03-30 21:30:16 |
| 2 | {'region': 'Canada', 'infectedCount': '6258', 'deceasedCount': '61'} | 14000 | 143 | 2020-03-31 20:56:29 |
| 2 | {'region': 'Newfoundland and Labrador', 'infectedCount': '135', 'deceasedCount': '0'} | 14000 | 143 | 2020-03-31 20:56:29 |
| 2 | {'region': 'Prince Edward Island', 'infectedCount': '11', 'deceasedCount': '0'} | 14000 | 143 | 2020-03-31 20:56:29 |
| 2 | {'region': 'Nova Scotia', 'infectedCount': '122', 'deceasedCount': '0'} | 14000 | 143 | 2020-03-31 20:56:29 |
| 2 | {'region': 'New Brunswick', 'infectedCount': '66', 'deceasedCount': '0'} | 14000 | 143 | 2020-03-31 20:56:29 |
| 2 | {'region': 'Quebec', 'infectedCount': '2840', 'deceasedCount': '22'} | 14000 | 143 | 2020-03-31 20:56:29 |
| 2 | {'region': 'Ontario', 'infectedCount': '1355', 'deceasedCount': '19'} | 14000 | 143 | 2020-03-31 20:56:29 |
| 2 | {'region': 'Manitoba', 'infectedCount': '72', 'deceasedCount': '1'} | 14000 | 143 | 2020-03-31 20:56:29 |
| 2 | {'region': 'Saskatchewan', 'infectedCount': '134', 'deceasedCount': '0'} | 14000 | 143 | 2020-03-31 20:56:29 |
| 2 | {'region': 'Alberta', 'infectedCount': '621', 'deceasedCount': '2'} | 14000 | 143 | 2020-03-31 20:56:29 |
| 2 | {'region': 'British Columbia', 'infectedCount': '884', 'deceasedCount': '17'} | 14000 | 143 | 2020-03-31 20:56:29 |
| 2 | {'region': 'Yukon', 'infectedCount': '4', 'deceasedCount': '0'} | 14000 | 143 | 2020-03-31 20:56:29 |
| 2 | {'region': 'Northwest Territories', 'infectedCount': '1', 'deceasedCount': '0'} | 14000 | 143 | 2020-03-31 20:56:29 |
| 2 | {'region': 'Nunavut', 'infectedCount': '0', 'deceasedCount': '0'} | 14000 | 143 | 2020-03-31 20:56:29 |
| 2 | {'region': 'Repatriated travellers', 'infectedCount': '13', 'deceasedCount': '0'} | 14000 | 143 | 2020-03-31 20:56:29 |
</code></pre>
<h2>将字典键转换为列</h2>
<pre class="lang-py prettyprint-override"><code>df_concat = pd.concat([df, df.infectedByRegion.apply(pd.Series)], axis=1).drop('infectedByRegion', axis=1)
| | infected | deceased | DateTime | region | infectedCount | deceasedCount |
| -:| -:| -:|: |: | :| :|
| 0 | 12516 | 122 | 2020-03-29 22:30:15 | Canada | 6258 | 61 |
| 0 | 12516 | 122 | 2020-03-29 22:30:15 | Newfoundland and Labrador | 135 | 0 |
| 0 | 12516 | 122 | 2020-03-29 22:30:15 | Prince Edward Island | 11 | 0 |
| 0 | 12516 | 122 | 2020-03-29 22:30:15 | Nova Scotia | 122 | 0 |
| 0 | 12516 | 122 | 2020-03-29 22:30:15 | New Brunswick | 66 | 0 |
| 0 | 12516 | 122 | 2020-03-29 22:30:15 | Quebec | 2840 | 22 |
| 0 | 12516 | 122 | 2020-03-29 22:30:15 | Ontario | 1355 | 19 |
| 0 | 12516 | 122 | 2020-03-29 22:30:15 | Manitoba | 72 | 1 |
| 0 | 12516 | 122 | 2020-03-29 22:30:15 | Saskatchewan | 134 | 0 |
| 0 | 12516 | 122 | 2020-03-29 22:30:15 | Alberta | 621 | 2 |
| 0 | 12516 | 122 | 2020-03-29 22:30:15 | British Columbia | 884 | 17 |
| 0 | 12516 | 122 | 2020-03-29 22:30:15 | Yukon | 4 | 0 |
| 0 | 12516 | 122 | 2020-03-29 22:30:15 | Northwest Territories | 1 | 0 |
| 0 | 12516 | 122 | 2020-03-29 22:30:15 | Nunavut | 0 | 0 |
| 0 | 12516 | 122 | 2020-03-29 22:30:15 | Repatriated travellers | 13 | 0 |
| 1 | 13000 | 133 | 2020-03-30 21:30:16 | Canada | 6258 | 61 |
| 1 | 13000 | 133 | 2020-03-30 21:30:16 | Newfoundland and Labrador | 135 | 0 |
| 1 | 13000 | 133 | 2020-03-30 21:30:16 | Prince Edward Island | 11 | 0 |
| 1 | 13000 | 133 | 2020-03-30 21:30:16 | Nova Scotia | 122 | 0 |
| 1 | 13000 | 133 | 2020-03-30 21:30:16 | New Brunswick | 66 | 0 |
| 1 | 13000 | 133 | 2020-03-30 21:30:16 | Quebec | 2840 | 22 |
| 1 | 13000 | 133 | 2020-03-30 21:30:16 | Ontario | 1355 | 19 |
| 1 | 13000 | 133 | 2020-03-30 21:30:16 | Manitoba | 72 | 1 |
| 1 | 13000 | 133 | 2020-03-30 21:30:16 | Saskatchewan | 134 | 0 |
| 1 | 13000 | 133 | 2020-03-30 21:30:16 | Alberta | 621 | 2 |
| 1 | 13000 | 133 | 2020-03-30 21:30:16 | British Columbia | 884 | 17 |
| 1 | 13000 | 133 | 2020-03-30 21:30:16 | Yukon | 4 | 0 |
| 1 | 13000 | 133 | 2020-03-30 21:30:16 | Northwest Territories | 1 | 0 |
| 1 | 13000 | 133 | 2020-03-30 21:30:16 | Nunavut | 0 | 0 |
| 1 | 13000 | 133 | 2020-03-30 21:30:16 | Repatriated travellers | 13 | 0 |
| 2 | 14000 | 143 | 2020-03-31 20:56:29 | Canada | 6258 | 61 |
| 2 | 14000 | 143 | 2020-03-31 20:56:29 | Newfoundland and Labrador | 135 | 0 |
| 2 | 14000 | 143 | 2020-03-31 20:56:29 | Prince Edward Island | 11 | 0 |
| 2 | 14000 | 143 | 2020-03-31 20:56:29 | Nova Scotia | 122 | 0 |
| 2 | 14000 | 143 | 2020-03-31 20:56:29 | New Brunswick | 66 | 0 |
| 2 | 14000 | 143 | 2020-03-31 20:56:29 | Quebec | 2840 | 22 |
| 2 | 14000 | 143 | 2020-03-31 20:56:29 | Ontario | 1355 | 19 |
| 2 | 14000 | 143 | 2020-03-31 20:56:29 | Manitoba | 72 | 1 |
| 2 | 14000 | 143 | 2020-03-31 20:56:29 | Saskatchewan | 134 | 0 |
| 2 | 14000 | 143 | 2020-03-31 20:56:29 | Alberta | 621 | 2 |
| 2 | 14000 | 143 | 2020-03-31 20:56:29 | British Columbia | 884 | 17 |
| 2 | 14000 | 143 | 2020-03-31 20:56:29 | Yukon | 4 | 0 |
| 2 | 14000 | 143 | 2020-03-31 20:56:29 | Northwest Territories | 1 | 0 |
| 2 | 14000 | 143 | 2020-03-31 20:56:29 | Nunavut | 0 | 0 |
| 2 | 14000 | 143 | 2020-03-31 20:56:29 | Repatriated travellers | 13 | 0 |
</code></pre>
<h2>转向所需的格式</h2>
<pre class="lang-py prettyprint-override"><code>df_pivot = df_concat.pivot(index='DateTime', columns='region', values=['infectedCount', 'deceasedCount'])
# rename multi-index column names
df_pivot.columns = [f'{col[1]}_{col[0]}' for col in df_pivot.columns.values]
# output form
Alberta_infectedCount British Columbia_infectedCount Canada_infectedCount Manitoba_infectedCount New Brunswick_infectedCount Newfoundland and Labrador_infectedCount Northwest Territories_infectedCount Nova Scotia_infectedCount Nunavut_infectedCount Ontario_infectedCount Prince Edward Island_infectedCount Quebec_infectedCount Repatriated travellers_infectedCount Saskatchewan_infectedCount Yukon_infectedCount Alberta_deceasedCount British Columbia_deceasedCount Canada_deceasedCount Manitoba_deceasedCount New Brunswick_deceasedCount Newfoundland and Labrador_deceasedCount Northwest Territories_deceasedCount Nova Scotia_deceasedCount Nunavut_deceasedCount Ontario_deceasedCount Prince Edward Island_deceasedCount Quebec_deceasedCount Repatriated travellers_deceasedCount Saskatchewan_deceasedCount Yukon_deceasedCount
DateTime
2020-03-29 22:30:15 621 884 6258 72 66 135 1 122 0 1355 11 2840 13 134 4 2 17 61 1 0 0 0 0 0 19 0 22 0 0 0
2020-03-30 21:30:16 621 884 6258 72 66 135 1 122 0 1355 11 2840 13 134 4 2 17 61 1 0 0 0 0 0 19 0 22 0 0 0
2020-03-31 20:56:29 621 884 6258 72 66 135 1 122 0 1355 11 2840 13 134 4 2 17 61 1 0 0 0 0 0 19 0 22 0 0 0
</code></pre>