[FIXED] I want to split the data and get value by rows and columns

Issue

I want to split the dataset along with row and column, by splitting the data set into 80:20% ratio where 80% is the training data and 20% will be the test data. But I am able to split the dataset into 80% but not by 20%.

import pandas as pd
import numpy as np
from sklearn.model_selection import train_test_split
from sklearn.linear_model import LinearRegression

city_attributes = pd.read_csv('./input/city_attributes.csv')
humidity = pd.read_csv('./input/humidity.csv')
pressure = pd.read_csv('./input/pressure.csv')
temperature = pd.read_csv('./input/temperature.csv')
weather_description = pd.read_csv('./input/weather_description.csv')
wind_direction = pd.read_csv('./input/wind_direction.csv')
wind_speed = pd.read_csv('./input/wind_speed.csv')

# we can reshape these using pd.melt
humidity = pd.melt(humidity, id_vars = ['datetime'], value_name = 'humidity', var_name = 'City')
pressure = pd.melt(pressure, id_vars = ['datetime'], value_name = 'pressure', var_name = 'City')
temperature = pd.melt(temperature, id_vars = ['datetime'], value_name = 'temperature', var_name = 'City')
weather_description = pd.melt(weather_description, id_vars = ['datetime'], value_name = 'weather_description', var_name = 'City')
wind_direction = pd.melt(wind_direction, id_vars = ['datetime'], value_name = 'wind_direction', var_name = 'City')
wind_speed = pd.melt(wind_speed, id_vars = ['datetime'], value_name = 'wind_speed', var_name = 'City')

# combine all of the dataframes created above 
weather = pd.concat([humidity, pressure, temperature, wind_direction, wind_speed, weather_description], axis = 1)
weather = weather.loc[:,~weather.columns.duplicated()] # indexing: every row, only the columns that aren't duplicates

# now we can merge this with the city attributes
weather = pd.merge(city_attributes,weather, on = 'City')
weather = weather.dropna()

first = pd.DataFrame()
rest = pd.DataFrame()

total_size = weather.shape[0]
train_size = 1277055
test_size = 319264

if len(weather) > train_size: 
   first = weather[:1277055]
   rest = weather[319264:]

print(rest)

test data output

train data output

Solution

Currently your code that reads

train_size = 1277055
test_size = 319264

if len(weather) > train_size: 
    first = weather[:1277055]
    rest = weather[319264:]

defines rest as all rows after the 319264th one, while first is correctly the first 1277055 rows. Maybe instead you wanted

train_size = 1277055
test_size = 319264

if len(weather) > (train_size + test_size): 
    first = weather.iloc[:train_size, :] 
    rest = weather.iloc[(train_size + 1):(train_size + test_size + 1), :] # same as weather[1277056:1596320, :]

Alternatively with sklearn's train_test_split:

train_size = 1277055
test_size = 319264
train_idx, test_idx = train_test_split(weather.index, train_size = train_size , test_size = test_size )

df_train = weather.iloc[train_idx, :]
df_test = weather.iloc[test_idx, :]

Example usage:

In [1]: import numpy as np
   ...: import pandas as pd
   ...: train_size = 1277055
   ...: test_size = 319264
   ...: weather = pd.DataFrame(np.random.randint(0,100,size=(train_size+test_size, 4)), columns=list('ABCD'))
   ...: print(weather.head())
    A   B   C   D
0  13  91  68  35
1  52  30  52  59
2  16  22  73  24
3  62  86  27  96
4  88  54  23   4

In [2]: if len(weather) >= (train_size + test_size):
   ...:     print('subsetting')
   ...:     first = weather.iloc[:train_size, :]
   ...:     rest = weather.iloc[(train_size + 1):(train_size + test_size + 1), :]
   ...: 
   ...:     print(first.shape)
   ...:     print(rest.shape)
   ...: 
subsetting
(1277055, 4)
(319263, 4)

Answered By - ludan