Python数据帧重塑

2024-03-28 10:20:29 发布

您现在位置:Python中文网/ 问答频道 /正文

我有一个python数据帧,可以简化如下:

python
df= pd.DataFrame([['January','Monday',np.nan,np.nan,np.nan,1,20],['January','Monday',np.nan,np.nan,np.nan,2,25],['February','Monday',np.nan,np.nan,np.nan,1,15],\
      ['February','Monday',np.nan,np.nan,np.nan,2,20],['February','Monday',np.nan,np.nan,np.nan,3,25],['March','Tuesday',np.nan,np.nan,np.nan,1,50],\
      ['March','Wednesday',np.nan,np.nan,np.nan,1,75]],columns = ['Month','Day','Data1','Data2', 'Data3','Count','Initial_Data'])
     Month        Day  Data1  Data2  Data3  Count  Initial_Data
0   January     Monday    NaN    NaN    NaN      1            20
1   January     Monday    NaN    NaN    NaN      2            25
2  February     Monday    NaN    NaN    NaN      1            15
3  February     Monday    NaN    NaN    NaN      2            20
4  February     Monday    NaN    NaN    NaN      3            25
5     March    Tuesday    NaN    NaN    NaN      1            50
6     March  Wednesday    NaN    NaN    NaN      1            75

新数据框架的目的/目标:我想按月份和日期对数据进行分类。我想用来自初始数据的数字填充列Data1、Data2和Data3。例如,对于一月和星期一,Data1=20,Data2=25,Data3保持为NaN,因为一月和星期一的计数最高=2。对于二月和星期一,我希望Data1=15,Data2=20和Data3=25,这是因为二月和星期一的计数最高,为3。对于三月日星期二,我希望Data1=50,Data2和Data3=NaN,对于三月日星期三,我希望Data1=75和Data2=Data3=NaN,因为它们的最高计数为1。最终数据如下:

      Month        Day  Data1  Data2  Data3
0   January     Monday     20   25.0    NaN
1   January     Monday     20   25.0    NaN
2  February     Monday     15   20.0   25.0
3  February     Monday     15   20.0   25.0
4  February     Monday     15   20.0   25.0
5     March    Tuesday     50    NaN    NaN
6     March  Wednesday     75    NaN    NaN

我尝试使用if语句,但它不起作用,因为我找不到填充所有三列(Data1、Data2和Data3)的解决方案。非常感谢。你知道吗


Tags: 数据npnanmarchmondaydaymonthdata1
2条回答

你可以试试这个:

df2 = df.set_index(['Month','Day','Count'])['Initial_Data'].unstack().add_prefix('Data').reset_index()
df.merge(df2, on=['Month','Day'], suffixes=('_x',''))[df.columns]

输出:

      Month        Day  Data1  Data2  Data3  Count  Initial_Data
0   January     Monday   20.0   25.0    NaN      1            20
1   January     Monday   20.0   25.0    NaN      2            25
2  February     Monday   15.0   20.0   25.0      1            15
3  February     Monday   15.0   20.0   25.0      2            20
4  February     Monday   15.0   20.0   25.0      3            25
5     March    Tuesday   50.0    NaN    NaN      1            50
6     March  Wednesday   75.0    NaN    NaN      1            75

详情:

首先,使用set_indexunstack最内部的索引移动到to列中的'Count'。从而重塑数据帧。然后在列标题中添加“Data”前缀。你知道吗

接下来,我们需要merge或者基于Month和day列将两个数据帧连接在一起。你知道吗

这是我的答案,但斯科特用一个更好的答案击败了我。你知道吗

import numpy as np
import pandas as pd

df = pd.DataFrame([
    ['January','Monday',np.nan,np.nan,np.nan,1,20],\
    ['January','Monday',np.nan,np.nan,np.nan,2,25],\
    ['February','Monday',np.nan,np.nan,np.nan,1,15],\
    ['February','Monday',np.nan,np.nan,np.nan,2,20],\
    ['February','Monday',np.nan,np.nan,np.nan,3,25],\
    ['March','Tuesday',np.nan,np.nan,np.nan,1,50],\
    ['March','Wednesday',np.nan,np.nan,np.nan,1,75]],
    columns = ['Month','Day','Data1','Data2', 'Data3','Count','Initial_Data'])

new = pd.DataFrame(columns = ['Month','Day','Data1','Data2', 'Data3'])

for ridx, row in df.iterrows():
    new.loc[ridx] = [row['Month'], row['Day'], np.nan, np.nan, np.nan]
    if row['Count'] == 1:
        new.loc[new.index[ridx], 'Data1'] = row['Initial_Data']
    if row['Count'] == 2:
        new.loc[new.index[ridx], 'Data2'] = row['Initial_Data']
        new.loc[new.index[ridx-1], 'Data2'] = row['Initial_Data']
        new.loc[new.index[ridx], 'Data1'] = new.loc[new.index[ridx-1], 'Data1']
    if row['Count'] == 3:
        new.loc[new.index[ridx], 'Data3'] = row['Initial_Data']
        new.loc[new.index[ridx-1], 'Data3'] = row['Initial_Data']
        new.loc[new.index[ridx-2], 'Data3'] = row['Initial_Data']
        new.loc[new.index[ridx], 'Data1'] = new.loc[new.index[ridx-1], 'Data1']
        new.loc[new.index[ridx], 'Data2'] = new.loc[new.index[ridx-1], 'Data2']

print(new)

      Month        Day Data1 Data2 Data3
0   January     Monday    20    25   NaN
1   January     Monday    20    25   NaN
2  February     Monday    15    20    25
3  February     Monday    15    20    25
4  February     Monday    15    20    25
5     March    Tuesday    50   NaN   NaN
6     March  Wednesday    75   NaN   NaN

相关问题 更多 >