pandas: how to do multiple groupby-apply operations

I have more experience with R’s data.table, but am trying to learn pandas. In data.table, I can do something like this:

> head(dt_m)
   event_id           device_id longitude latitude               time_ category
1:  1004583 -100015673884079572        NA       NA 1970-01-01 06:34:52   1 free
2:  1004583 -100015673884079572        NA       NA 1970-01-01 06:34:52   1 free
3:  1004583 -100015673884079572        NA       NA 1970-01-01 06:34:52   1 free
4:  1004583 -100015673884079572        NA       NA 1970-01-01 06:34:52   1 free
5:  1004583 -100015673884079572        NA       NA 1970-01-01 06:34:52   1 free
6:  1004583 -100015673884079572        NA       NA 1970-01-01 06:34:52   1 free
                 app_id is_active
1: -5305696816021977482         0
2: -7164737313972860089         0
3: -8504475857937456387         0
4: -8807740666788515175         0
5:  5302560163370202064         0
6:  5521284031585796822         0


dt_m_summary <- dt_m[,
                     .(
                       mean_active = mean(is_active, na.rm = TRUE)
                       , median_lat = median(latitude, na.rm = TRUE)
                       , median_lon = median(longitude, na.rm = TRUE)
                       , mean_time = mean(time_)
                       , new_col = your_function(latitude, longitude, time_)
                     )
                     , by = list(device_id, category)
                     ]

The new columns (mean_active through new_col), as well as device_id and category, will appear in dt_m_summary. I could also do a similar by transformation in the original table if I want a new column that has the results of the groupby-apply:

dt_m[, mean_active := mean(is_active, na.rm = TRUE), by = list(device_id, category)]

(in case I wanted, e.g., to select rows where mean_active is greater than some threshold, or do something else).

I know there is groupby in pandas, but I haven’t found a way of doing the sort of easy transformations as above. The best I could think of was doing a series of groupby-apply’s and then merging the results into one dataframe, but that seems very clunky. Is there a better way of doing that?

Solved

IIUC, use groupby and agg. See docs for more information.

df = pd.DataFrame(np.random.rand(10, 2),
                  pd.MultiIndex.from_product([list('XY'), range(5)]),
                  list('AB'))

df

df.groupby(level=0).agg(['sum', 'count', 'std'])

---

A more tailored example would be

# level=0 means group by the first level in the index
# if there is a specific column you want to group by
# use groupby('specific column name')
df.groupby(level=0).agg({'A': ['sum', 'std'],
                         'B': {'my_function': lambda x: x.sum() ** 2}})

Note the dict passed to the agg method has keys 'A' and 'B'. This means, run the functions ['sum', 'std'] for 'A' and lambda x: x.sum() ** 2 for 'B' (and label it 'my_function')

Note 2 pertaining to your new_column. agg requires that the passed functions reduce columns to scalars. You're better off adding the new column ahead of the groupby/agg

@piRSquared has a great answer but in your particular case I think you might be interested in using pandas very flexible apply function. Because it can be applied to each group one at a time you can operate on multiple columns within the grouped DataFrame simultaneously.

def your_function(sub_df):
    return np.mean(np.cos(sub_df['latitude']) + np.sin(sub_df['longitude']) - np.tan(sub_df['time_']))

def group_function(g):
    return pd.Series([g['is_active'].mean(), g['latitude'].median(), g['longitude'].median(), g['time_'].mean(), your_function(g)], 
                     index=['mean_active', 'median_lat', 'median_lon', 'mean_time', 'new_col'])

dt_m.groupby(['device_id', 'category']).apply(group_function)

However, I definitely agree with @piRSquared that it would be very helpful to see a full example including expected output.

Send data from Activity to fragment in an Android app

In my application I have an Activity (MainScreen) and one Fragment (Orders). I have a string (name) in the Activity and I want to send it to the Fragment. (Under the comment lines you can see my attempt to send the string from the activity to the fragment). I don't know why, but the application crashes with this.

This is MainScreen.java:

public class MainScreen  extends AppCompatActivity {

    Switch list_toggle;
    String user_id,username,status;
    boolean ischecked;


    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.main_screen);

        final TextView textView4 = (TextView) findViewById(R.id.textView4);
        final TextView textView3 = (TextView) findViewById(R.id.textView3);

        list_toggle = (Switch) findViewById(R.id.mySwitch);

        Bundle extras = getIntent().getExtras();
        if (extras != null) {

            username = extras.getString("USER_NAME");
            user_id = extras.getString("USER_ID");
            status = extras.getString("STATUS");
        }

        if (status.contentEquals("Available")){

            ischecked = true;
        }
        else{
            ischecked = false;
        }

        textView4.setText(Html.fromHtml(status));
        list_toggle.setChecked(ischecked);


        String[] parts = username.split(" ");
        String name = parts[0]; // 004
        String surname = parts[1];

        textView3.setText(Html.fromHtml("Hi, " + name + " "));


        list_toggle.setOnCheckedChangeListener(new CompoundButton.OnCheckedChangeListener() {
            @Override
            public void onCheckedChanged(CompoundButton buttonView, boolean isChecked) {
                if (isChecked) {

                    textView4.setText(Html.fromHtml("Available"));

                } else {

                    textView4.setText(Html.fromHtml("Unavailable"));

                }
            }
        });

        //I tried to do this to send "name" to the fragment
        Bundle bundle = new Bundle();
        bundle.putString("name", name);
        Orders fragobj = new Orders();
        fragobj.setArguments(bundle);

        TabLayout tabLayout = (TabLayout) findViewById(R.id.tab_layout);
        tabLayout.addTab(tabLayout.newTab().setText("Orders"));
        tabLayout.addTab(tabLayout.newTab().setText("Past Orders"));
        tabLayout.addTab(tabLayout.newTab().setText("More"));
        tabLayout.setTabGravity(TabLayout.GRAVITY_FILL);

        final ViewPager viewPager = (ViewPager) findViewById(R.id.pager);
        final PagerAdapter adapter = new PagerAdapter
                (getSupportFragmentManager(), tabLayout.getTabCount());
        viewPager.setAdapter(adapter);
        viewPager.addOnPageChangeListener(new TabLayout.TabLayoutOnPageChangeListener(tabLayout));
        tabLayout.setOnTabSelectedListener(new TabLayout.OnTabSelectedListener() {


            @Override
            public void onTabSelected(TabLayout.Tab tab) {
                viewPager.setCurrentItem(tab.getPosition());
            }

            @Override
            public void onTabUnselected(TabLayout.Tab tab) {

            }

            @Override
            public void onTabReselected(TabLayout.Tab tab) {

            }
        });

    }


    @Override
    public boolean onCreateOptionsMenu(Menu menu) {
        getMenuInflater().inflate(R.menu.menu_main, menu);
        return true;
    }

    @Override
    public boolean onOptionsItemSelected(MenuItem item) {
        int id = item.getItemId();
        if (id == R.id.action_settings) {
            return true;
        }

        return super.onOptionsItemSelected(item);
    }

}

And this is Orders.java:

public class Orders extends Fragment {
    public View onCreateView(LayoutInflater inflater, ViewGroup container,
                             Bundle savedInstanceState) {
        View v = inflater.inflate(R.layout.orders, container, false);
        ListView listView1 = (ListView) v.findViewById(R.id.listView1);
        TextView textView3 = (TextView) v.findViewById(R.id.textView16);


        String currentDate = new SimpleDateFormat("dd/MM/yyyy", Locale.getDefault()).format(new Date());
        textView3.setText(Html.fromHtml(currentDate));

        //I tried to do this to get the String name from the Activity
        String data = getArguments().getString("name");
        System.out.println(data);

        Order[] items = {
                new Order("#403", "07-04-2016", "5:29 PM"),
                new Order("#404", "07-04-2016", "5:35 PM"),
                new Order("#405", "07-04-2016", "5:40 PM"),
                new Order("#406", "07-04-2016", "5:54 PM"),
        };

        ArrayAdapter adapter = new ArrayAdapter(getActivity(),
                android.R.layout.simple_list_item_1, items);

        listView1.setAdapter(adapter);

        listView1.setOnItemClickListener(new AdapterView.OnItemClickListener() {
            @Override
            public void onItemClick(AdapterView parent, View view, int position,
                                    long id) {
                String item = ((TextView) view).getText().toString();


                Intent intent = new Intent(getActivity(), OrderDetails.class);
                intent.putExtra("data",item);
                startActivity(intent);

            }
        });

        return v;
    }
}

Solved

One simplest way is to define 'name' as public static variable and use String data=MainScreen.name; in fragment class.

Sequelize generates a not working SQL when use order option

when I use order option in #findAll it generates SQL:

SELECT
  `id`, `first_name` AS `firstName`,
  `last_name` AS `lastName` FROM `customers` AS `Customer`
ORDER BY `Customer`.`firstName` DESC;

but this SQL causes error:

ER_BAD_FIELD_ERROR: Unknown column 'Customer.firstName' in 'order clause'

Code example:

var Customer = sequelize.define("Customer", {
  id: {
    type: Sequelize.INTEGER({unsigned: true}),
    primaryKey: true
  },
  firstName: {
    type: Sequelize.STRING(32),
    field: "first_name"
  },
  lastName: {
    type: Sequelize.STRING(32),
    field: "last_name"
  }
}, {
  name: {
    singular: "customer",
    plural: "customers"
  },
  tableName: "customers",
  timestamps: false,
  underscored: true
});

Customer.findAll({
  order: [["firstName", "DESC"]]
}).then(function(list) {
  console.log(list);
}).catch(function(err) {
  console.log(err);
});

Mysql: 5.6.20

Sequelize: 3.14.2

Are there any solutions of this issue?

Solved

Use order: [["first_name", "DESC"]]; the ORDER BY looks at column names, not your SELECT alias firstName.

Send a fake SMS over bluetooth to android

I'm building a installation for trade show where a PC with linux would send a SMS to a rooted android cellphone (lenovo a760). The problem is that there is no internet, no gsm network (there is but very expensive). Using WiFi is forbidden by trade show operator. Using cable and adb is my last hope, but not very convenient (cables are ugly).

I look for a solution that would allow PC to send/trigger android over bluetooth so the cellphone will act like it just got a message.

Solved

I manged to use gammu to connect over bluetooth and send an sms to myself. Python code:

import gammu
statemachine = gammu.StateMachine()
statemachine.SetConfig(
    0, {'Device': 'bt_addr', 'Connection': 'blueat'},
)
statemachine.Init()
statemachine.SendSMS({
    'Text': 'test', 
    'SMSC': {'Location': 1},
    'Number': '012345667',
})