列逐对聚合和重组在Pandas中

huangapple
go评论99阅读模式
英文:

Column Pair-wise aggregation and reorganization in Pandas

问题

我正在将一个csv文件导入到一个pandas dataframe中，如下所示：

  1. df = pd.DataFrame( {0: {0: 'ID', 1: '1', 2: '2', 3: '3', 4: '4', 5: '5'}, 1: {0: '净成本', 1: '30', 2: '40', 3: '50', 4: '35', 5: '45'}, 2: {0: '费用描述', 1: '附加费 A', 2: '折扣 X', 3: '折扣 X', 4: '折扣 X', 5: '附加费 A'}, 3: {0: '费用金额', 1: '9.5', 2: '-12.5', 3: '-11.5', 4: '-5.5', 5: '9.5'}, 4: {0: '费用描述', 1: '折扣 X', 2: '', 3: '', 4: '附加费 B', 5: '折扣 X'}, 5: {0: '费用金额', 1: '-11.5', 2: '', 3: '', 4: '3.5', 5: '-10.5'}, 6: {0: '费用描述', 1: '折扣 Y', 2: '', 3: '', 4: '', 5: '附加费 B'}, 7: {0: '费用金额', 1: '-3.25', 2: '', 3: '', 4: '', 5: '4.5'}, 8: {0: '费用描述', 1: '附加费 B', 2: '', 3: '', 4: '', 5: ''}, 9: {0: '费用金额', 1: '2.5', 2: '', 3: '', 4: '', 5: ''}} )

第一行是包含列名的标题，其中“费用描述”和“费用金额”成对出现多次。

期望的输出是一个df，每个描述都有一个唯一的列，重新组织的列按字母顺序排序，并且NaN显示为0：

  1. |   ID |   净成本 |   附加费 A |   附加费 B |   折扣 X |   折扣 Y |
  2. |-----:|-------:|----------:|----------:|-------:|-------:|
  3. |    1 |     30 |       9.5 |       2.5 |  -11.5 |  -3.25 |
  4. |    2 |     40 |         0 |         0 |  -12.5 |      0 |
  5. |    3 |     50 |         0 |         0 |  -11.5 |      0 |
  6. |    4 |     35 |         0 |       3.5 |   -5.5 |      0 |
  7. |    5 |     45 |       9.5 |       4.5 |  -10.5 |      0 |
英文:

I am importing a csv file into a pandas dataframe such as:

  1. df = pd.DataFrame( {0: {0: 'ID', 1: '1', 2: '2', 3: '3', 4: '4', 5: '5'}, 1: {0: 'Net Cost', 1: '30', 2: '40', 3: '50', 4: '35', 5: '45'}, 2: {0: 'Charge Description', 1: 'Surcharge A', 2: 'Discount X', 3: 'Discount X', 4: 'Discount X', 5: 'Surcharge A'}, 3: {0: 'Charge Amount', 1: '9.5', 2: '-12.5', 3: '-11.5', 4: '-5.5', 5: '9.5'}, 4: {0: 'Charge Description', 1: 'Discount X', 2: '', 3: '', 4: 'Surcharge B', 5: 'Discount X'}, 5: {0: 'Charge Amount', 1: '-11.5', 2: '', 3: '', 4: '3.5', 5: '-10.5'}, 6: {0: 'Charge Description', 1: 'Discount Y', 2: '', 3: '', 4: '', 5: 'Surcharge B'}, 7: {0: 'Charge Amount', 1: '-3.25', 2: '', 3: '', 4: '', 5: '4.5'}, 8: {0: 'Charge Description', 1: 'Surcharge B', 2: '', 3: '', 4: '', 5: ''}, 9: {0: 'Charge Amount', 1: '2.5', 2: '', 3: '', 4: '', 5: ''}} )
0 1 2 3 4 5 6 7 8 9
ID Net Cost Charge Description Charge Amount Charge Description Charge Amount Charge Description Charge Amount Charge Description Charge Amount
1 30 Surcharge A 9.5 Discount X -11.5 Discount Y -3.25 Surcharge B 2.5
2 40 Discount X -12.5
3 50 Discount X -11.5
4 35 Discount X -5.5 Surcharge B 3.5
5 45 Surcharge A 9.5 Discount X -10.5 Surcharge B 4.5

The first row are the headers with column names Charge Description and Charge Amount forming pairs and appearing multiple times.

Desired output is a df with a unique column for each description, with the reorganized columns sorted alphabetically and NaNs showing as 0:

ID Net Cost Surcharge A Surcharge B Discount X Discount Y
1 30 9.5 2.5 -11.5 -3.25
2 40 0 0 -12.5 0
3 50 0 0 -11.5 0
4 35 0 3.5 -5.5 0
5 45 9.5 4.5 -10.5 0

This post looks like a good starting point but then I need a column for each Charge Description and only a single row per ID.

答案1

得分: 2

I used the file you shared, and edited the columns with the initial dataframe df shared (Pandas automatically adds suffixes to columns to make them unique) to keep the non uniqueness:

  1. invoice = pd.read_csv('Downloads/Example Invoice.csv')
  2. invoice.columns = ['ID', 'Net Cost', 'Charge Description', 'Charge Amount',
  3.                    'Charge Description', 'Charge Amount', 
  4.                    'Charge Description', 'Charge Amount', 
  5.                    'Charge Description', 'Charge Amount']
  6. print(invoice)
  7.    ID  Net Cost Charge Description  Charge Amount  ... Charge Description  Charge Amount Charge Description  Charge Amount
  8. 0   1        30        Surcharge A            9.5  ...         Discount Y          -3.25        Surcharge B            2.5
  9. 1   2        40         Discount X          -12.5  ...                NaN            NaN                NaN            NaN
  10. 2   3        50         Discount X          -11.5  ...                NaN            NaN                NaN            NaN
  11. 3   4        35         Discount X           -5.5  ...                NaN            NaN                NaN            NaN
  12. 4   5        45        Surcharge A            9.5  ...        Surcharge B           4.50                NaN            NaN

First step is to transform to long form with pivot_longer from pyjanitor - in this case we take advantage of the fact that charge description is followed by charge amount - we can safely pair them and reshape into two columns. After that is done, we flip back to wide form - getting Surcharge and Discount values as headers. Thankfully, the index is unique, so a pivot works without extras. I used pivot_wider here, primarily for convenience - the same can be achieved with pivot, with just a few cleanup steps - under the hood pivot_wider uses pd.pivot.

  1. # pip install pyjanitor
  2. import pandas as pd
  3. import janitor
  5. index = ['ID', 'Net Cost']
  6. arr = ['Charge Description', 'Charge Amount']
  7. (invoice
  8. .pivot_longer(
  9.     index = index, 
  10.     names_to = arr, 
  11.     names_pattern = arr, 
  12.     dropna=True)
  13. .pivot_wider(
  14.     index=index,
  15.     names_from='Charge Description', 
  16.     values_from='Charge Amount')
  17. .fillna(0)
  18. )
  20.    ID  Net Cost  Discount X  Discount Y  Surcharge A  Surcharge B
  21. 0   1        30       -11.5       -3.25          9.5          2.5
  22. 1   2        40       -12.5        0.00          0.0          0.0
  23. 2   3        50       -11.5        0.00          0.0          0.0
  24. 3   4        35        -5.5        0.00          0.0          3.5
  25. 4   5        45       -10.5        0.00          9.5          4.5

Another option - since the data is fairly consistent with the ordering, you can dump down into numpy, reshape into a two column array, keep track of the ID and Net Cost columns (ensure they are correctly paired), and then pivot to get your final data:

  1. index = ['ID', 'Net Cost']
  2. arr = ['Charge Description', 'Charge Amount']
  3. invoice = invoice.set_index(index)
  4. out = invoice.to_numpy().reshape(-1, 2)
  5. out = pd.DataFrame(out, columns = arr)
  6. # reshape above is in order `C` - default
  7. # so we can safely repeat the index
  8. # with a value of 4
  9. # which is what you get -> 
  10. # invoice.columns.size // 2
  11. # to correctly pair the index with the new dataframe
  12. out.index = invoice.index.repeat(invoice.columns.size//2)
  13. # get rid of nulls, and flip to wide form
  14. (out
  15. .dropna(how='all')
  16. .set_index('Charge Description', append=True)
  17. .squeeze()
  18. .unstack('Charge Description', fill_value=0)
  19. .rename_axis(columns = None)
  20. .reset_index()
  21. )
  23.    ID  Net Cost Discount X Discount Y Surcharge A Surcharge B
  24. 0   1        30      -11.5      -3.25         9.5         2.5
  25. 1   2        40      -12.5          0           0           0
  26. 2   3        50      -11.5          0           0           0
  27. 3   4        35       -5.5          0           0         3.5
  28. 4   5        45      -10.5          0         9.5         4.5

You can convert the data dtypes for Discount to numeric

英文:

I used the file you shared, and edited the columns with the initial dataframe df shared (Pandas automatically adds suffixes to columns to make them unique) to keep the non uniqueness:

  1. invoice = pd.read_csv('Downloads/Example Invoice.csv')
  2. invoice.columns = ['ID', 'Net Cost', 'Charge Description', 'Charge Amount',
  3.                    'Charge Description', 'Charge Amount', 
  4.                    'Charge Description', 'Charge Amount', 
  5.                    'Charge Description', 'Charge Amount']
  6. print(invoice)
  7.    ID  Net Cost Charge Description  Charge Amount  ... Charge Description  Charge Amount Charge Description  Charge Amount
  8. 0   1        30        Surcharge A            9.5  ...         Discount Y          -3.25        Surcharge B            2.5
  9. 1   2        40         Discount X          -12.5  ...                NaN            NaN                NaN            NaN
  10. 2   3        50         Discount X          -11.5  ...                NaN            NaN                NaN            NaN
  11. 3   4        35         Discount X           -5.5  ...                NaN            NaN                NaN            NaN
  12. 4   5        45        Surcharge A            9.5  ...        Surcharge B           4.50                NaN            NaN

First step is to transform to long form with pivot_longer from pyjanitor - in this case we take advantage of the fact that charge description is followed by charge amount - we can safely pair them and reshape into two columns. After that is done, we flip back to wide form - getting Surcharge and Discount values as headers. Thankfully, the index is unique, so a pivot works without extras. I used pivot_wider here, primarily for convenience - the same can be achieved with pivot, with just a few cleanup steps - under the hood pivot_wider uses pd.pivot.

  1. # pip install pyjanitor
  2. import pandas as pd
  3. import janitor
  5. index = ['ID', 'Net Cost']
  6. arr = ['Charge Description', 'Charge Amount']
  7. (invoice
  8. .pivot_longer(
  9.     index = index, 
  10.     names_to = arr, 
  11.     names_pattern = arr, 
  12.     dropna=True)
  13. .pivot_wider(
  14.     index=index,
  15.     names_from='Charge Description', 
  16.     values_from='Charge Amount')
  17. .fillna(0)
  18. )
  20.    ID  Net Cost  Discount X  Discount Y  Surcharge A  Surcharge B
  21. 0   1        30       -11.5       -3.25          9.5          2.5
  22. 1   2        40       -12.5        0.00          0.0          0.0
  23. 2   3        50       -11.5        0.00          0.0          0.0
  24. 3   4        35        -5.5        0.00          0.0          3.5
  25. 4   5        45       -10.5        0.00          9.5          4.5

Another option - since the data is fairly consistent with the ordering, you can dump down into numpy, reshape into a two column array, keep track of the ID and Net Cost columns (ensure they are correctly paired), and then pivot to get your final data:

  1. index = ['ID', 'Net Cost']
  2. arr = ['Charge Description', 'Charge Amount']
  3. invoice = invoice.set_index(index)
  4. out = invoice.to_numpy().reshape(-1, 2)
  5. out = pd.DataFrame(out, columns = arr)
  6. # reshape above is in order `C` - default
  7. # so we can safely repeat the index
  8. # with a value of 4
  9. # which is what you get -> 
  10. # invoice.columns.size // 2
  11. # to correctly pair the index with the new dataframe
  12. out.index = invoice.index.repeat(invoice.columns.size//2)
  13. # get rid of nulls, and flip to wide form
  14. (out
  15. .dropna(how='all')
  16. .set_index('Charge Description', append=True)
  17. .squeeze()
  18. .unstack('Charge Description', fill_value=0)
  19. .rename_axis(columns = None)
  20. .reset_index()
  21. )
  23.    ID  Net Cost Discount X Discount Y Surcharge A Surcharge B
  24. 0   1        30      -11.5      -3.25         9.5         2.5
  25. 1   2        40      -12.5          0           0           0
  26. 2   3        50      -11.5          0           0           0
  27. 3   4        35       -5.5          0           0         3.5
  28. 4   5        45      -10.5          0         9.5         4.5

You can convert the data dtypes for Discount to numeric

答案2

得分: 0

你可以首先使用 melt 函数来展开你的数据框，然后在清理后使用 pivot_table 函数来重新塑造它：

  1. # 第一步
  2. out = (pd.DataFrame(df.iloc[1:].values, columns=df.iloc[0].tolist())
  3.          .melt(['ID', 'Net Cost'], ignore_index=False))
  5. m = out['variable'] == 'Charge Description'
  7. # 第二步
  8. out = (pd.concat([out[m].reset_index(drop=True).add_prefix('_'),
  9.                   out[~m].reset_index(drop=True)], axis=1)
  10.          .query("_value != ''")
  11.          .pivot_table(index=['ID', 'Net Cost'], columns='_value',
  12.                       values='value', aggfunc='first')
  13.          .rename_axis(columns=None).reset_index().fillna(0))

输出：

  1. >>> out
  2.   ID Net Cost Discount X Discount Y Surcharge A Surcharge B
  3. 0  1       30      -11.5      -3.25         9.5         2.5
  4. 1  2       40      -12.5          0           0           0
  5. 2  3       50      -11.5          0           0           0
  6. 3  4       35       -5.5          0           0         3.5
  7. 4  5       45      -10.5          0         9.5         4.5
英文:

You can flatten your dataframe first with melt then reshape with pivot_table after cleaning it up:

  1. # 1st pass
  2. out = (pd.DataFrame(df.iloc[1:].values, columns=df.iloc[0].tolist())
  3.          .melt(['ID', 'Net Cost'], ignore_index=False))
  5. m = out['variable'] == 'Charge Description'
  7. # 2nd pass
  8. out = (pd.concat([out[m].reset_index(drop=True).add_prefix('_'),
  9.                   out[~m].reset_index(drop=True)], axis=1)
  10.          .query("_value != ''")
  11.          .pivot_table(index=['ID', 'Net Cost'], columns='_value',
  12.                       values='value', aggfunc='first')
  13.          .rename_axis(columns=None).reset_index().fillna(0))

Output:

  1. >>> out
  2.   ID Net Cost Discount X Discount Y Surcharge A Surcharge B
  3. 0  1       30      -11.5      -3.25         9.5         2.5
  4. 1  2       40      -12.5          0           0           0
  5. 2  3       50      -11.5          0           0           0
  6. 3  4       35       -5.5          0           0         3.5
  7. 4  5       45      -10.5          0         9.5         4.5

答案3

得分: 0

您可以在连接成对的数据后使用 pivot_table：

  1. import pandas as pd
  3. df = pd.DataFrame.from_dict(
  4.     {0: {0: 'ID', 1: '1', 2: '2', 3: '3', 4: '4', 5: '5'}, 1: {0: 'Net Cost', 1: '30', 2: '40', 3: '50', 4: '35', 5: '45'}, 2: {0: 'Charge Description', 1: 'Surcharge A', 2: 'Discount X', 3: 'Discount X', 4: 'Discount X', 5: 'Surcharge A'}, 3: {0: 'Charge Amount', 1: '9.5', 2: '-12.5', 3: '-11.5', 4: '-5.5', 5: '9.5'}, 4: {0: 'Charge Description', 1: 'Discount X', 2: '', 3: '', 4: 'Surcharge B', 5: 'Discount X'}, 5: {0: 'Charge Amount', 1: '-11.5', 2: '', 3: '', 4: '3.5', 5: '-10.5'}, 6: {0: 'Charge Description', 1: 'Discount Y', 2: '', 3: '', 4: '', 5: 'Surcharge B'}, 7: {0: 'Charge Amount', 1: '-3.25', 2: '', 3: '', 4: '', 5: '4.5'}, 8: {0: 'Charge Description', 1: 'Surcharge B', 2: '', 3: '', 4: '', 5: ''}, 9: {0: 'Charge Amount', 1: '2.5', 2: '', 3: '', 4: '', 5: ''}})
  6. # 设置第一行为标题
  7. df.columns = df.iloc[0, :]
  8. df.drop(index=0, inplace=True)
  10. df = pd.concat([df.iloc[:, [0, 1, i, i + 1]] for i in range(2, len(df.columns), 2)]).replace('', 0)
  12. print(df[df['Charge Description'] != 0]
  13.     .pivot_table(columns='Charge Description', values='Charge Amount', index=['ID', 'Net Cost'])
  14.     .fillna(0))

输出：

  1. Charge Description  Discount X  Discount Y  Surcharge A  Surcharge B
  2. ID Net Cost                                                         
  3. 1  30                    -11.5       -3.25          9.5          2.5
  4. 2  40                    -12.5        0.00          0.0          0.0
  5. 3  50                    -11.5        0.00          0.0          0.0
  6. 4  35                     -5.5        0.00          0.0          3.5
  7. 5  45                    -10.5        0.00          9.5          4.5
英文:

You can use pivot_table after concatenating pair-wise:

  1. import pandas as pd
  2. df = pd.DataFrame.from_dict(
  3. {0: {0: 'ID', 1: '1', 2: '2', 3: '3', 4: '4', 5: '5'}, 1: {0: 'Net Cost', 1: '30', 2: '40', 3: '50', 4: '35', 5: '45'}, 2: {0: 'Charge Description', 1: 'Surcharge A', 2: 'Discount X', 3: 'Discount X', 4: 'Discount X', 5: 'Surcharge A'}, 3: {0: 'Charge Amount', 1: '9.5', 2: '-12.5', 3: '-11.5', 4: '-5.5', 5: '9.5'}, 4: {0: 'Charge Description', 1: 'Discount X', 2: '', 3: '', 4: 'Surcharge B', 5: 'Discount X'}, 5: {0: 'Charge Amount', 1: '-11.5', 2: '', 3: '', 4: '3.5', 5: '-10.5'}, 6: {0: 'Charge Description', 1: 'Discount Y', 2: '', 3: '', 4: '', 5: 'Surcharge B'}, 7: {0: 'Charge Amount', 1: '-3.25', 2: '', 3: '', 4: '', 5: '4.5'}, 8: {0: 'Charge Description', 1: 'Surcharge B', 2: '', 3: '', 4: '', 5: ''}, 9: {0: 'Charge Amount', 1: '2.5', 2: '', 3: '', 4: '', 5: ''}})
  4. # setting first row as header
  5. df.columns = df.iloc[0, :]
  6. df.drop(index=0, inplace=True)
  7. df = pd.concat([df.iloc[:, [0,1,i,i+1]] for i in range(2, len(df.columns), 2)]).replace('', 0)
  8. print(df[df['Charge Description']!=0]
  9. .pivot_table(columns='Charge Description', values='Charge Amount', index=['ID', 'Net Cost'])
  10. .fillna(0))

Output:

  1. Charge Description  Discount X  Discount Y  Surcharge A  Surcharge B
  2. ID Net Cost                                                         
  3. 1  30                    -11.5       -3.25          9.5          2.5
  4. 2  40                    -12.5        0.00          0.0          0.0
  5. 3  50                    -11.5        0.00          0.0          0.0
  6. 4  35                     -5.5        0.00          0.0          3.5
  7. 5  45                    -10.5        0.00          9.5          4.5

答案4

得分: 0

我将使用melt函数来堆叠具有相同名称的列，然后使用pivot函数来创建你想要的结果。

  1. # 确保第一行现在是列名，然后删除第一行。
  2. df.columns = df.iloc[0]
  3. df = df[1:]
  5. # 创建两个已经melt的数据框，并按索引将它们连接起来。
  6. df1 = df.melt(['ID', 'Net Cost'], ['Charge Description']).sort_values(by='ID').reset_index(drop=True)
  7. df2 = df.melt(['ID', 'Net Cost'], ['Charge Amount']).sort_values(by='ID').reset_index(drop=True)
  8. df1['Charge Amount'] = df2['value']
  10. # 进行一些清理工作，重命名df1中添加的'value'列。
  11. df1 = df1.drop(columns=[0]).rename(columns={'value': 'Charge Description'})
  12. df1 = df1.dropna()
  14. # 对数据进行透视。
  15. df1 = df1.pivot(index=['ID', 'Net Cost'], columns='Charge Description', values='Charge Amount')

df1的结果如下：

  1. Charge Description Discount X Discount Y Surcharge A Surcharge B
  2. ID Net Cost                                                     
  3. 1  30                   -11.5      -3.25         9.5         2.5
  4. 2  40                   -12.5        NaN         NaN         NaN
  5. 3  50                   -11.5        NaN         NaN         NaN
  6. 4  35                    -5.5        NaN         NaN         3.5
  7. 5  45                   -10.5        NaN         9.5         4.5
英文:

I would use melt to stack the identically named columns, then pivot to create the outcome you want.

  1. # Ensure the first line is now the column names, and then delete the first line.
  2. df.columns = df.iloc[0]
  3. df = df[1:]
  4. # Create two melted df's, and join them on index.
  5. df1 = df.melt(['ID', 'Net Cost'], ['Charge Description']).sort_values(by='ID').reset_index(drop=True)
  6. df2 = df.melt(['ID', 'Net Cost'], ['Charge Amount']).sort_values(by='ID').reset_index(drop=True)
  7. df1['Charge Amount'] = df2['value']
  8. # Clean up a little, rename the added 'value' column from df1. 
  9. df1 = df1.drop(columns=[0]).rename(columns={'value': 'Charge Description'})
  10. df1 = df1.dropna()
  11. # Pivot the data.
  12. df1 = df1.pivot(index=['ID', 'Net Cost'], columns='Charge Description', values='Charge Amount')

Result of df1:

  1. Charge Description Discount X Discount Y Surcharge A Surcharge B
  2. ID Net Cost                                                     
  3. 1  30                   -11.5      -3.25         9.5         2.5
  4. 2  40                   -12.5        NaN         NaN         NaN
  5. 3  50                   -11.5        NaN         NaN         NaN
  6. 4  35                    -5.5        NaN         NaN         3.5
  7. 5  45                   -10.5        NaN         9.5         4.5`

答案5

得分: 0

以下是翻译好的部分：

"My first thought was to read the data out into a list of dictionaries representing each Row (making both the keys and values from the data values), then form a new dataframe from that."（我最初的想法是将数据读取到一个字典列表中，每个字典代表一行（从数据值中同时生成键和值），然后从中创建一个新的数据框。）

"For your example, that would make..."（对于你的示例，这将产生...）

"For the SMALL sample dataset, using comprehensions appears to be quite quick for that..."（对于小规模示例数据集，使用推导似乎相当快速...）

"To sort the column names, add the following..."（要对列名进行排序，请添加以下内容...）

"df2 = df2[['ID', 'Net Cost', *sorted(df2.columns[2:])]]"（df2 = df2[['ID', 'Net Cost', *sorted(df2.columns[2:])]]）

英文:

My first thought was to read the data out in to a list of dictionaries representing each Row (making both the keys and values from the data values), then form a new dataframe from that.

For your example, that would make...

  1. [
  2. {
  3. 'ID': '1',
  4. 'Net Cost': '30',
  5. 'Discount X': '-11.5',
  6. 'Discount Y': '-3.25',
  7. 'Surcharge A': '9.5',
  8. 'Surcharge B': '2.5',
  9. },
  10. {
  11. 'ID': '2',
  12. 'Net Cost': '40',
  13. 'Discount X': '-12.5',
  14. },
  15. {
  16. 'ID': '3',
  17. 'Net Cost': '50',
  18. 'Discount X': '-11.5',
  19. },
  20. {
  21. 'ID': '4',
  22. 'Net Cost': '35',
  23. 'Discount X': '-5.5',
  24. 'Surcharge B': '3.5',
  25. },
  26. {
  27. 'ID': '5',
  28. 'Net Cost': '45',
  29. 'Discount X': '-10.5',
  30. 'Surcharge A': '9.5',
  31. 'Surcharge B': '4.5',
  32. },
  33. ]

For the SMALL sample dataset, using comprehensions appears to be quite quick for that...

  1. import pandas as pd
  2. from itertools import chain
  3. rows = [
  4. {
  5. name: value
  6. for name, value in chain(
  7. [
  8. ("ID", row[0]),
  9. ("Net Cost", row[1]),
  10. ],
  11. zip(row[2::2], row[3::2]) # pairs of columns: (2,3), (4,5), etc
  12. )
  13. if name
  14. }
  15. for ix, row in df.iloc[1:].iterrows() # Skips the row with the column headers
  16. ]
  17. df2 = pd.DataFrame(rows).fillna(0)

Demo (including timings of this and three other answers):

EDIT:

To sort the column names, add the following...

  1. df2 = df2[['ID', 'Net Cost', *sorted(df2.columns[2:])]]

huangapple
  • 本文由 发表于 2023年2月16日 03:24:11
  • 转载请务必保留本文链接：https://go.coder-hub.com/75464568.html
  • aggregate
  • melt
  • pandas
  • pivot
  • python
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定