How to split a pandas dataframe?

Question

id  state   city
1   0   0
2   13  9
3   118 2524
4   20  0
5   0   0
6   3   8
7   0   0
8   10  26
9   0   0
10  6   13
11  0   0

I want to split the data-frame into 2. One with the state and city column as 0, and other which have state and city codes in them. Then after getting the values for the ids with state and city '0',append it with the original dataframe.

please append what you have done so far and ask your problems. — parisssss, Jan 02 '19 at 10:55
possible duplicate https://stackoverflow.com/questions/41624241/pandas-split-dataframe-into-two-dataframes — Shanteshwar Inde, Jan 02 '19 at 11:11
@nikhil ambekar if the answer posted below helped solve your problem, you may accept it by clicking on the tick sign beside it, thank you! — DirtyBit, Feb 07 '19 at 06:18

DirtyBit · Answer 1 · 2019-01-02T11:34:27.113

AFAIU, you need two dataframes (one with the state and city column as 0) and the other (which have state and city codes in them).

import pandas as pd
import numpy as np

columns = ['state','city']
stateList = [0,13,18,20,0,3,0,10,0,6,0]
cityList = [0,9,2524,0,0,8,0,26,0,13,0]


newList = list(zip(stateList,cityList))
data = np.array(newList)
# print(data)
df = pd.DataFrame(data, columns=columns)
df_zero = df.loc[(df['state'] == 0) & (df['city'] == 0)]
print("Printing the zero valued dataframe: ")
print(df_zero)

df_non_zero = df.loc[(df['state'] != 0) & (df['city'] != 0)]
print("Printing the non-zero valued dataframe: ")
print(df_non_zero)

print("Printing the index values of zero valued dataframe: ")
print(df_zero.index.values)

OUTPUT:

score 0 · Answer 2 · answered Jan 02 '19 at 11:12

Considering your dataframe has name df. First make two series of zeros, one for state and second for city. Note: I am just taking some initial values of your dataframe but it will work for any size of dataframe.

city = pd.Series(np.zeros(len(df)))
state = pd.Series(np.zeros(len(df)))

Now, make a dataframe from these two series like this,

df1 = pd.DataFrame()
df1['state_0'] = state.values
df1['city_0'] = city.values
df1

Ouput:

   state_0  city_0
0   0.0     0.0
1   0.0     0.0
2   0.0     0.0
3   0.0     0.0

Then, make a second dataframe as your original,

df2 = df
df2

Output:

    state   city
0   0        0
1   13       9
2   118    2524
3   20       0

Now, just concatenate these two dataframes like this,

df = pd.concat([df1, df2], axis=1)
df

Output:

    state_0 city_0  state   city
0   0.0      0.0      0      0
1   0.0      0.0     13      9
2   0.0      0.0     118    2524
3   0.0      0.0     20      0

How to split a pandas dataframe?

2 Answers2