将列表数据转换为汇总表（转置和分组？）

Question

我有在不同州/省使用多种语言的数据，每个州使用一种语言的人数由政府提供

我想总结/转换如屏幕截图所示。其中每个语言/状态对都列出了编号为的说话人

在Python或其他工具中，什么是好方法

Sample

数据样本CSV

State,Language,Speakers
ANDHRA PRADESH, Adi,37,
ARUNACHAL PRADESH, Adi,105158,
ASSAM, Adi,2992,
BIHAR, Adi,7,
CHHATTISGARH, Adi,6,
DADRA & NAGAR HAVELI, Adi,1,
GOA, Adi,1,
GUJARAT, Adi,3,
HARYANA, Adi,44,
HIMACHAL PRADESH, Adi,13,
JAMMU & KASHMIR, Adi,10,
JHARKHAND, Adi,9,
KARNATAKA, Adi,61,
KERALA, Adi,24,
MADHYA PRADESH, Adi,283,
MAHARASHTRA, Adi,9,
MANIPUR, Adi,20,
MEGHALAYA, Adi,207,
MIZORAM, Adi,17,
NAGALAND, Adi,60,
NCT OF DELHI, Adi,20,
ODISHA, Adi,1,
PUDUCHERRY, Adi,5,
PUNJAB, Adi,21,
RAJASTHAN, Adi,758,
SIKKIM, Adi,1,
TAMIL NADU, Adi,9,
TRIPURA, Adi,3,
UTTAR PRADESH, Adi,25,
UTTARAKHAND, Adi,6,
WEST BENGAL, Adi,496,
ARUNACHAL PRADESH, Henchul,27720,
ASSAM, Henchul,548,
BIHAR, Henchul,6,
CHANDIGARH, Henchul,2,
GOA, Henchul,4,
GUJARAT, Henchul,16,
HIMACHAL PRADESH, Henchul,69,
JAMMU & KASHMIR, Henchul,3,
JHARKHAND, Henchul,714,
KARNATAKA, Henchul,7,
KERALA, Henchul,4,
MAHARASHTRA, Henchul,3,
MANIPUR, Henchul,3,
MEGHALAYA, Henchul,77,
MIZORAM, Henchul,2,
NAGALAND, Henchul,6,
NCT OF DELHI, Henchul,1,
ODISHA, Henchul,1,
PUNJAB, Henchul,2,
RAJASTHAN, Henchul,9,
UTTAR PRADESH, Henchul,27,
UTTARAKHAND, Henchul,1,
WEST BENGAL, Henchul,21,

Answer 1

假设State、Language和speaker都是数据帧中的列，那么您只需使用pivot

import pandas as pd

df = pd.DataFrame(
    {
        'State': ['State1', 'State2', 'State3', 'State1', 'State2', 'State3', 'State1', 'State2'],
        'Language': ['Lang1', 'Lang1', 'Lang1', 'Lang2', 'Lang2', 'Lang2', 'Lang3', 'Lang3'],
        'Speakers': [10, 100, 400, 1, 45, 100, 1000, 40]
    }
)

df.pivot(index='State', columns=['Language'], values='Speakers')


Language    Lang1   Lang2   Lang3
State           
State1      10.0    1.0     1000.0
State2      100.0   45.0    40.0
State3      400.0   100.0   NaN