Create new column as a group by in pandas dataframe

Question

Hello guys I need your help to get the expected output/result in pandas dataframe. I have a file which contains data as shown here:

Time/Location    Value
Location1   
Today             3
Next day          0
Weekend          -6
Next week         1
Location2   
Today             2
Next day         -1
Weekend           3
Next week         2
Location3   
Today             1
Next day          3
Weekend 1
Next week        -1
Location4   
Today             3
Next day          2
Weekend           5
Next week         4
Location5   
Today             4
Next day          2
Weekend           3
Next week         1
Location6   
Today            -1
Next day          3
Weekend           3
Next week         2

And expected the output as the following which is creating new column for the "Location".

Location    Time       Value
Location1   Today       3
Location1   Next day    0
Location1   Weekend    -6
Location1   Next week   1
Location2   Today       2
Location2   Next day   -1
Location2   Weekend     3
Location2   Next week   2
Location3   Today       1
Location3   Next day    3
Location3   Weekend     1
Location3   Next week  -1
Location4   Today       3
Location4   Next day    2
Location4   Weekend     5
Location4   Next week   4
Location5   Today       4
Location5   Next day    2
Location5   Weekend     3
Location5   Next week   1
Location6   Today      -1
Location6   Next day    3
Location6   Weekend     3
Location6   Next week   2

I appreciate any help/suggestions....please!

Thank you!

post data, not pics. use this as a guide: https://stackoverflow.com/questions/20109391/how-to-make-good-reproducible-pandas-examples — sammywemmy, Feb 11 '20 at 23:04
What is the issue, exactly? Stack Overflow is not a free code writing service. See: [ask], [tour], [help/on-topic]. — AMC, Feb 12 '20 at 02:31

jezrael · Accepted Answer · 2020-02-12T10:27:10.010

If not exist values in Value are missing use DataFrame.insert with replace values to missing in first column and forward filling them by ffill, last remove rows by DataFrame.dropna and rename column:

df.insert(0, 'Location', df['Time/Location'].mask(df['Value'].notna()).ffill())
df = df.dropna(subset=['Value']).rename(columns={'Time/Location':'Time'})
print (df)
     Location       Time  Value
1   Location1      Today    3.0
2   Location1   Next day    0.0
3   Location1    Weekend   -6.0
4   Location1  Next week    1.0
6   Location2      Today    2.0
7   Location2   Next day   -1.0
8   Location2    Weekend    3.0
9   Location2  Next week    2.0
11  Location3      Today    1.0
12  Location3   Next day    3.0
13  Location3    Weekend    1.0
14  Location3  Next week   -1.0
16  Location4      Today    3.0
17  Location4   Next day    2.0
18  Location4    Weekend    5.0
19  Location4  Next week    4.0
21  Location5      Today    4.0
22  Location5   Next day    2.0
23  Location5    Weekend    3.0
24  Location5  Next week    1.0
26  Location6      Today   -1.0
27  Location6   Next day    3.0
28  Location6    Weekend    3.0
29  Location6  Next week    2.0

Another idea is test values in first column by Series.isin and filter by boolean indexing:

L = ['Today','Next day','Weekend','Next week']
m = df['Time/Location'].isin(L)
df.insert(0, 'Location', df['Time/Location'].mask(m).ffill())
df = df[m].rename(columns={'Time/Location':'Time'})
print (df)
     Location       Time  Value
1   Location1      Today    3.0
2   Location1   Next day    0.0
3   Location1    Weekend   -6.0
4   Location1  Next week    1.0
6   Location2      Today    2.0
7   Location2   Next day   -1.0
8   Location2    Weekend    3.0
9   Location2  Next week    2.0
11  Location3      Today    1.0
12  Location3   Next day    3.0
13  Location3    Weekend    1.0
14  Location3  Next week   -1.0
16  Location4      Today    3.0
17  Location4   Next day    2.0
18  Location4    Weekend    5.0
19  Location4  Next week    4.0
21  Location5      Today    4.0
22  Location5   Next day    2.0
23  Location5    Weekend    3.0
24  Location5  Next week    1.0
26  Location6      Today   -1.0
27  Location6   Next day    3.0
28  Location6    Weekend    3.0
29  Location6  Next week    2.0

Create new column as a group by in pandas dataframe

1 Answers1