How to fill empty index or empty row based on another column value?

Question

I have a data frame:

             Date                Cities       Random_Number
Country
US           2020-01-01          LA           100
             2020-01-03          LA           150
UK           2020-01-01          Ldn          125
             2020-01-03          Birmingham   135

My desired data frame:

             Date                Cities       Random_Number
Country
US           2020-01-01          LA           100
US           2020-01-03          LA           150
UK           2020-01-01          Ldn          125
UK           2020-01-03          Birmingham   135

My aim is to have empty index row to be filled. Many thanks.

Index(['US','','UK','']),dtype = 'object',name = 'Country'.. — teteh May
– teteh May, Commented Mar 5, 2020 at 12:36

jezrael · Accepted Answer · 2020-03-05 12:39:24Z

1

Because there are empty strings first convert them to missing values by Series.mask and then forward filling missing values by ffill:

df = df.reset_index()
print (df)
  Country        Date      Cities  Random_Number
0      US  2020-01-01          LA            100
1          2020-01-03          LA            150
2      UK  2020-01-01         Ldn            125
3          2020-01-03  Birmingham            135

df['Country'] = df['Country'].mask(df['Country'] == '').ffill()
print (df)
  Country        Date      Cities  Random_Number
0      US  2020-01-01          LA            100
1      US  2020-01-03          LA            150
2      UK  2020-01-01         Ldn            125
3      UK  2020-01-03  Birmingham            135

answered Mar 5, 2020 at 12:39

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

Comments

AKSHAY PANDYA · Accepted Answer · 2020-03-05 12:44:22Z

0

can you try this

data.fillna(method='ffill')

Got your desired output.

answered Mar 5, 2020 at 12:44

AKSHAY PANDYA

912 bronze badges

Comments

René · Accepted Answer · 2020-03-05 12:45:05Z

You can try df.head(4) to 'ungroup' the DataFrame.

df = pd.DataFrame([['US', '2020-01-01', 'LA', 100],
                   ['US', '2020-01-03', 'LA', 150],
                   ['UK', '2020-01-01', 'Ldn', 125],
                   ['UK', '2020-01-03', 'Birmingham', 135]],
                  columns=['Country', 'Date', 'Cities', 'Random_Number']).groupby('Country')
print(df)

Result:

             Date                Cities       Random_Number
Country
US           2020-01-01          LA           100
             2020-01-03          LA           150
UK           2020-01-01          Ldn          125
             2020-01-03          Birmingham   135

Ungroup:

print(df.head(4))

Result:

  Country        Date      Cities  Random_Number
0      US  2020-01-01          LA            100
1      US  2020-01-03          LA            150
2      UK  2020-01-01         Ldn            125
3      UK  2020-01-03  Birmingham            135

Collectives™ on Stack Overflow

How to fill empty index or empty row based on another column value?

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related