Python Dataframe Reshaping

3
votes

I have a python pandas dataframe that can be simplified as follow:

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

The aim/goal of new data frame: I would like to have the data classified by month and day. I would like to fill column Data1, Data2 and Data3 with figure from Initial_Data. So for example for Month January and Day Monday, Data1 = 20, Data2 = 25 and Data3 stays as NaN because month January and Day Monday has highest Count = 2. For month February and day Monday I would like to have Data1 = 15, Data2 = 20 and Data3 = 25, this is because month February and day Monday has highest Count=3. For month March day Tuesday, I would like to have Data1 = 50, Data2 and Data3 = NaN and for Month March day Wednesday I would like to have Data1 = 75 and DAta2 = Data3 = NaN because they have highest Count = 1. The Final datafame shall look like this:

      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

I tried to use if statement, but it does not work since I cant find solution to fill all three columns (Data1,Data2 and Data3). Many thanks in advance.

2
pythonpandasdataframeif-statement

2 Answers

1
votes

You can try this:

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]

Output:

      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

Details:

First, use set_index and unstack the inner most index to move to 'Count' in to columns. Thus reshaping the dataframe. Then add 'Data' prefix to column headers.

Next, we need to merge or join the two dataframe together based on Month and day columns.

0
votes

Here was my answer, but Scott beat me to it with a much better one. 

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