1
$\begingroup$

i have a dataset like this enter image description here

my desire format is like thisenter image description here

I tried using index slicing eg dll.loc[:4,'category'] = "CAPITAL FUND" dll.loc[5:10,'category'] = "BORROWING" but this idea is risky so is there any idea to solve this?

$\endgroup$
1
$\begingroup$

Instead of perhaps iterating of each row and filling the gaps as required, I would suggest trying to do it via indexing. The solution is:

df['category'] = df.where(~df.id.isnull())['item'].ffill()

Here I break down my solution to help you understand why it works.

Imagine your dataframe is called df. I created a small version of yours as follows:

In [1]: import pandas as pd

In [2]: df = pd.DataFrame.from_dict(
            {'id':   [1, None, None, 2, None, None, 3, None, None],
             'item': ['CAPITAL FUND', 'A', 'B', 'BORROWINGS', 'A', 'B', 'DEPOSITS', 'A', 'B']})

In [3]: df       # see what it looks like

Out[3]: 

 id          item   
0  1.0  CAPITAL FUND
1  NaN             A
2  NaN             B
3  2.0    BORROWINGS
4  NaN             A
5  NaN             B
6  3.0      DEPOSITS
7  NaN             A
8  NaN             B

I get the dataframe back where the id column is not null (~ reverses the isnull()). On the resulting dataframe, I take only the item column (using [item]) and then fill the missing gaps, using the previous valid value in that column.

In [4]: df['category'] = df.where(~df.id.isnull())['item'].ffill() 

In [5]: df
Out[5]: 
    id          item      category
0  1.0  CAPITAL FUND  CAPITAL FUND
1  NaN             A  CAPITAL FUND
2  NaN             B  CAPITAL FUND
3  2.0    BORROWINGS    BORROWINGS
4  NaN             A    BORROWINGS
5  NaN             B    BORROWINGS
6  3.0      DEPOSITS      DEPOSITS
7  NaN             A      DEPOSITS
8  NaN             B      DEPOSITS

The trick is to understand this part: df.where(~df.id.isnull())['item']

It returns really the whole dataframe, with the values where ~df.id.isnull() is True. Then only the item dataframe. The result is this:

In [6]: df.where(~df.id.isnull())['item'] 
Out[6]: 
0    CAPITAL FUND
1             NaN
2             NaN
3      BORROWINGS
4             NaN
5             NaN
6        DEPOSITS
7             NaN
8             NaN

Now it should be clear why the final .ffill() works as we would like. It forward fills the missing values, using the last known valid value.

$\endgroup$
3
  • $\begingroup$ it worked thank you but if i had a some values in NaN then is there any idea to make category column? $\endgroup$ Oct 24 '18 at 11:34
  • $\begingroup$ @subashpoudel - you will need to do the filtering part differently - that will depend on your data that has values instead of NaNs. So change the part: ~df.id.isnull() to return just the values [CAPITAL FUND, BORROWINGS. DEPOSITS]. You essentially need to build that last code block I show by customising the filter part. $\endgroup$
    – n1k31t4
    Oct 24 '18 at 11:51
  • $\begingroup$ i tired to change ~df.if.isnull() but i got error can u give me example for that? if u give example i would be great help for me $\endgroup$ Oct 25 '18 at 2:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.