使用列表列表在pandas中创建数据帧

Question

我有一张这样的单子

 sports =  [['Sport', 'Country(s)'], ['Foot_ball', 'brazil'], ['Volleyball', 'Argentina', 'India'], ['Rugger', 'New_zealand', ‘South_africa’], ['Cricket', 'India'], ['Carrom', 'Uk', ‘Usa’], ['Chess', 'Uk']]

我想使用上面的列表创建panda数据框，如下所示：

sport      Country(s)
Foot_ball      brazil
Volleyball     Argentina   
Volleyball     india
Rugger         New_zealnd  
Rugger         South_africa
Criket         India
Carrom         UK
Carrom         Usa
Chess          UK

我试着这样做

sport_x = []
for x in sports[1:]:
    sport_x.append(x[0])
print(sport_x)

country = []
for y in sports[1:]:
    country.append(y[1:])

header = sports[0]

df = pd.DataFrame([sport_x,country], columns = header)

中途，我得到了这个错误 但我犯了个错误

AssertionError: 2 columns passed, passed data had 6 columns

有什么建议，怎么做

Answer 1

类似这样的方法首先“扩展”不规则形状的行，然后对它们进行数据框架化

>>> sports = [
        ["Sport", "Country(s)"],
        ["Foot_ball", "brazil"],
        ["Volleyball", "Argentina", "India"],
        ["Rugger", "New_zealand", "South_africa"],
        ["Cricket", "India"],
        ["Carrom", "Uk", "Usa"],
        ["Chess", "Uk"],
    ]
>>> expanded_sports = []
>>> for row in sports:
...   for country in row[1:]:
...     expanded_sports.append((row[0], country))
...
>>> pd.DataFrame(expanded_sports[1:], columns=expanded_sports[0])
        Sport    Country(s)
0   Foot_ball        brazil
1  Volleyball     Argentina
2  Volleyball         India
3      Rugger   New_zealand
4      Rugger  South_africa
5     Cricket         India
6      Carrom            Uk
7      Carrom           Usa
8       Chess            Uk
>>>

编辑：另一个使用.melt()的解决方案，但这在我看来更难看，顺序也不一样

>>> pd.DataFrame(sports[1:]).melt(0, value_name='country').dropna().drop('variable', axis=1).rename({0: 'sport'}, axis=1)
         sport       country
0    Foot_ball        brazil
1   Volleyball     Argentina
2       Rugger   New_zealand
3      Cricket         India
4       Carrom            Uk
5        Chess            Uk
7   Volleyball         India
8       Rugger  South_africa
10      Carrom           Usa

Answer 2

或者，使用^{}和列表理解：

df=pd.DataFrame([[i[0],','.join(i[1:])] if len(i)>2 else i for i in sports[1:]],
         columns=sports[0])
df['Country(s)']=df['Country(s)'].str.split(',')
final=df.explode('Country(s)').reset_index(drop=True)

---

        Sport    Country(s)
0   Foot_ball        brazil
1  Volleyball     Argentina
2  Volleyball         India
3      Rugger   New_zealand
4      Rugger  South_africa
5     Cricket         India
6      Carrom            Uk
7      Carrom           Usa
8       Chess            Uk